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:
No comments:
Post a Comment