X

The Oracle BI Application Blogs provides the latest and tips and tricks on Oracle BI Applications Product (OBIA)

Correlating SQL statement in DB Sessions with ODI Sessions in ODI 11.1.1.7

Guest Author

Author : Jayant Mahto

NOTE: This support is only available for Oracle database.

When a LP is executed by an ODI agent then it creates ODI sessions for the scenario steps and sends appropriate SQL to the DB for execution, which in turn creates DB sessions. For long running jobs (or when there is a suspicion that some sessions are hanging in ODI) we need to correlate ODI sessions and the corresponding DB sessions.

This is achieved by examining the content of the ACTION column in the V$SESSION, which has the following format:

Action: <SESSION_ID>/<STEP_NB>/<STEP_RUN_NUMBER>/<TASK_ORDER_NUMBER>

SESSION_ID: ODI Session ID. It is displayed in operator log.

STEP_NB: Step number (ex: the step number in the package). It is displayed in operator log.

STEP_RUN_NUMBER: If you restart the same session then this will provide Nth run info. Note that if the LP scenario step is set to create a new session in case of failure for LP restart then this number will always show 1.

TASK_ORDER_NUMBER: Task order number for the step. It is displayed in operator log.

Finding out the SQL statement being executed in the database:

The following SQL in the database will provide all the DB sessions being executed by the user '<user_name>'. Run it on the database with DBA user and replace <user_name> with the DB user being used by ODI to create DB sessions.

select a.action, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.action is not null and
a.username='<user_name>'

/

Look into the content of the ACTION column to correlate it back to ODI sessions.

In the following example the SESSION_ID: 38420, STEP_NB: 2 and TASK_ORDER_NUMBER: 21 is in wait state. It is third re-start of the same session, marked by STEP_RUN_NUMBER.

This is the result of the SQL:

ACTION

SQL_TEXT

38420/2/3/21

 

update SCOTT.TARGET_EMP T set ( T.ENAME, T.JOB, T.MGR, T.HIREDATE, T.SAL, T.COMM, T.DEPTNO ) = ( select S.ENAME, S.JOB, S.MGR, S.HIREDATE, S.SAL, S.COMM, S.DEPTNO from SCOTT.I$_TARGET_EMP S where T.EMPNO =S.EMPNO ) where (EMPNO) in ( select EMPNO from SCOTT.I$_TARGET_EMP where IND_UPDATE = 'U' )

 

 

Correlating this SQL to the ODI session step in the operator:

Join the discussion

Comments ( 4 )
  • guest Wednesday, September 25, 2013

    I noticed this as well - when it messed up some of our DB triggers that rely on the action values!

    Any idea what the meaning of the MODULE values are?


  • guest Thursday, November 14, 2013

    The ACTION and MODULE columns in V$SESSION tables are meant to be used by the application that creates the DB session. In this case the application is ODI agent and it stamps the appropriate values in ACTION and MODULE to the SQL statements sent to the database. It doesn't affect the values in other SQL statements of the DB sessions created by some other application and it is not clear how can it mess up your DB triggers.

    The value in MODULE column is "JDBC Thin Client".


  • guest Friday, April 11, 2014

    Thanks for explanation on action values.

    Can you please help me what are the module values mean..

    Sample Module, action output from database..

    MODULE ACTION

    ----------------------------------- -----------------------

    ODI:1362461626310/525/4583007 10417525/2/1/6

    ODI:1362461626310/525/4583007 10186525/2/1/7

    ODI:1362461626310/525/4583007 10349525/2/1/7

    Thanks


  • guest Wednesday, April 20, 2016

    Hi,

    During the load,we are capturing the session ID into one field which is Integer in Netezza.

    The session Id value is exceeded 2147483647 value and failed to load.

    where/how can we reset session ID value in ODI?


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.