Archiving ODI logs

Have you ever had the problem of lots of logs in ODI listing Operator. What would you think if there is a method to archive logs and you can store them as much as you want and can select anytime you want to?

I want to mention that since you are making changes in your work repository environment make sure you have your backups, exports, etc. These are my personal experiences and will take no responsibility for your development/data/work loss.

ODI stores logs in work repository, including variable history, in 12 tables. First create these tables with prefix “ARC_” to a different schema. I suggest you not to create any referential integrity constraints, foreign keys, indexes or constraints for these tables.

1. Generate the create table script, rename table and schema name, delete everything except CREATE TABLE script. Do not create indexes, foreign keys, constraints or other database objects for those tables.

2. Reverse both SOURCE and TARGET tables in ODI model.

3. Create a variable to store how many days you want to (this could be in a parameter table or maybe static). In this example I will use variable with name #V_Purge_Log_Retention.

4. Create interface for each of them regarding the filters and joins related to table below. (Click to enlarge picture)

5. Prepare a ODI procedure that will delete tables in order, shown below (click to enlarge picture). Remember, because of the referential integrity in ODI Repository, you need to send delete statements to database into right order. Otherwise, you might receive a referential integrity error or lose unexpected data.

6.Pack these stuff up shown below (click to enlarge picture). All the scenarios starting with “I_ARC” are in “Asynchronous Mode”. If any of the scenarios fail, exception is generated. You may take a look throwing exceptions in ODI in my other post named “ODI Alert Mechanism“.

That’s it. If you are able to do these things in right sequence, your ODI logs are in secure in your “ARC_” tables.

About these ads