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!

There are lots of users who are using Windows 7 on their personal machine and being a latest product  there is not much support from the codes of Jython causing some issues . Stating that i recently came across a small issue and i would like to share with you all and provide the solution.

In ODI , we can call Windows and Unix based command using Jython  and it uses the  OS.getOSType() to identify  the Operating system . If the Operating systems are Windows based it identifies them as ‘ NT’  and executes cmd  and if they are Unix or Linux based it identified them as ‘ POSIX ‘  and execute shell scripts.

In ODI , this os.getOSType() is stored in the  python file – oracledi\lib\scripting\Lib\javaos.py.

_osTypeMap = (        ( "nt", r"(nt)|(Windows NT)|(Windows NT 4.0)|(WindowsNT)|"                r"(Windows 2000)|(Windows XP)|(Windows CE)|(Windows 2003)"),         ( "dos", r"(dos)|(Windows 95)|(Windows 98)|(Windows ME)" ),        ( "mac", r"(mac)|(MacOS.*)|(Darwin)" ),        ( "None", r"(None)" ),        ( "posix", r"(.*)" ), # default - posix seems to vary mast widely        )

While i was trying to call the startcmd.bat  in windows 7 i was getting an error like this

OSError: (0, ‘Failed to execute command ([\’sh\’, \’-c\’, )

java.io.IOException: Cannot run program "sh": CreateProcess error=2, The system cannot find the file specified’)

which is getostype()  considers Windows 7  as ‘POSIX’ and so its searching for shell  scripts and throwing me the above error

The resolution is simple ,  just add Windows 7 to the list into the file – oracledi\lib\scripting\Lib\javaos.py  ( Line No 302 )

_osTypeMap = (        ( "nt", r"(nt)|(Windows NT)|(Windows NT 4.0)|(WindowsNT)|"                r"(Windows 2000)|(Windows XP)|(Windows CE)|(Windows 2003)|(Windows 7)"),         ( "dos", r"(dos)|(Windows 95)|(Windows 98)|(Windows ME)" ),        ( "mac", r"(mac)|(MacOS.*)|(Darwin)" ),        ( "None", r"(None)" ),        ( "posix", r"(.*)" ), # default - posix seems to vary mast widely        )

Looking to know your os type – try this code  in jython.bat or jython.sh

import os
print os._getOsType()

There are some time when we would like to execute a single process irrespective of Operating system either Unix or Windows.  Although we can call the OS command , the other way to do that is to writing command based on the environment . We can use OS Type to get the environment.

import os
if os._getOsType() == 'nt':   
      print 'Windows environment'
else:   
      print 'Unix environment '

Say if we want to write a copy command depending on environment  so the above command be re-written as

import os
if os._getOsType() == 'nt':   
      os.command(r “ copy /r source_path dest_path”)
else:   
      os.command (r “ cp -f source_path dest_path” )

Look in odiexperts for more tips and solutions.

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.

This post came from curiosity to try some thing and digging into the code of the ODI java and XML and  Please dont try this in your Production box. This is completely experimental .

When we don’t have latest driver in the ODI , we copy the driver to ODI  and write our own JDBC Url  and driver information. 

This post is about editing the an DriverRefV3.xml and making a manual entry into ODI Drivers  List. Just add the below codes at the end of  DriverRefV3.xml. The File is stored under the  sunopsis zip folder , this where all the main class and java files of odi is located.

Try this at your  home system or your personal development but never in the production box.

The XML File path is  -  >

C:\OraHome_1\oracledi\lib\sunopsis\com\sunopsis\res\DriverRefV3.xml

[ Note – : Before doing this Please back up a clean copy  of  DriverRefV3.xml  file ]

The File have an  two main Object Class namely  com.sunopsis.dwg.dbobj.SnpDriver containing information about  JDBC Driver.

The other Object Class is com.sunopsis.dwg.dbobj.SnpUrl containing details about JDBC Url .

For this Example i am adding the JDBD Driver 2.0 for SQL Server 2005 and later. copy the below code and make accordingly changes.

<Object class="com.sunopsis.dwg.dbobj.SnpDriver">

<Field name="IDriver" type="com.sunopsis.sql.DbInt"><![CDATA[31]]></Field>

<Field name="SunopsisCompatibility" type="java.lang.String"><![CDATA[1]]></Field>

<Field name="DriverLabel" type="java.lang.String"><![CDATA[Microsoft SQL Server 2005 & later Driver for JDBC]]></Field>

<Field name="DriverName" type="java.lang.String"><![CDATA[com.microsoft.sqlserver.jdbc.SQLServerDriver]]></Field>

<Field name="DriverDesc" type="java.lang.String"><![CDATA[Microsoft SQL Server 2005 Driver for JDBC (Type 4).

Use this driver to connect to Microsoft SQL server 2005 & later .

Available at http://www.microsoft.com/sql/downloads

]]></Field>

<Field name="FirstDate" type="java.sql.Timestamp">null</Field>

<Field name="FirstIUser" type="com.sunopsis.sql.DbInt"><![CDATA[null]]></Field>

<Field name="LastDate" type="java.sql.Timestamp">null</Field>

<Field name="LastIUser" type="com.sunopsis.sql.DbInt"><![CDATA[null]]></Field>

</Object>

<Object class="com.sunopsis.dwg.dbobj.SnpUrl">

<Field name="IUrl" type="com.sunopsis.sql.DbInt"><![CDATA[31]]></Field>

<Field name="IDriver" type="com.sunopsis.sql.DbInt"><![CDATA[31]]></Field>

<Field name="UrlName" type="java.lang.String"><![CDATA[jdbd:sqlserver://<host>:<port>;databaseName=<database_name>;SelectMethod=cursor;]]]></Field>

<Field name="UrlDesc" type="java.lang.String"><![CDATA[URL to connect to SQL Server 2005 or higher.

URL Parameters:

<host>: server network name or IP adress.

<port>: server port number (usually 1433).

<property>=<value>: Connection properties. Refer to the driver's documentation for a list of available properties.

Avaialbe at - http://msdn.microsoft.com/en-us/library/ms378428.aspx

]]></Field>

<Field name="FirstDate" type="java.sql.Timestamp">null</Field>

<Field name="FirstIUser" type="com.sunopsis.sql.DbInt"><![CDATA[null]]></Field>

<Field name="LastDate" type="java.sql.Timestamp">null</Field>

<Field name="LastIUser" type="com.sunopsis.sql.DbInt"><![CDATA[null]]></Field>

</Object>

 This idea would have been great  if we could have updated in the server box and reflected the same in the client as not the case in ODI . Just an experimental and wish to share with you.

If you have done or found out any such method , Please share with the odiexperts followers.