ODI debugging

With RMS 13.2 Oracle introduced new Retail Analytics (RA) application, which depreciated old Oracle Retail Data Warehouse. RA is using Oracle Data Integrator (ODI) application to extract/transform/load data from different sources instead of flat files and RETL processing.

ODI has a user friendly GUI, however when it comes to debugging and checking what is really going with yours Scenario it means a lot of clicking and switching of the windows.   As a simple guy I like to use old school SQL queries to parse debug information straight from ODI database.

This query will return all information about your session and tasks executed with additional executable and error information provided. SQL MODEL was used to flatten the information so it can be readable. Please bare in mind that it is still work in progress (a little). The parameter is yours ODI session number.

Columns EXECUTABLE will show what was executed and ERROR will show the error.  I cant put everything into one column because in PL/SQL it is impossible to cast CLOBS on a fly. So just copy all content from EXECUTABLE for particular task in another window and enjoy debugging!


SELECT executable_sql.sess_name,
executable_sql.sess_no,
executable_sql.nno,
executable_sql.nb_run,
executable_sql.scen_task_no,
executable_sql.task_status,
executable_sql.task_end,
executable_sql.task_name1,
executable_sql.task_name2,
executable_sql.task_name3,
executable_sql.executable0,
executable_sql.executable1,
executable_sql.executable2,
executable_sql.executable3,
executable_sql.executable4,
executable_sql.executable5,
executable_sql.executable6,
error_sql.error1,
error_sql.error2,
error_sql.error3,
error_sql.error4,
error_sql.error5
FROM
(SELECT sess_name,
sess_no,
nno,
nb_run,
scen_task_no,
task_status,
task_end,
task_name1,
task_name2,
task_name3,
max(output0)executable0,
max(output1)executable1,
max(output2)executable2,
max(output3)executable3,
max(output4)executable4,
max(output5)executable5,
max(output6)executable6
FROM
(
SELECT sess_name,
sess_no,
nno,
nb_run,
scen_task_no,
task_beg,
task_end,
task_status,
task_name1,
task_name2,
task_name3,
output0,
output1,
output2,
output3,
output4,
output5,
output6,
txt_count,
position
FROM (SELECT sess.sess_name,
task_log.sess_no,
task_log.nno,
task_log.nb_run,
task_log.scen_task_no,
task_log.task_beg,
task_log.task_end,
task_log.task_status,
task.task_name1,
task.task_name2,
task.task_name3,
row_number() over (PARTITION BY task_log.sess_no, task_log.nno, task_log.nb_run, task_log.scen_task_no order by txt_log.txt_type desc, txt_log.txt_ord asc) position,
txt_log.txt val,
COUNT(1) over(PARTITION BY task_log.sess_no, task_log.nno, task_log.nb_run, task_log.scen_task_no) txt_count
FROM snp_session sess,
SNP_SESS_TASK task,
SNP_SESS_TASK_LOG task_log,
SNP_SESS_TXT_LOG txt_log
WHERE task_log.sess_no = txt_log.sess_no
AND sess.sess_no = task.sess_no
AND task.nno = task_log.nno
AND task.scen_task_no = task_log.scen_task_no
AND task_log.nno = txt_log.nno
AND task_log.nb_run = txt_log.nb_run
AND task_log.scen_task_no = txt_log.scen_task_no
AND sess.sess_no = task_log.sess_no
and task_log.sess_no = &session
ORDER BY sess.sess_end DESC NULLS LAST,
task_log.sess_no,
task_log.nno,
task_log.nb_run,
task_log.scen_task_no,
position)
MODEL UNIQUE SINGLE REFERENCE
RETURN UPDATED ROWS
DIMENSION BY
(position,
sess_no,
nno,
nb_run,
scen_task_no)
MEASURES
(val AS tmp,
cast('' AS varchar2(4000)) as output0,
cast('' AS varchar2(4000)) as output1,
cast('' AS varchar2(4000)) as output2,
cast('' AS varchar2(4000)) as output3,
cast('' AS varchar2(4000)) as output4,
cast('' AS varchar2(4000)) as output5,
cast('' AS varchar2(4000)) as output6,
sess_name,
task_beg,
task_end,
task_status,
task_name1,
task_name2,
task_name3,
txt_count)
RULES(
output0[position between 0 and 10,any,any,any,any] = output0[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output1[position between 11 and 20,any,any,any,any] = output1[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output2[position between 21 and 30,any,any,any,any] = output2[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output3[position between 31 and 40,any,any,any,any] = output3[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output4[position between 41 and 50,any,any,any,any] = output4[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output5[position between 51 and 60,any,any,any,any] = output5[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output6[position between 61 and 70,any,any,any,any] = output6[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()]))
GROUP BY sess_name,
sess_no,
nno,
nb_run,
scen_task_no,
task_beg,
task_end,
task_status,
task_name1,
task_name2,
task_name3) executable_sql,
(
SELECT sess_name,
sess_no,
nno,
nb_run,
scen_task_no,
max(output1)error1,
max(output2)error2,
max(output3)error3,
max(output4)error4,
max(output5)error5
FROM
(
SELECT sess_name,
sess_no,
nno,
nb_run,
scen_task_no,
output1,
output2,
output3,
output4,
output5,
txt_count,
position
FROM (SELECT sess.sess_name,
task_log.sess_no,
task_log.nno,
task_log.nb_run,
task_log.scen_task_no,
row_number() over (PARTITION BY task_log.sess_no, task_log.nno, task_log.nb_run, task_log.scen_task_no order by sess_mess.txt_ord) position,
sess_mess.txt val,
COUNT(1) over(PARTITION BY task_log.sess_no, task_log.nno, task_log.nb_run, task_log.scen_task_no) txt_count
FROM snp_session sess,
SNP_SESS_TASK task,
SNP_SESS_TASK_LOG task_log,
SNP_EXP_TXT sess_mess
WHERE sess.sess_no = task_log.sess_no
AND sess.sess_no = task.sess_no
AND task.nno = task_log.nno
AND task.scen_task_no = task_log.scen_task_no
AND task_log.i_txt_task_mess = sess_mess.i_txt
and task_log.sess_no = &session
ORDER BY sess.sess_end DESC NULLS LAST,
task_log.sess_no,
task_log.nno,
task_log.nb_run,
task_log.scen_task_no,
sess_mess.txt_ord)
MODEL UNIQUE SINGLE REFERENCE
RETURN UPDATED ROWS
DIMENSION BY
(position,
sess_no,
nno,
nb_run,
scen_task_no)
MEASURES
(val AS tmp,
cast('' AS varchar2(4000)) as output1,
cast('' AS varchar2(4000)) as output2,
cast('' AS varchar2(4000)) as output3,
cast('' AS varchar2(4000)) as output4,
cast('' AS varchar2(4000)) as output5,
sess_name,
txt_count)
RULES(
output1[position<=10,any,any,any,any] = output1[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output2[position between 11 and 20,any,any,any,any] = output2[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output3[position between 21 and 30,any,any,any,any] = output3[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output4[position between 31 and 40,any,any,any,any] = output4[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()],
output5[position between 41 and 50,any,any,any,any] = output5[cv()-1,cv(),cv(),cv(),cv()] || tmp[cv(),cv(),cv(),cv(),cv()]))
GROUP BY sess_name,
sess_no,
nno,
nb_run,
scen_task_no
) error_sql
WHERE executable_sql.sess_no=error_sql.sess_no (+)
AND executable_sql.nno=error_sql.nno(+)
AND executable_sql.nb_run=error_sql.nb_run(+)
AND executable_sql.scen_task_no=error_sql.scen_task_no(+)
ORDER BY executable_sql.scen_task_no DESC NULLS LAST,
executable_sql.sess_no,
executable_sql.nno,
executable_sql.nb_run,
executable_sql.scen_task_no;

About these ads
This entry was posted in pl/sql, oracle, Not so simple query, database, toolset and tagged , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s