BODS SQL sessions not closed after job termination

[alpteja]

It has been observed that there are some issues due SAP BODS not able to close the SQL sessions after job termination.

• SQL performance issues due to long running sessions
• Deadlocks due to existing job run again which the tables are being used by previous session

Is there any perminant solution to fix this ?

[former_member442248]

Hi. Please explain with examples. Deadlocks and performance issues are mostly related to either bad ETL job design and or issues with the source / target systems. In the example above you have to build a logic in your ETL that in case a previous instance of the job is running it should not trigger a new instance.

Regards. S

[alpteja]

Hi Shazin,

I can't explain with proper screenshots for the above mentioned issue.
But I will try to clarify why it is related to performance and deadlocks.

Main issue to is due to aborting the job in Console :

If you have been using SAP Data Services console to monitor the jobs in large DWH environment, there might be some expectations of data flow how many records it fetches and if the data flow fetching more records than expected we would like to kill before loading whole data as it is not required and we will abort the job using console.

Performance related:

If the job session not terminated in SQL, Since BODS pushes the code to SQL servers while executing data flows bods
if those sessions were not terminated on SQL servers properly its obvious that some one who is using the same tables will get slow loading at their end. (I don't know if it is a bug in software)

Deadlocks is possible:

If the SQL server is still writing some data due to a non closed session and the other users or jobs trying to update the same tables used by existing session then there will be a deadlock.

I hope the above comments help you to understand better and thanks for the quick response.

Note : Due to existing SQL session there is no direct dead locks or performance problems.
As there is lot of complexity involved here, without knowing what are the current running sessions we can't do anything.
And as per your comment the single instance feature is already there in BODS which only triggers the JOB only after completion of previous execution and it will only check if the job is terminated or not and it will not check background session related to the job if it is properly closed or not.