ODI-IKM Incremental Update (Updateless) v1

I have been thinking of creating a brand new Knowledge Module which will make an Incremental Update without using any UPDATE/DELETE SQL statements, only with inserts to increase performance. Initially, I was thinking that there should be a second integration table, but when I started scrawling to figure out flow diagram and how Knowledge Module works, I realized that there is no need for a second integration table.
If you are using Oracle database which your target table existing, this Knowledge Module can fit your needs. Let me try to explain how it works;

1. Drops and Creates I$ flow table (as usual)
2. Inserts the missing records (new records on source) to I$ flow table (if INSERT_NEW_ROWS option is set to true).
3. Inserts the matching records (existing records on source) to I$ flow table (if UPDATE_EXISTING_ROWS option is set to true).
4. Inserts the non-matching records (deleted records on source) to I$ flow table (if NOT_DELETE_NON_EXISTING_ROWS option is set to true).
5. Wrap them up and truncate & insert target table from flow table and drop flow table (if DELETE_TEMPORARY_OBJECTS option is set to true).
6. Analyze target (if ANALYZE_TARGET option is set to true).

Using DBLink for Oracle to Oracle in ODI

DBLink is the one of the most powerful attributes, if it is needed to load data from one Oracle database to other Oracle database especially when you are doing extraction. If you want to use DBLink, that you already implemented in Topology as “Instance / DBLink (Data Server)”, in a Knowledge Module in Oracle Data Integrator (ODI), there are not fancy options.

Adding Variabled HINTS in ODI

As in my previous post I have described how to implement HINTS per interface. It is a little bit tricky because HINTS are stored in development “ODI interfaces” and scenario of the interface must be regenerated when a HINT is changed and completely in developers’ hands.

I have thought about it and found a way to let dba’s change HINTS by updating a parameter table for each interfaces’/procedures’ each step. Once your dba updates/inserts the desired row (per SQL statement for each Knowledge Module or ODI Procedure) and commit the record it is been executed in the next execution of that interface or procedure.

Adding HINTS in ODI

We all know that Oracle is powerful when you use HINTS and override tables’ physical options and database parameters. There are many tuning and/or performance related blogs on this subject, but what I will share is how to implement HINTS in ODI.

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?

Adding Functions to ODI

In most cases we (ETL developers) are stuck into doing something. Sometimes an analytic function helps, sometimes a function in database. But is there a way to use this function in a structured way that ODI can understand? Let’s try to find out. 🙂

Languages in Topology Manager help you leverage your languages that you can use in ODI. You can even add a new language (if you know and eager to type all commands).

ODI Handling DQ

If you have some SQL scripts to send output to some business users, especially for data quality issues or other information, how would you handle this. In case you do not have a data quality tool for that.

Step 1 : Prepare your data quality scripts.
Step 2 : Put those scripts into files.
Step 3 : Prepare PL/SQL or ODI code to read contents of those files. Send this script to your database, insert output to an ERROR table.
Step 4 : Read ERROR table by ERROR_CODE and loop it from beginning to end.
Step 5 : Send e-mail for each script, attach the appropriate file and mention how many rows are generated in this ERROR_CODE.
Step 6 : Continue with Step 4.

ODI Alert Mechanism

Today, I have read an article about recommendation about not using red lines (ko) in ODI packages. I just want to explain how I manage alert mechanism in ODI.
This is a sample package that I use in extraction phase.

Adding Datatypes to Oracle Data Integrator (ODI)

As I’ve mentioned before ODI is a tool that can learn. And here is one of the proof. 🙂
There are many datatypes in various databases and each version may have new datatype, but your ETL tool should learn while talking with database with that datatype. I’ll try to explain how to add “bigint” datatype to Sybase Adaptive Server Enterprise and “SDO_GEOMETRY” to Oracle.

Changing ODI Repository Name

If you receive an error in ODI 10g like;
java.lang.Exception: Error during Session launching
at com.sunopsis.dwg.dbobj.SnpSession.remoteExecute
or
java.lang.NullPointerException
at com.sunopsis.dwg.cmd.DwgCommandBase.prepare(DwgCommandBase.java)

First check whether your agents are working and test connection to repositories. If everything ok, you might have changed your repository name recently. 🙂

When you export your work and master repository and import to another schema or database and whether you connect to your imported repository, you change your repository name in your first environment.

Correct order is first rename your Master Repository name, log off / log on and change your Work Repository name and check below fields in both your Master and Work Repositories in both environments.

Master Repository : SNP_REM_REP
Work Repository : SNP_LOC_REPW

REP_NAME column of MASTER_REP.SNP_REM_REP should be equal to REP_NAME column in WORK_REP.SNP_LOC_REPW.

You cannot see WORK_REP.SNP_LOC_REPW value in ODI Topology Manager and if you are changing your repository name you need to update value from database.