6 Replies Latest reply: Apr 10, 2013 12:18 AM by user964802 RSS

    Need Session stats as a report

    913195
      Hi All,

      I would like to know if there any feature available in ODI which will facilitate to generate a report of the session statistics shown in Operator in ODI 11g.
      I mean is there any option provied in ODI operator which can be used to automatically generate a report showing the session statistics for a package/scenario ran.

      OR

      which system table in ODI repository I can query to pull the stats for the sessions?

      Thanks and Regards
      Santywalsh
        • 1. Re: Need Session stats as a report
          DecaXD
          from repository

          SNP_SESSION (pk I_SESSION) contains SESSION information
          SNP_SESS_STEP contains child element

          you could create a view or using sdk.
          • 2. Re: Need Session stats as a report
            Bhabani Ranjan
            for sdk you can refer
            https://blogs.oracle.com/dataintegration/entry/odi_sdk_retrieving_information_from
            • 3. Re: Need Session stats as a report
              913195
              Thanks for your reply,

              and what will be the table to extract all the stats such as no of inserts, duration , no of updates.. which we see when we select a taks..
              Basically I want to avoid manuall capturing the stats for a session and its corresponding child taks and thir stats shown on the right side..

              Thanks
              Santy
              • 4. Re: Need Session stats as a report
                DecaXD
                HI,

                here some column that could be useful for your purpose.

                in SNP_SESSION you have

                SESS_NO     id session
                SESS_NAME     session name
                SCEN_VERSION     scenario version
                SESS_BEG     start date
                SESS_END     end date
                SESS_DUR     duration
                SESS_STATUS     d = done, e = error
                AGENT_NAME     agent
                CONTEXT_CODE     code
                PARENT_SESS_NO     sess_no of parent
                NB_ROW     # row
                NB_INS     #insert
                NB_UPD     #update
                NB_DEL     #delete
                NB_ERR     #error


                in SNP_SESS_STEP

                SESS_NO     #SESSION
                NNO     step id


                in SNP_SESS_TASK_LOG

                SESS_NO     PK
                NNO     PK
                NB_RUN     #RETRY
                TASK_BEG     START DATE
                TASK_END     END DATE
                TASK_DUR     DURATION
                NB_ROW     #ROW
                NB_INS     #INSERT
                NB_UPD     #UPDATE
                NB_DEL     #DELETE
                NB_ERR     #ERROR
                ERROR_MESSAGE     MESSAGE
                • 5. Re: Need Session stats as a report
                  JeromeFr
                  SNP_SESSION, SNP_STEP_LOG and SNP_SESS_TASK_LOG, respectively for the sessions, its steps and its tasks.
                  • 6. Re: Need Session stats as a report
                    user964802
                    Here's a MSSQL view that works for me:

                    CREATE view
                    [dbo].[vw_snp_session_report]
                    as
                    select
                    sess.sess_no,
                    sess.sess_name,
                    sest.step_name,
                    case
                    when sess.sess_status = 'D'
                    then 'OK'
                    when sess.sess_status = 'E'
                    then 'Error'
                    when sess.sess_status = 'R'
                    then 'Running'
                    when sess.SESS_STATUS = 'M'
                         then 'Warning'
                    else sess.sess_status
                    end as sess_status,
                    cast(year(sess.sess_beg) as char(4))+
                    right('0'+ cast(month(sess.sess_beg) as varchar(4)),2)+
                    right('0'+ cast(day(sess.sess_beg) as varchar(4)),2)+ '_' +
                    right('0'+ datename(hour,sess.sess_beg),2)+
                    right('0'+ datename(minute,sess.sess_beg),2)
                    as sess_beg,
                    isnull(cast(year(sess.sess_end) as char(4))+
                    right('0'+ cast(month(sess.sess_end) as varchar(4)),2)+
                    right('0'+ cast(day(sess.sess_end) as varchar(4)),2)+ '_' +
                    right('0'+ datename(hour,sess.sess_end),2)+
                    right('0'+ datename(minute,sess.sess_end),2),'n/a')
                    as sess_end,
                    isnull(cast(sess.sess_dur as varchar),'n/a')
                    as sess_dur,
                    isnull(right(replicate('0',18) + cast(cast(sess_dur/60 as decimal(19,2))as varchar),8),'n/a')
                    as sess_min,
                    sess.agent_name,
                    sess.context_code,
                    isnull(sess.scen_name,'n/a')
                    as scen_name,
                    sess.user_name,
                    isnull(cast(slog.step_dur as varchar),'n/a')
                    as step_dur,
                    slog.nb_row,
                    slog.nb_ins,
                    slog.nb_upd,
                    slog.nb_del,
                    slog.nb_err
                    from snp_session sess
                    inner join snp_step_log slog on sess.sess_no = slog.sess_no
                    inner join snp_sess_step sest on slog.sess_no = sest.sess_no
                    and slog.nno = sest.nno
                    where 1=1
                    and sess_beg > getdate()-1
                    and sess_name <> 'SESSIONREPORT'