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.

My colleague, Fuat, prepared the necessary development to me and after reviewing the code, I want to share it with you. He blogged the same stuff, it is better for you to check his blog post from here.

1. Create a database table to store HINTS in text format;

• INTERFACE_NAME : Name of the Interface that will be queried to get the HINT.
• INTERFACE_KM : Name of the Knowledge Module that Interface is using (IKM or LKM).
• KM_STEP_NAME : Name of the step in Knowledge Module which will use the HINT.
• KM_STEP_ORDER : Order of the step of Knowledge Module.
• STEP_HINT_NAME : Name of the step of the Knowledge Module. This helps dba which step is sent to database.
• STEP_HINT_ORDER : There might be more than 1 hint per step of Knowledge Module, this column shows the order of HINT.
• HINT_VALUE : Exact value of HINT to be added into SQL Statement. This value should contain /*+ and */.

2. Now it is time to edit Knowledge Module and selecting HINTS into ODI Variables and use them.

2.1. Add a step for the first line of Knowledge Module. You can name this step as Get Hints.

Remember that all the HINTS to use in this Knowledge Module would be passed into #Hint_n variables for future use. Technology of “Command on Target” is Java BeanShell and “Command on Source” is Oracle. We already know which KM we are using, Step Names with the variable names and the last filter should be applied is Interface name which can be determined with the function <%= snpRef.getStep(“STEP_NAME”) %>. All the HINTS for this Knowledge Module are in variables now. It is time to use them.

2.2. In the below picture, it is showing how variabled HINTs are used. Syntax should be as “<@=HINT_NAME@>” where HINT_NAME should be same with the variable declared on the Knowledge Module step on the “Command on Target” section.

3. Here is how this method looks like in the operator (I know it is not so fancy but you need to trust ODI).

4. And the last step, adding variabled HINTS into SQL Statements.

Limitations;

* You cannot view HINTs session values within Operator logs. But you may insert the value of the HINT into a log table for each value per Interface for previous working values. All you have to do is adding a step in your Knowledge Module/ODI Procedure to insert the value of HINTS used.

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;

ODI Variables – General Know-How

• Using a variable within a variable
  • If you want to use a ODI variable within a variable simply type variable name in refresh statement. Here is an exampls how you can do. Below SQL statement connects to MSSQL Server and returns if job (named “My_BI_Job”) succesfully finished or not in specific date . Variable #V_SYSDATE is a variable that is refreshed as SYSDATE from DUAL in Oracle database. If this doesn’t work try typing PROJECT_NAME after “#” sign. If you have multiples projects ODI confuses which project to get variable.

select count(1) from msdb.dbo.sysjobhistory
where step_id = 0
  and run_status = 1
  and job_id = (select job_id from msdb.dbo.sysjobs where name = ‘My_BI_Job’)
  and run_date = CAST(SUBSTRING(‘#V_SYSDATE’, 1,8) as integer)

Loading multiple files with ODI

If your source is file and you need to load multiple files into your database you need to do more than something. If you want to load your files using ODI interfaces you need to make some customization within ODI. There are some utilities or commands to load files into databases such as Sybase uses BCP, Oracle uses SQL Loader, MS SQL Server uses Bulk Insert. Some of them support multiple files, some of them support transformations. But there is a bit problem if name of the file is generated by source system, that means you don’t know the names of the files.

I want to explain a methodology loading multiple files into Oracle using external tables, which I think the best and easiest way.

First the environmental stuff and some prerequisites;

1. A Knowledge Module to load files using file name as a ODI Variable.
2. Some operating system folders for loading, rejected and log files.
3. A table named ETL_FILE_LOG to hold the file name in ODI Variable and to log in database.
4. 4 ODI Procedure getting file list, work with processed files, work with rejected files and rename files.
5. An ODI Interface to make transformations and loading.
6. 2 ODI Variables to manage file_name and file_id.
7. An ODI Package to manage everything.

Let’s start to do some work.