OTech Magazine – Winter 2014 issue

I was invited to write an article by Douwe Pieter van den Bos before UKOUG Tech’13 conference (actually he got me from my session of UKOUG Tech’13, which I couldn’t attend) and agreed to write an article for OTech Magazine. This magazine is fully non-vendored, independent and free to download magazine which Oracle technology experts write on their expertise.

My article “Future is now, ODI12c” which about Oracle Data Integrator 12c on page 120. I have collated Oracle’s public ODI12c documentation and summarised the most important features with my previous ODI experience. Besides me, there are really valuable information of Oracle experts and lots for Oracle technology, so enjoy reading!

Click here for OTech Magazine – Winter 2014 issue main page.
Click here for direct pdf version OTech Magazine – Winter 2014 issue.
Click here to reach directly to my article in OTech Magazine Winter 2014 issue.

ODTUG Webinar Invitation

On 23rd of July, I’m invited to ODTUG’s webinar. Please click below links to register the event or view the event details.

Register the webinar.

Webinar details;

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.

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.

Installing ODI to Windows 7

If you receive a error message saying “this operating system is not supported” follow below operations.

Since Windows 7 is released as version 6.1, previous versions of ODI supports up to 6.0 depending on the version that you want to install. What you have to do to install ODI on Windows 7 platform is so simple.

1. Find the file named, oraparam.ini in the ODI setup directory (default is \\InstallPath\ODI\setup\Windows\Disk1\install).
2. Edit oraparam.ini and find the header [Certified Versions] or value named “CERTIFIED_VERSION_FAILURE_MESSAGE”.
3. Add 6.1 next to 6.0 with comma without any space or other character. It should seem like that;

Windows=4.0,5.0,5.1,5.2,6.0,6.1

You can now run setup without asking version failure and follow on screen instructions.

ODI Definition

Here’s my definition to Oracle Data Integrator known as ODI. To me, this definition  is the power of ODI.

ODI is a tool that can talk or can learn how to talk with any database system or operating system in its own language.

ODI SendMail

Here is a brief description how you can handle ODI Sendmail command. This command is quite useful to send mails on errors or success in flow.
First of all ODISendMail can be used in ODI packages by drag and drop under in Toolbox. Here is a short explanation;

REFRESH CUBE : A simple procedure that starts the cube refreshment job.
P_CHECK_CUBES : A scenario that checks whether cube is refreshed or not. Succeeds when cube refresh process finishes successfully.
V_CUBE_MAIL_BODY : Refreshes a variable to be used in mail’s body section. Includes a select statement for descriptive usage.
OdiSendMail 25 : This is the pain point. Sends the prepared mail. Properties are below;