How To Clear The Cache For ODI Studio


It is necessary to purge Journal/Operator entries which are older than ten days.

Steps to implement the solution:

  1. First define an ODI Variable to retrieve the targeted date in Java format:
    1. Create a Project (or Global) Variable V, of Alphanumeric datatype.
    2. On the Variable Refresh tab, select a database schema, and run the SELECT command to assign a value.

      For example:

      • On Oracle schema, use SQL command:
        SELECT to_char(sysdate-10, ‘YYYY/MM/DD HH:MI:SS’) FROM dual
        SELECT to_char(sysdate-10, ‘YYYY/MM/DD HH24:MI:SS’) FROM dual /* format to include the 24 hours */

        To get the complete list of Oracle format models which may be used for Date/Time conversions, see:

        http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#i34924

      • On Microsoft SQLServer schema, use SQL command:
        SELECT convert (char(11), getdate()-10, 111) + convert (char(8), getdate()-10, 8)

        To get the complete list of Microsoft SQLServer format models which may be used for Date/Time conversions, see:

        http://msdn.microsoft.com/en-us/library/ms187928.aspx

      The statements above must return the date in “yyyy/MM/dd/ hh:mm:ss” format, elsewhere either:

      – An error message would be signaled, as indicated in Note 423654.1 – “Timestamp format must be yyyy-mm-dd hh:mm:ss.fffffffff” Message When Using The ODI OdiPurgeLog Tool, or

      -The OdiPurgeLog would treat it as a wrong date (for example, “2013/25/05”), but instead of giving an error, it would delete all the Sessions regardless of the -TODATE parameter. Note an easy way for getting the dates in the right format without using “convert”, “to_date”, etc conversion functions would be to force the language of ODI GUI / Agent to English, as explained in Note 1317507.1 – How To Define Java Options (Such As The Limits Of Memory Heap, The Location Of Non-Java Libraries, etc) In ODI

  2. Next, purge the ODI log using the above Variable, and the OdiPurgeLog tool.

    For this, create a two steps Package:

    1. Drag the Variable into the Package, and set the step to “Refresh Variable” type.
    2. Click on OdiPurgeLog icon to add this step into the Package.

      Change “End Date” to #<Project Code>.Variable_name (where <Project Code> is the code of Project if the Variable has been defined in a Project, or GLOBAL if the Variable has been defined as Global).

    The above will generate the OdiPurgeLog command, with -TODATE property set to the Variable value:

    OdiPurgeLog “-TODATE=#<Project Code>.V”

    If it is necessary to delete Session Logs as well as Scenario Logs, add the PURGE_REPORTS parameter to the tool:

    OdiPurgeLog “-TODATE=#<Project Code>.V” “-PURGE_REPORTS=1”

    If this is not set then only Session execution logs will be purged.

    We strongly recommend you always set “-PURGE_REPORT=1” to delete the Scenario reports along with the Sessions, and therefor avoid the accumulation of **orphan** records in the SNP_STEP_REPORT table.
  3. To automatize the purging of Logs, you can generate a Scenario from above specific Procedure, and schedule for regular execution on the desired ODI Agent.
  4. It is recommended that you regularly use the OdiExportLog tool to backup your logs to a zip file before using the OdiPurgeLog to remove them from the ODI Repository.

Thanks,

~KKT~

Advertisements

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