ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

August 25, 2010
by Cezar Santos
26 Comments

Categories: How to , Logic , ODI , Tips and Tricks

Tags: , , , , , ,

Single Post View


Calling OdiSqlUnload using ODI Procedure with no hardcoded password

Hello all,

OdiSqlUnload is a great utility for unloading  but then we need to hardcoded and provide the parameters of the username and password which can change in the Other Environment in ( Testing , UAT or Prod ) . Here is a small trick to handle that situation.

In the Command on Source, Select the Required Technology and Schema name .

Copy the Syntax of the OdiSqlUnload into an ODI procedure into Command on Target  with Sunopsis API as the Technology

OdiSqlUnload “-FILE=” “-DRIVER=<%=odiRef.getInfo(“SRC_JAVA_DRIVER”)%>” “-URL=<%=odiRef.getInfo(“SRC_JAVA_URL”)%>” “-USER=<%=odiRef.getInfo(“SRC_USER_NAME”)%>” “-PASS=<%=odiRef.getInfo(“SRC_ENCODED_PASS”)%>” “-FILE_FORMAT=VARIABLE” “-FIELD_SEP=,” “-ROW_SEP=rn” “-DATE_FORMAT=yyyy/MM/dd HH:mm:ss” “-CHARSET_ENCODING=ISO8859_1” “-XML_CHARSET_ENCODING=ISO-8859-1” “-FETCH_SIZE=5000” “-QUERY=select * from QUERY”

This way ODI will automatically get the parameters from the topology and also use the Fetch_Size 5000 for good performance .

Also look into this post for generating header in OdiSqlUnload – http://odiexperts.com/?p=1845

Just a small trick . Look for more ideas at odiexperts.com

August 19, 2010
by Cezar Santos
12 Comments

Categories: Administration , Architecture , Common Errors , ODI , ODI Bug , Tips and Tricks

Tags: , , , , , , ,

Single Post View


SNP_EXP_TXT – A “huge” table problem

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):

WARNNIG: THIS IS A DML AGAINST ODI WORK REPOSITORY TABLES. ONLY EXECUTE IT WITH ALL TABLES IN UNION SUB-QUERY. IF NOT, YOU CAN DELETE VALID RECORDS AND SEVERLY DAMAGE YOUR ODI INSTALATION UP TO UNRECOVERY STATE.
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
                   union all
                         select DISTINCT I_TXT_VAR_T as I_TXT
                          FROM SNP_VAR_DATA
                  ) 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!

August 17, 2010
by kdevendr
4 Comments

Categories: Administration , Architecture , How to , ODI , Technology , Tips and Tricks

Tags: , , , ,

Single Post View


Internal ID

This post is all about explaining how the internal Id in ODI plays and acts and what is the best practice and what actually happens when most of the error occurs.

Internal Id is an important element in ODI when its comes to Import Export of Object  and so a good understanding of it can help us save time and above all errors related to Export Import . Internal ID apart being an incrementing code also have relationship to Work and Master Repository.

If you are keen on knowing how the internal Id objects have relation to Work (WR)and Master Repository. Here is the key , when you define your 2 or 3 digit number as the Internal ID to Repository,  this number(WR Internal ID )  acts like a base in all the object.

Lets say my internal id for Work Repository is 200 .
My project will have the Internal Id – 1200
My Folder will have Internal Id of -2200
Interface1 – 2200
Interface2- 3200
Interface3- 1200
Variable1- 2200.

If you have notice all the  different objects have suffix 200( WR Internal ID) to their incrementing number.

Note : ODI can have same internal Id for different objects but never for the same object , since while storing into the SNP work repository tables it increments accordingly by 1000 or other number as defined in its code.

Similarly its go for the Master Repository.

Lets say my Master Repository internal id 50 .
Data Server 1- 2050
Data Server 2 – 3050
Physical Schema1 – 1050
Physical Schema2 – 2050
Agent1-1050

[Please note some of the Default Data server created while Initial ODI installation( Ex – XML_GEO_DIM , File Generic etc ) will have different Internal ID not matching to your Master Repository IDs. ]

We hope till now , you would have got an good understanding of ODI objects , Repository Internal ID and their relationship .

Now let us cover the importance and the simple reason why Repositories needs to have different Internal ID and which is the highly suggested and the best practise to do with few simple scenarios.

Scenario 1. Two DWR having the same internal ID.

Let  us call them DWR1 and DWR2. They both have the same Internal Id of 200 connected to different Master Repository.

Let say the development is being carried on both the repository or even the Development and Testing environment are both DWR .

Let say we  have reveresed engineered datastore
Source Data Store – ABC (121200)
Target Data Store – TRG (321200)
and created the interface
Interface – XMT_Interface (143200)

The  interface is ready and some body in the DWR2  needs the same Interface for their development build, but unfortunately some one have already reverse engineered the same in the DWR and their internal id as follows .
Source Data Store – ABC (122200)
Target Data Store – TRG (322200)

When the Developer brings in the Interface XMT_Interface thinking that already the Data store exists in the DWR2 , it will fail because of improper links , since  Interface will look for  Datastore ABC (121200) and TRG (321200) and KM and when they are different it error out.
To solve this  either the developer have to bring in Duplicate mode and also change the Source Data stores. In short he pretty much needs to rebuild the whole interface again.

The other sub scenario can be Interface with the same ID can exists with different name and code. Again the Developer need to import in duplicate and assign the required datastore , in short rework .

Scenario 2. Two DWR having different internal ID.

Let me call them DWR1 and DWR2. They both have the different internal id of 200 and 201 respectively.

Now let say the developer is moving the Interface from the DWR1 to DWR 2. Well even now the interface will fail , not becuase of  Interface id but becuase of the datastore can be different in the DWR2.

To solve this we need to import the data store too but that’s like having two different data stores for single purpose.

Well here the steps to handle in such a scenarios.

Step1. Sync Global Variable and other Global ODI objects , Model and Datastore when imported in One DWR to another DWR immediately or at the End of the Day.
Step2. Import the Codes in this order Project, KM, Local Variable, Procedure ,Interface and Packages for sync [ if required ]

Well with these above conditions in place, the Developer can easily migrate the code since
Source Data Store – ABC (121200)
Target Data Store – TRG (321200)
Will be present and also being a different internal id we can use Insert_Update and import the codes.

Above all, what ever is the case, have different internal Id for Work Repository.

Scenario -3 Importing from DWR to EWR having the same internal ID. (100)

Lets take an Example where a Developer in the EWR makes a duplicate of the present scenario ( 12100) and ODI starts to define a new Internal ID but here comes the issue  , that

scenario internal id can be already present  or in future when trying to import makes an Error ,since we have created a duplicate interface with that ID.

Scenario -4 Importing from DWR to EWR having the different internal ID. (200 and 201 )

If in case the EWR would have had a different internal id DWR (200) and EWR (201) , so when the Developer make a duplicate the new id will be with some thing like this (12201) and so its does not updates or modify or error out because of an existing object or even when importing scenarios in the future ,since the base ODI have scenarios with numbers ending in 200 .

Similarly the Internal ID can impact for different ODI objects in Topology and Security Manager, so use different Internal ID.

I hope this post have given you an understanding and the relationship of Internal Id , its generation method and why the objects gets error out.

Moral of the Post – Create Different internal Id for both Work Repository and Master Repository always .

Look for odiexperts.com for more tips, tricks and best practices.

August 10, 2010
by kdevendr
27 Comments

Categories: How to , Knowledge Modules , Logic , ODI , Tips and Tricks

Tags:

Single Post View


SCD Type 2

Hi Guys,

Today we are going to talk about an old question that we already got several, hundreds, thousands (lol) of questions but, at true, is pretty easy!

I would like to indroduce you to the Mr. SCD!!!!!

In few lines, you will be able to configurate and use it…. Let’s start:

This post defines the necessary setting to use the Oracle Slowly changing dimension .

image

Set the columns with required properties namely,

SURROGATE_KEY – Sequence

NATURAL_KEY – Primary or Unique Key

OVERWRITE ON CHANGE – Column for which data needs to be updated

ADD ROW ON CHANGE – Column for which a new Surrogate Key and New rows to generated when the data for this Column changes

CURRENT RECORD FLAG – 0 or 1  .  The KM populates this column so no need to populate this column .(1 : Current Record , 0 : Past Records)

STARTING TIMESTAMP – Column  indicating the beginning of a Record’s availability. use SYSDATE or SYSTIMESTAMP depending on the datatype.

ENDING TIMESTAMP -Column indicating the end of the Record’s availability. The value is a default of “01 January 2400”

image

image

image

image

image

image

image

image

Map the columns in this way where the Surrogate Key needs to be sequence .  The ending timestamp and Current Flag columns  need to be mapped even that the  KM will populate those column accordingly. Just add any value to them.

Note :

1. If in case you don’t  have any ‘Overwrite on change‘ column , please check the option ignore errors on Update Existing row., since in the select statement it wont find the column to do query and will error out.

2. If the Target datastore is table , Flow control or static control have to yes and the appropriate CKM have to be selected , else there will be error in Historize old rows saying ‘invalid user.table.column, table.column, or column specification”.

Well, as you can see, no secrets to use it!!!!

August 5, 2010
by Cezar Santos
7 Comments

Categories: Administration , Architecture , How to , ODI , Tips and Tricks

Tags: , , , , , , , , ,

Single Post View


11g Oracle Data Integrator – Part 2/11g – Creating Repositories

Hello Dear Readers 😉

Nothing better to start our analysis that the real first step to start an Oracle Data Integrator 11g environment. It’s means REPOSITORIES!

The visual interface is all new but the functionality is just the same… I liked this new interface to create “ODI things” once it’s just one place to do all.

The big difference from 10g version is that, now, is possible to define a “external user validation” since the Master Repository creation. Pretty good to security protocols!

Let’s show you the screen and process to create the repositories… it will allow all of you to see the new design and learn how to manipulate it:

When you First Login to ODI Studio, you will see an option to connect to Repository.

 

Creating Master Repository

File – > New -> Master Repository Creation Wizard


For this post we are going with ODI authentication , the default  SUPERVISOR password is still  SUNOPSIS.


Click Finish for ODI to create the required Master Repository. Once completed Login into Topology.


Click Connect to Repository and furnish the required information,similar to 10g

Creating Work Repository

Once logged in , Go to Repositories and Right click on  Work Repositories  – > New Work Repository


You can specify the password for ODI Work Repository , as any one trying to open the Work Repository need to enter the password created now.


Provide the ID and Name of the Work Repository and click Finish and once created .


Click Connect to Repository and provide the necessary details and also selec the Work repository and once done Login into Designer.

Well guys, as you can see, it’s not so different from previous version but, in my opinion, looks really better!!!!!

See you all in the “3/11g post”

August 5, 2010
by Cezar Santos
3 Comments

Categories: Administration , Architecture , How to , ODI , Tips and Tricks

Tags: , , , , , , , , ,

Single Post View


11g Oracle Data Integrator – Creating Repositories – Part 2/11g

Hello Dear Readers 😉

Nothing better to start our analysis that the real first step to start an Oracle Data Integrator 11g environment. It’s means REPOSITORIES!

The visual interface is all new but the functionality is just the same… I liked this new interface to create “ODI things” once it’s just one place to do all.

The big difference from 10g version is that, now, is possible to define a “external user validation” since the Master Repository creation. Pretty good to security protocols!

Let’s show you the screen and process to create the repositories… it will allow all of you to see the new design and learn how to manipulate it:

When you First Login to ODI Studio, you will see an option to connect to Repository.

 

Creating Master Repository

File – > New -> Master Repository Creation Wizard


For this post we are going with ODI authentication , the default  SUPERVISOR password is still  SUNOPSIS.


Click Finish for ODI to create the required Master Repository. Once completed Login into Topology.


Click Connect to Repository and furnish the required information,similar to 10g

Creating Work Repository

Once logged in , Go to Repositories and Right click on  Work Repositories  – > New Work Repository


You can specify the password for ODI Work Repository , as any one trying to open the Work Repository need to enter the password created now.


Provide the ID and Name of the Work Repository and click Finish and once created .


Click Connect to Repository and provide the necessary details and also selec the Work repository and once done Login into Designer.

Well guys, as you can see, it’s not so different from previous version but, in my opinion, looks really better!!!!!

See you all in the “3/11g post”

August 3, 2010
by kdevendr
9 Comments

Categories: How to , Logic , ODI , Tips and Tricks

Tags: , , , , ,

Single Post View


Regenerate ODI Objects using OdiGenerateAllScen

Hi,

One more little trick!

ODI objects such as Packages, Interface, Variable and Procedure scenarios can be regenerated without manually regenerating individual scenarios using OdiGenerateAllScen.

Drag in the OdiGenerateAllScen and select the Project and select Yes to Generate Package Scenarios and name it as Regenerate Package, similarly do so for other Object types and join them as shown below. The only advantage if you wish to regenerate just one object type you execute that particular step.

Else call it Regenerate and select yes to all the option so that regeneration of all the objects can be done at once.

Fast and simple!

July 31, 2010
by kdevendr
8 Comments

Categories: How to , Logic , ODI , Reports , Tips and Tricks

Tags: , , ,

Single Post View


Generate Column_name Header for OdiSqlUnload

Last week we have shown you the code for generating the Header and populate the same in OdiSqlUnload file , but the codes seems to be limited to the Database and then separate query needs to be created for each database reading from the System table,which doesn’t makes things so easy.

In order to solve it and to make the codes independent, we have revisited and rewritten the whole codes to generate header.

All you need to is to write the query you have used in the OdiSqlUnload .

import string
import java.sql as sql
import java.lang as lang
import re
sourceConnection = odiRef.getJDBCConnection("SRC")
output_write=open('c:/output.txt','r+')
myStmt = sourceConnection.createStatement()
my_query = "select * FROM ODI_WORK.SNP_TABLE"
my_query=my_query.upper()
if string.find(my_query, '*') > 0:
    myRs = myStmt.executeQuery(my_query)
    md=myRs.getMetaData()
    collect=[]
    i=1
    while (i  0:
    header = my_query[7:string.find(my_query, 'FROM')].replace("||','||",',')
else:
    header = my_query[7:string.find(my_query, 'FROM')]

print header
old=output_write.read()
output_write.seek(0)
output_write.write (header+'n'+old)

sourceConnection.close()
output_write.close()

Note :
The codes presently support only this type of queries format
select * from table_name (or)
select column1,column2 from table_name (or)
select column1||’,’||column2 from table_name

image

After running the above script for all columns

image

( or ) for fewer columns

image

Usefull? 🙂

Look for odiexperts for more tips and tricks.

Thanks for visiting us!

July 30, 2010
by kdevendr
6 Comments

Categories: Common Errors , How to , Logic , ODI , Reports , Tips and Tricks

Tags: , , , , ,

Single Post View


Error Records Log

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("----------------------------------------------------------- nn")
#---------------------------------------------------------------------------
sqlstmt="SELECT 'Errored Interface t- '||SUBSTR(ORIGIN,INSTR(ORIGIN,')')+1,
LENGTH(ORIGIN))||'nError Message tt- '||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='nn'.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 ]

image

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

image

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

image

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…

July 30, 2010
by kdevendr
4 Comments

Categories: ODI

Single Post View


Get the Error Message of any Failed ODI objects inside Package

When we have multiple Interface, Variable  and other ODI objects inside the Child Package  and when such Child Package Fails we get the Error message ‘The scenario did not end properly’  and we need to look into the operator .

Here is a simple code  and process which can look into the Child Session and get the correct error message and also later we can capture the same in the Error Log .

 image

Create a Global Variable called “ GET_ERROR_MSG” and paste the following codes ,  so you can call and use it for multiple package.

SELECT ' The  '||CASE
WHEN SSS.STEP_TYPE='F' THEN 'INTERFACE'
WHEN SSS.STEP_TYPE='VD' THEN 'VARAIBLE DECLARATION'
WHEN SSS.STEP_TYPE='VS' THEN 'SET/INCREMENT VARAIBLE'
WHEN SSS.STEP_TYPE='VE' THEN 'EVALUATE VARIABLE'
WHEN SSS.STEP_TYPE='V' THEN 'REFRESH VARIABLE'
WHEN SSS.STEP_TYPE='T' THEN 'PROCEDURE'
WHEN SSS.STEP_TYPE='OE' THEN 'OS COMMAND'
WHEN SSS.STEP_TYPE='SE' THEN 'ODI TOOL'
WHEN SSS.STEP_TYPE='RM' THEN 'REVERSE ENGINEERING MODEL'
WHEN SSS.STEP_TYPE='CM' THEN 'CHECK MODEL'
WHEN SSS.STEP_TYPE='CS' THEN 'CHECK SUB MODEL'
WHEN SSS.STEP_TYPE='CD' THEN 'CHECK DATA STORE'
WHEN SSS.STEP_TYPE='JM' THEN 'JOURNALIZE MODEL'
WHEN SSS.STEP_TYPE='JD' THEN 'JOURNALIZE DATA STORE'
END||' '||SSS.STEP_NAME||
' , has failed due to - '||
SNET.TXT
FROM <%=odiRef.getObjectName("SNP_SESSION")%> SS,
<%=odiRef.getObjectName("SNP_SESS_STEP")%> SSS,
<%=odiRef.getObjectName("SNP_EXP_TXT")%> SNET
WHERE SSS.SESS_NO=SS.SESS_NO
AND SNET.I_TXT=SS.I_TXT_SESS_MESS
AND SNET.TXT_ORD=0
AND SS.PARENT_SESS_NO ='<%=odiRef.getPrevStepLog("SESS_NO")%>'

 
[ Note :select the Work repository schema in the variable so that getobjectname can calculate accordingly the values, if in case you are using other schema which have select permission on work Repository schema , replace <%=odiRef.getObjectName(“”)%> with the required schema name. ]

In the first step call an OdiOutFile and uncheck Append option so that new File is created for a daily run  with  Header and Date .

image

Call the Child package and call the Variable   and  join the KO step ( red) to the Variable so that when ever the Child Package fails only then the variable is called else process the next child Package.

In the next step , call the OdiOutFile and mention the path to File mentioned in Step 1 and in the Text mention the variable name and keep repeating the steps for multiple child packages.

image

This way you can capture all the Failed ODI objects Error Messages into Single Log and send them as an attachment through OdiSendMail.

 image

« Previous12345678910...13Next »