8 Replies Latest reply: Dec 18, 2012 7:35 AM by A. Drieux RSS

    Odi 10 g Get Last Session Date

    aetl
      Hi,


      I want to get Last run time of Package.For example I will put a filter between last run time of package and sysdate.But how can I get Last session date of a package?

      I will insert new datas to target table from last session time to current time

      Thnx a lot
        • 1. Re: Odi 10 g Get Last Session Date
          JeromeFr
          I would go for a query like this on your work repository (in the refresh tab of an ODI variable) :
          SELECT max(SESS_END) FROM SNP_SESSION WHERE SESS_NAME = 'package_name' AND SESS_STATUS = 'OK';
          I do not have an ODI install in front of me, so I'm not about the OK. Just check the current values of some past executions ;)
          • 2. Re: Odi 10 g Get Last Session Date
            A. Drieux
            You can do it from 2 ways

            manually :
            you set variables in your package in order to store in a custom parameter table the date of last beginning and last ending

            from the repository
            you retrieve the value in the work repository : in the SNP_SCEN_REPORT table. You have the columns SESS_BEG and SESS_END in date format.
            With SCEN_NO, do an inner join with SNP_SCEN to get the name of the scenario.
            Juste retrieve the last row which SCEN_NAME = your scenario.
            • 3. Re: Odi 10 g Get Last Session Date
              A. Drieux
              Damn, same time Jerome :-)
              • 4. Re: Odi 10 g Get Last Session Date
                aetl
                Hi,

                I created a variable that called #GET_SESSION
                SELECT <%=odiRef.getSession("SESS_NO")%> FROM DUAL

                and then I created second variable valled GET_SESSION2 :

                SELECT TO_DATE(SUBSTR(MAX(SESS_BEG),1,10),'DD-MM-YYYY') FROM ODIW_rep.SNP_SESSION WHERE SESS_NO=#GET_SESSION

                For example

                My session information is here :

                195001     DENEME_1234          5     2778252     1355211056548     11/12/2012 09:30:56     11/12/2012 09:30:57

                beg time is :11/12/2012 09:30:56


                But when I execute package now (11/12/2012 09:38:56) max(sess_beg) will be last executed time but it will be sysdate.I want to get 11/12/2012 09:30:56

                How can ı get ?
                • 5. Re: Odi 10 g Get Last Session Date
                  A. Drieux
                  This is normal.
                  You have created a variable that retrieves the ACTUAL session begin date.

                  So, when you're launching your package at 11/12/2012 09:38:56, it will retrieve the begin date of this session, so 11/12/2012 09:38:56.

                  If you want the previous session, you cannot use a filter like SESS_NO = <%=odiRef.getSession("SESS_NO")%>

                  ==> You must filter on the previous session of the same scenario. Or the previous session of the same session name.
                  Use WHERE clause to filter on finished / not running session
                  But don't use <%=odiRef.getSession("SESS_NO")%>

                  Other solution : use the SESS_END in order to retrieve the END date. Since your actuel scenario is not finish, you will get the last session.
                  If you really want the BEGIN date of the last session, you must adapt your SQL Statement in order to get the session previously executed.
                  • 6. Re: Odi 10 g Get Last Session Date
                    aetl
                    Hi Drieux ,

                    I want to get last execution lend time.

                    Now my filter should be like this..SELECT * FROM SOURCE_TABLE S WHERE S.TRANS_DATE BETWEEN last_execution_time and SYSDATE.For example my previous last_time is 11/12/2012 09:30:56
                    sysdate is 11/12/2012 09:30:56
                    I will insert just these datas.Is there any solution to insert datas from previous last execution to current?
                    Last execution time is :previous end_time .Now What should I do?

                    Best Regards
                    • 7. Re: Odi 10 g Get Last Session Date
                      A. Drieux
                      I refer you to our previous posts


                      1) Either you store the execution date in a custom parameter table (if you want to use this solution, I will give you more details).
                      2) OR you retrieve the date in the SNP_SESSION table, or the SNP_SCEN_REPORT table with the SQL statement we give you, Jerome and me.


                      Moreover, your previous execution should NOT be the last END execution time, but the last BEGINNING execution time. Otherwise, you won't extract the data that have been created in source table between the first extract and the end of your last execution.


                      3) You can also use CDC to journalize new/modified rows in source and select only them.
                      • 8. Re: Odi 10 g Get Last Session Date
                        A. Drieux
                        Did you succeed ?
                        Please close the thread if everything is ok, and mark as helpful or correct the posts that helps you ;)