Hi Guys,

Another really usefull tip about data types!

There are some time it happens we don’t have the required data type in respective Technology in ODI say for example DATETIME YEAR TO DAY ,DATETIME YEAR TO SECOND etc and so when we do a Selective reverse or RKM we don’t get the correct data type listed causing which we get the Null data type or get assigned to String .

The other simple solution if you are particular in getting the data type listed in Model . Here are the steps to Manually add the data type in Topology and create the respective conversation syntax.

Step1 . Go to the Respective data type and right click and Insert Data type or Duplicate an already existing datatype

Step2. Provide the required code and syntax

Step 3 . Provide the required converted To and Converted From detail for the Technology you are going to Work with . Lets say my target will be Oracle in that case , I have assigned to Timestamp .

Easy and Simple solution in dealing with Data types missing.

Any comments???

It’s our pleasure to have you here, at  www.odiexperts.com

Hi People,

There is a “bug” in Oracle Data Integrator that doesn’t clean the SNP_EXP_TXT when the “parent” records are deleted.

That happens because, in the repository data model, there is no FK between the SNP_EXP_TXT and its several parents table exactly because they are several.

This situation can bring a huge increase of records and, the most of then, absolutely useless. I already see environments with more than 50 millions of records and with less than 100 k useful…

A consequence of this behavior could be a very slow answer when try to acess objects from Designer and/or Operator. That is very commom.

To solve, all you need is just execute the following DML (NOT TESTED ON 11G, ONLY FOR 10 version):

delete snp_exp_txt
 where not exists
          (select 0
             from (     select distinct I_TXT_TASK_MESS I_TXT
                          from SNP_SESS_TASK_LOG
                   union all
                        select distinct I_TXT_SCEN I_TXT
                          from SNP_SCEN
                   union all
                        select distinct I_TXT_SESS_MESS I_TXT
                          from SNP_SCEN_REPORT
                   union all
                        select distinct I_TXT_DESCRIPTION I_TXT
                          from SNP_SCEN_FOLDER
                   union all
                        select distinct I_TXT_SESS_MESS I_TXT
                          from SNP_SESSION
                   union all
                       select distinct I_TXT_SESS_PARAMS I_TXT
                         from SNP_SESSION  
                   union all
                       select distinct I_TXT_STEP_MESS I_TXT
                         from SNP_STEP_REPORT
                   union all
                        select distinct I_TXT_STEP_MESS I_TXT
                          from SNP_STEP_LOG
                   union all
                        select distinct I_TXT_VAR I_TXT
                          from SNP_VAR_SESS
                   union all
                        select distinct I_TXT_DEF_T I_TXT
                          from SNP_VAR_SESS
                   union all
                        select distinct I_TXT_VAR I_TXT
                          from SNP_VAR_SCEN
                   union all
                         select distinct I_TXT_DEF_T I_TXT
                           from SNP_VAR_SCEN
                   union all
                         select DISTINCT I_TXT as I_TXT      
                          FROM SNP_TXT
                   union all
                         select DISTINCT I_TXT_VAR_IN as I_TXT
                          FROM SNP_VAR
                  ) as UNION_TABLES
              where UNION_TABLES.I_TXT = snp_exp_txt.I_TXT          )

This is an Oracle DB query, adapt it for you Work Repository technology.

You can, either, create a Topology to Work Repository and use an ODI Procedure to schedule this DML once a month, for instance!

As always, comments are welcome!!

Talk to you soon, friends!

Hi every one…

In the last few posts we have shown how to display the SQL results in Operator , fetch the failed objects errors message in Package and various other techniques. Today we want to cover another small scripts which can enable you fetch details of the Interface that have error  out because of PK , FK ,Not Null constraints etc and goes to the Error table.

This scripts creates a File and dumps all the information by reading from SNP_CHECK_TAB  table and finally file can be added to OdiSendMail and send to the Administrator or Developers so that they can know which interface got error  records, so they can do the needful and also in daily load we fail to see all these smaller details especially we have hundreds of interfaces.

import string
import java.sql as sql
import java.lang as lang
import re
sourceConnection = odiRef.getJDBCConnection("SRC")
output_write=open('c:/snp_check_tab.txt','w')
sqlstring = sourceConnection.createStatement()
#---------------------------------------------------------------------------
output_write.write("The Errored Interface of today's (<%=odiRef.getSysDate( )%>)
run are .... \n")
output_write.write("----------------------------------------------------------- \n\n")
#---------------------------------------------------------------------------
sqlstmt="SELECT 'Errored Interface \t- '||SUBSTR(ORIGIN,INSTR(ORIGIN,')')+1,
LENGTH(ORIGIN))||'\nError Message \t\t- '||ERR_MESS||'\nNo of Errored Records \t- '||
ERR_COUNT AS OUTPUT FROM  ODI_TEMP.SNP_CHECK_TAB WHERE TRUNC(CHECK_DATE)=TRUNC(SYSDATE)"
result=sqlstring.executeQuery(sqlstmt)
rs=[]
while (result.next()):
      rs.append(str(result.getString("output")+'\t'))

res='\n\n'.join(map(string.strip,rs))
print >> output_write, res

sourceConnection.close()
output_write.close()

[Note – In the above scripts please change the File path and the Schema(Work Schema ) name according to your respective Environment ]

Provide the Technology and Schema of your work Schema or required schema which can access the SNP_CHECK_TAB and provide the code in Command on Target

and for every run you will get the sample output as shown below.

Attach the File to OdiSendMail and get the daily Error Interface , Message and Records detail .

Download the Codes

Comments are alwasys welcome!

See you soon…

There seems to be some forum threads with installing ODI in windows 7 Edition . I would like to throw some light and tweaks to make it work.

ODI doesn’t support windows 7 since it checks for Windows version before installing. To suppress run the setup from command prompt > setup -ignoresysprereqs , this will let you pass through the initial Windows version check and keep continuing until the end.

In windows 7 Home premium , my ODI was not successful in installation or even it does the ODI dint start so I triggered manually by copying Oracledi folder and created the Environment Variable – ODI_JAVA_HOME to point to latest JDK Path.

Windows 7 Professional , Enterprise and Server 2008 R2 have been successful in installation with ignoresysprereqs option.

After ODI installation , install the JDK1.6 and later since 1.6 support transparency else ODI will work in Basic Theme and create or change the Environment Variable – ODI_JAVA_HOME to point to latest JDK Path.

Small  simple idea to access ODI faster from Taskbar

The easy way of calling designer or topology is creating a shortcut or batch file. Batch files are smart windows programming as shell scripts for Unix.

For example  – create a batch file  -  designer.bat and copy paste

CD C:\OraHome_1\oracledi\bin    < odi designer path >
DESIGNER.BAT

similarly do so for operator , topology and agent too .

CD C:\OraHome_1\oracledi\bin
agentscheduler.bat -name=XMT_AGENT

or  create shortcut into the desktop from  oracledi/bin folder  into a separate Folder and call it Batch_files or whatever meaningful name you wish to provide.

Right  click on Taskbar – >  Toolbars  – > New toolbar  and selected the Batch file folder .

As shown in the above image , with one click from taskbar i can access my ODI easily. This is just my way of accessing ODI in windows 7 , if you have some thing more creative , please share with odiexperts followers.

There is an easy way of writing query inside ODI

Right click on the any datastore of the required technology on which you wish to run the query  and select Data.

Click on the  SQL (?) on the Top Left  and type in your query  inside the query box and get results in the  result box as you can see in the above screen .

Have fun and enjoy and look odiexperts for more tips and tricks.