Andreas_Sprosec_7439

SQL Agent settings for reuse of DB sessions

Discussion created by Andreas_Sprosec_7439 on Mar 17, 2017
You Asked

What is an SQL job doing when it uses an existing database connection? If the session resets the session, or all of the settings set by the predecessor in that session are retained for him.
For Oracle DBs, a dbms_session.modify_package_state (dbms_session.reinitialize) would reinitialize a session.

and we said ...

The system is working as designed.

Regarding your question: you have set the value to '0' if every execution should establish a new database connection.


retention_time=Number of seconds after which an unused database connection should be terminated.

You can use following query to check the oracle SID for each database connection:
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') session_user
, sys_context ('userenv','CURRENT_SCHEMA') CURRENT_SCHEMA
, sys_context ('userenv','SESSION_USER') SESSION_USER
, sys_context ('userenv','SESSION_USERID') SESSION_USERID
, sys_context ('userenv','SESSIONID') SESSIONID
, sys_context ('userenv','SID') SID
FROM DUAL;

If the value is set to '0' the database connection will be closed after 180 sec if the JOBP completed.

retention_time=180
20160727/170538.924 - U2000005 Job 'JOBS.SQL.SELECT.QUERY' with RunID '1897812' started.
20160727/170538.924 - U2000003 Job 'JOBS.SQL.SELECT.QUERY' started with RunID '1897812'.
20160727/170538.970 - U0029000 Report '1897813' for file 'C:\UC4\agents\WS10\agents\VWGSUP15_SQL_ORACLE_ASP_01\bin\..\temp\PLOG_OAAEDZKU.TXT' has been started.
20160727/170538.986 - U2012001 Successfully established connection to 'VWGSUP12:1521/WO112' with user 'UC4'.
20160727/170539.002 - U2000009 Job 'JOBS.SQL.SELECT.QUERY' with RunID '1897812' ended with return code '0'.
20160727/170539.080 - U2001026 Job 'JOBS.SQL.SELECT.QUERY' with RunID '1897812' has been removed from the job table.
20160727/170539.282 - U0029000 Report '1897814' for file 'C:\UC4\agents\WS10\agents\VWGSUP15_SQL_ORACLE_ASP_01\bin\..\temp\OAAEDZKU.TXT' has been started.
20160727/170539.282 - U0029001 Report '1897813' ended normally.
20160727/170539.314 - U0029001 Report '1897814' ended normally.
20160727/170843.354 - U2012009 Database connection to 'VWGSUP12:1521', user 'UC4' has been closed.

If the value is set to '0' the database connection will be closed immediately if the JOBP completed.

retention_time=0
20160727/171036.664 - U2000005 Job 'JOBS.SQL.SELECT.QUERY' with RunID '1897815' started.
20160727/171036.680 - U2000003 Job 'JOBS.SQL.SELECT.QUERY' started with RunID '1897815'.
20160727/171036.726 - U0029000 Report '1897816' for file 'C:\UC4\agents\WS10\agents\VWGSUP15_SQL_ORACLE_ASP_01\bin\..\temp\PLOG_OAAEDZKX.TXT' has been started.
20160727/171037.491 - U2012001 Successfully established connection to 'VWGSUP12:1521/WO112' with user 'UC4'.
20160727/171037.662 - U2012009 Database connection to 'VWGSUP12:1521', user 'UC4' has been closed.
20160727/171037.662 - U2000009 Job 'JOBS.SQL.SELECT.QUERY' with RunID '1897815' ended with return code '0'.
20160727/171037.678 - U0029001 Report '1897816' ended normally.
20160727/171037.803 - U2001026 Job 'JOBS.SQL.SELECT.QUERY' with RunID '1897815' has been removed from the job table.
20160727/171038.021 - U0029000 Report '1897817' for file 'C:\UC4\agents\WS10\agents\VWGSUP15_SQL_ORACLE_ASP_01\bin\..\temp\OAAEDZKX.TXT' has been started.
20160727/171038.052 - U0029001 Report '1897817' ended normally. 

Outcomes