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

Odi 10 g Get Last Session Date

aetl Explorer
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    Damn, same time Jerome :-)
  • 4. Re: Odi 10 g Get Last Session Date
    aetl Explorer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    Did you succeed ?
    Please close the thread if everything is ok, and mark as helpful or correct the posts that helps you ;)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points