4 Replies Latest reply: Jul 29, 2014 10:39 AM by _Phil RSS

    ODI 12c: SQL query to retrieve Session info like shown in the Operator view

    2680920


      Hi,

       

      I need to query the Session info like the information shown in the Operator view for ODI 12c.

      I googled and found some scripts that work with 11g and before, but when I run those I do not get any info when running those on our 12c installation.

       

      I need the following information:

      • For Session = Session ID
      • and Step = step number
        • The number of inserts
        • The number of Updates
        • The number of Deletes
        • The number of Errors
        • The number of Rows
        • 1. Re: ODI 12c: SQL query to retrieve Session info like shown in the Operator view
          _Phil

          I don't have a full query for you, but check the "snp_session" and "snp_step_log" tables in the work repository schema (join them on "sess_no").

          • 2. Re: ODI 12c: SQL query to retrieve Session info like shown in the Operator view
            user2071946

            Hi

             

            This should work...

             

             

            select case when SSS.NNO=(select min(SSS0.NNO) from
            <mySchema>.SNP_SESS_STEP SSS0 where SSS0.SESS_NO=SS.SESS_NO) then
            SS.SESS_NO end "Session ID" ,
            case when SSS.NNO=(select min(SSS0.NNO) from
            <mySchema>.SNP_SESS_STEP SSS0 where SSS0.SESS_NO=SS.SESS_NO) then
            SS.SESS_NAME end "Session Name",
            case when SSS.NNO=(select min(SSS0.NNO) from
            <mySchema>.SNP_SESS_STEP SSS0 where SSS0.SESS_NO=SS.SESS_NO) then
            SS.SESS_BEG end "Start Time",
            case when SSS.NNO=(select min(SSS0.NNO) from
            <mySchema>.SNP_SESS_STEP SSS0 where SSS0.SESS_NO=SS.SESS_NO) then
            SS.SESS_END end "End Time",
            case when SSS.NNO=(select min(SSS0.NNO) from
            <mySchema>.SNP_SESS_STEP SSS0 where SSS0.SESS_NO=SS.SESS_NO) then
            SS.SESS_STATUS end "Job Status",
            case when SSS.NNO=(select min(SSS0.NNO)
            from <mySchema>.SNP_SESS_STEP SSS0 where SSS0.SESS_NO=SS.SESS_NO) then
            SS.SESS_MESS end "Error Message",
            '|',
            SSS.STEP_NAME "Step Name",
            SSS.NNO "Step Number",
            SSL.STEP_DUR "Step Duration (Seconds)",
            SSL.NB_ROW "Rows Processed",
            SSL.NB_INS "Rows Inserted",
            SSL.NB_UPD "Rows Updated",
            SSL.NB_DEL "Rows Deleted",
            SSL.NB_ERR "Rows to Error Table"
            from
            <mySchema>.SNP_SESSION SS,
            <mySchema>.SNP_SESS_STEP SSS,
            <mySchema>.SNP_STEP_LOG SSL
            where (1=1)
            and SS.SESS_NO=SSS.SESS_NO
            and SSS.SESS_NO=SSL.SESS_NO
            and SSS.NNO=SSL.NNO
            order by SS.SESS_BEG DESC, SSS.NNO ;

            where:
            <mySchema> is the Database Schema name where the Work Repository is stored.


            Note: Please close the thread by marking correct/helpful if the issue is resolved


            Best Regards

            asp

            • 3. Re: ODI 12c: SQL query to retrieve Session info like shown in the Operator view
              2680920

              Hi,

               

              Thank you for your answer.

               

              Although the script runs without error, I get zero 0 records as result.

              We run ODI with Loadplans containing Mappings generated to Scenarios.

               

              Looking deeper into the script and our ODI tables, I notice that SNP_SESS_STEP table has no records.

              I modified the query and use the SNP.TASK_LOG, SNP_SCEN_REPORT, SNP_SCEN_TASK and SNP_SCEN_STEP tables and get the desired result.

               

               

              This is what I came up with:

              Any comments or suggestions?

              Am I missing something?

               

              select      SCS.TABLE_NAME, SESS_NO, SCS.STEP_NAME, TO_CHAR(SCT.SCEN_TASK_NO, '099') || ' - ' || SCT.TASK_NAME1 as step, SCT.NNO, STL.NB_RUN, STL.SCEN_TASK_NO, STL.NB_ROW, STL.NB_INS, STL.NB_UPD, STL.NB_DEL, STL.NB_ERR, STL.TASK_DUR as STEP_DUR, STL.TASK_BEG as STEP_BEGIN, STL.TASK_END as STEP_END, SCR.SESS_DUR as SESSION_DUR, SCR.SESS_BEG as SESSION_BEGIN, SCR.SESS_END as SESSION_END

              from        ODIW.SNP_SESS_TASK_LOG STL,

                          ODIW.SNP_SCEN_REPORT SCR,

                          ODIW.SNP_SCEN_TASK SCT,

                          ODIW.SNP_SCEN_STEP SCS

              where       SCR.SCEN_RUN_NO=STL.SESS_NO

              and         SCT.SCEN_NO=SCR.SCEN_NO

              and         SCT.SCEN_TASK_NO=STL.SCEN_TASK_NO

              and         SCS.SCEN_NO=SCT.SCEN_NO

              and         SCS.NNO=SCT.NNO

              --and         SESS_NO = 7152

              and         SCT.TASK_NAME1 in

                          ('Insert flow into I$ table',

                          'Update existing rows',

                          'Historize old rows',

                          'Insert changing and new dimensions',

                          'Historize records not in sourcefile'

                          )

              order by    SCS.STEP_NAME, SCT.SCEN_TASK_NO, SESS_NO

              • 4. Re: ODI 12c: SQL query to retrieve Session info like shown in the Operator view
                _Phil

                If you're getting the result you need, that's great.  When you run individual interfaces or packages (not compiled scenarios) then perhaps you would see the session data logged into SNP_SESS*. It's logging to the SNP_SCEN* tables when the scenarios are executed from within the load plan.