Execute Shell Script through ODI

Here we tried to focus how an external program can be executed through ODI.
In many of scenarios user may have requirement to create some external program like JAR ( java) , Batch , Shell or PL/SQl proc & to be a part of ODI execution process user would need to add these in package.


The following example demonstrates to execute shell through ODI.

Suppose a user created shell program at the following location on Unix box 
/E/Common/x_load_utility/Load.sh


1. First step is to determine that Unix box server should be attached to an ODI  AGENT so that user can execute any program of a unix sever


2. Create a package in ODI  & add a step ODI-OS Command from tool box window.


3. Command to execute :  Add the location & parameter (V_LOAD optional) required to execute the shell script i.e


/E/Common/x_load_utility/Load.sh V_LOAD


Output File : This step will generate a log for every execution at location .

/E/Common/x_load_utility/Load.log

Error File :  This step will generate a error log if any error is encountered at location.

/E/Common/x_load_utility/Load.err

Append to Output file : If yes then it will keep on adding/maintaining logs of each process in out file.

Synchronous : Yes , if step need to be serialized  or No if  step need to run in parallel.

Variable/dynamic based options in ODI

Why variable/dynamic based option ..?

 Lets take a very simple example for this...

Suppose user wants to perform loading from source to target. There are two types of loading

"Initial load" ( first time) target table will be empty & only insert will be performed on target.

"Regular Load" it will be performed after initial & both insert or update may happen on target.

If developers  create option "load_option"  then at a time he can perform only one type of load either "initial" or "regular"

OR

 User have to develop two interfaces & run either of one depending upon type of load. But in this two interfaces are required ( code replication & maintenance overhead)

This can be achieved through a single interface & create variable/dynamic options & its value will be supplied at the time of execution.

Step1: Create A variable "v_load_type" for load type.
In refresh tab :select '#v_load_type' from dual 

Step2. Create option v_option in IKM.

Step3 .Open IKM   ( oracle incremental update)  ,

Add/replace existing step  in KM

1.    /* if regular then do update else don't apply update */

  declare

  dummy_var varchar2(10);

   begin

    <@ if ("regular".equals("<%=odiRef.getOption("v_option")%>")) { @>

/* Put the script code when "v_option" value is "regular" or "not null" according to option condition ,which user want to apply*/

<@ } else { @>
 /*  put else condition script code which user want to apply */

 select null into dummy_var from dual;

<@ } @>

end;

 Step4.

In Interface put Variable #v_load_type in v_option

Step5 Make a package 

a. Refresh variable v_load_type

b. Next step add interface.

c. Create a package scenario with variable parameter v_load_type

Perform "Initial load" : 

Pass parameter 'initial' to the scenario ,only Insert script will be generated/update will not take place

So Finally an option can be made dynamic through appending code

declare

dummy_var varchar2(10);

begin

 <@ if ("regular".equals("<%=odiRef.getOption("v_option")%>")) { @>

--------- Put the code to be genetaed or executed...

<@ } else { @>

select null into dummy_var from dual;

<@ } @>

end;

Options in ODI KM's/Procedures

Loading of File: End of line is not specified

Purpose of options in ODI: Options plays a vital role & though this conditional ( just like if,cases in sql) based queries/codes can be generated for loading purposes.

I have a scenario where my source is a File  ( fixed file -position based records) & its EOL( end of line) is not specified). All the records are appearing in the same line.
The length of record is 8 so below text contain two records
ABCD1234QRST5421

Layout or record C1(1:2),C2(3:4),C3(5:8)
record1: ABCD1234
record2: QRST5421

create datastore for file ABC

Now I want to load the records using LKM file to Oracle(sqlldr).

Step1: First Add an option (FIXED_REC_LENGTH) in LKM which will hold length of record.
Step2: Go to "Generate CTL file" command in LKM & change the code.
If
 Fixed record length option is not null  then it will generate control file with fixed record length
else
It will perform generate normal ODI control file code.

Apply the changes in control file & save it.

Now Create a interface for load file to oracle & in the flow diagram set option "FIXED_REC_LENGTH"
as 8

Choose appropriate IKM ( sql control append) & execute the interface.

CTL generated through LKM is


SnpsOutFile "-File=C:/SNAPON/ABC.ctl"

OPTIONS (
SKIP=0,
ERRORS=0,
DIRECT=FALSE
)
LOAD DATA
INFILE "C:/SNAPON/EOL.txt"  "FIX 8"
BADFILE "C:/SNAPON/ABC.bad"
DISCARDFILE "C:/SNAPON/ABC.dsc"
DISCARDMAX 1
INTO TABLE SNAPON_W.C$_0ABC
(
C2_C1 POSITION(1:2),
C1_C2 POSITION(3:4),
C3_C3 POSITION(5:8)
)

Check target table & 2 records inserted through load.

In next post will we discuss "Variabale" based "Options".

Variable/Dynamic based data sever connection in ODI topology

We can design Variable based connection in ODI Topology manager.

Why variable based connection...?

A. lets say user have requirement  to create some common components ( e.g. Knowledge modules) in ODI which can used across all the different products or projects.

B. Now different projects have different data severs & different data severs ( physical & logical) will be associated with different environments  like production, development.

C. Usually in Any software domain ( banking,automobile,retail etc) developers tries to develop a component which can be used across all the products/projects. 

Example:

 Lets assume source & target databases for 'n' number of projects & all products or projects have same type of  database tables DDL structure.

Source : Oracle & Target : Postgresql

so 'n' number of data base connections required in topology manager ? 

'n' number of interfaces or packages required to perform load to target ?

NO, just one connection (topology manager) & one package(designer) to perform load.

Create a metadata table 'Metadata' in oracle which will contain all the source & target database details like ( sever name,port,etc) & add one column for Primary Key ( numeric)  

Create variables such as, for source schema V_SRC_SCHEMA & V_SRC_database, V_SRC_port,etc & similarly for target V_target .
Create a variable for primary key column defined in metadata table & refresh all source & target variables on the basis of Primay Key. 

Create only one connection in topology manager for source & target with the variables created above & create variable based logical schema too...
Create Models with variable based logical schema
Now create a interface to load data from source to target

Finally put the variables ( Refresh Mode) & Interfaces in the final package & generate Scenario with variable parameter Primary key .

Now Scenario is ready for use, Just pass the of the primary key value of the connection sever ( defined in meta data table) to the scenario & DATA will be loaded from SOURCE --> TARGET for that product/project................

In next phase i will attach screenshot for this ............

Executing Scenario through Web Agent

There are number of ways to execute Scenario.
a. Designer
b. Operator
c. Metadata Navigator.
d. Web agent

In this Section we will be focus on Web Agent & hardly it will took  few minutes to launch web agent & execute scenario through them.  It is always recommended that to use Option B & C in the real time production environments


The advantages of Launching scenario through web agent is:
It is very simple & fast to configure.
Doesn't require any maintenance & easy to debug.
In real time it can be used by Testers.


To Launch agent first we need to set up the parameters in odiparams.bat or sh file at ODI installed location
 i.e C:\OraHome_1\oracledi\bin
set the following parameters

STEP A

rem
rem Repository Connection Information
rem
set ODI_SECU_DRIVER=oracle.jdbc.driver.OracleDriver
set ODI_SECU_URL=jdbc:oracle:thin:@localhost:1521:NEERAJ
set ODI_SECU_USER=SNAPON_M
set ODI_SECU_ENCODED_PASS=fDyXo2Vl73xS4g932Hags
set ODI_SECU_WORK_REP=REP_SNAPON_W
set ODI_USER=SUPERVISOR
set ODI_ENCODED_PASS=LELKIELGLJMDLKMGHEHJDBGBGFDGGH

Now there is a question how to get encoded or encrypted password for password parameters.
This is very simple open the command prompt --> go to C:\OraHome_1\oracledi\bin -->
Now run
agent ENCODE <password>
you will get encoded password


Make sure your all the JAVA HOME & ODI_JAVA_HOME is set already 

-SECU_DRIVER=<driver name>	The JDBC driver used to access the master repository.
-SECU_URL=<url>	The JDBC URL used to access the master repository.
-SECU_USER=<user>	The database user for the master repository connection.
-SECU_PASS=<password>	Password of the user for the master repository connection. This password must be encrypted using the command agent ENCODE <password>.
-WORK_REPOSITORY=<work repository name>	Name of the work repository that contains the scenarios to be executed.

STEP B.
 open the command prompt --> go to ODI bin location i.e C:\OraHome_1\oracledi\bin -->

 execute the batch file agentweb with parameter

“-port=20300” “-web_port=8080”.

STEP C.

Now in the browser  & open the link

http://localhost:8080/

Now Launcher is ready for use.

Please follow the link http://gerardnico.com/doc/odi/webhelp/en/usermanual/agent/topo_agent_startweb.htm

Previous Session/Scenario/Package details in ODI ( oracle data Integrator)

odiRef.getPrevStepLog: oracle data Integrator


In this artice i will try to explain how to extract the log/details for mutiple scenarios in ODI.
What is odiRef.getPrevStepLog ?
This ODI inbuilt function helps to identify or create reports at package/scenario level.
odiRef.getPrevStepLog : Returns information about the most recently executed step in a package. The information requested is specified through the parameter. If there is no previous step (for example, if the getPrevStepLog step is executed from outside a package), the exception "No previous step" is raised. 


I will try to explain one of the scenario where it can be used.
for an example suppose I have a main package/scenario "COPY_OF_PKG_LOAD_SUZ_CORNERSTONE" which includes/calls  Scenario "COPY_OF_PKG_SUZ_ORA_PGSQL version 001".

I want to create the log for the Scenario "COPY_OF_PKG_SUZ_ORA_PGSQL version 001".
Add these codes (in the image below )in the email body ( the mail notification step which i have already posted in previous article "Email Reporting in ODI" )  or the ouput log file & add it to the main pacakage "COPY_OF_PKG_LOAD_SUZ_CORNERSTONE"



I have added these deatils in my SUCCESS & FAILURE notification email step and added it to the main package "COPY_OF_PKG_LOAD_SUZ_CORNERSTONE"

Previous Session/Scenarion/Package details


MESSAGE: <%=odiRef.getPrevStepLog("MESSAGE")%>
step name: <%=odiRef.getPrevStepLog("STEP_NAME")%>
Session No: <%=odiRef.getPrevStepLog("SESS_NO")%>
Steps Number: <%=odiRef.getPrevStepLog("NNO")%>
Step Type: <%=odiRef.getPrevStepLog("STEP_TYPE")%>
Step Begin: <%=odiRef.getPrevStepLog("BEGIN")%>
Step End: <%=odiRef.getPrevStepLog("END")%>
Step Duration: <%=odiRef.getPrevStepLog("DURATION")%>
Step Status: <%=odiRef.getPrevStepLog("STATUS")%>

MESSAGE: Error log if there is any error / or empty if COPY_OF_PKG_SUZ_ORA_PGSQL version 001 executed successfully.  Error message returned by previous step, if any. Blank string if no error.

step name:   Name of the previous step of the package "COPY_OF_PKG_SUZ_ORA_PGSQL version 001'

Session No: Session number of previous step

Steps Number:  Number of steps

Step Type:  

• F: Interface
• VD: Variable declaration
• VS: Set/Increment variable
• VE: Evaluate variable
• V: Refresh variable
• T: Procedure

Step Begin:  The date and time that the step began

Step End: The date and time that the step terminated

Step Duration: Time the step took to execute in seconds

Step Status: Returns the one-letter code indicating the status with which the previous step terminated. The state R (Running) is never returned.

• D: Done (success)
• E: Error
• Q: Queued
• W: Waiting
• M: Warning

I created the Scenario for the package "COPY_OF_PKG_LOAD_SUZ_CORNERSTONE" & executed it. 
THe OUPUT of odiRef.getPrevStepLog step which i recieved through email is :

Failure loading of Package

Session Name:           COPY_OF_PKG_LOAD_SUZ_CORNERSTONE

Session Version:  002

Session No:       21768001

Context:          Development

Failure details

step name:  Execution of the Scenario COPY_OF_PKG_SUZ_ORA_PGSQL version 001

Session No: 21768001

Steps Number:     1

Step Type:  SE

Step Begin: 2010-11-08 01:57:03.644

Step End:   2010-11-08 01:57:05.84

Step Duration:    2

Step Status:      E

Error Message:    java.lang.Exception: The scenario did not end properly.

      at com.sunopsis.dwg.dbobj.SnpScen.a(SnpScen.java)

      at com.sunopsis.dwg.dbobj.SnpScen.localExecuteSync(SnpScen.java)

      at com.sunopsis.dwg.tools.StartScen.actionExecute(StartScen.java)

      at com.sunopsis.dwg.function.SnpsFunctionBaseRepositoryConnected.execute(SnpsFunctionBaseRepositoryConnected.java)

      at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execIntegratedFunction(SnpSessTaskSql.java)

      at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)

      at com.sunopsis.dwg.dbobj.SnpSessTaskSqlS.treatTaskTrt(SnpSessTaskSqlS.java)

      at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)

      at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)

      at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)

      at com.sunopsis.dwg.cmd.DwgCommandScenario.treatCommand(DwgCommandScenario.java)

      at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)

      at com.sunopsis.dwg.cmd.e.k(e.java)

      at com.sunopsis.dwg.cmd.h.A(h.java)

      at com.sunopsis.dwg.cmd.e.run(e.java)

      at java.lang.Thread.run(Thread.java:619)

Email Reporting in ODI ( Oracle Data Integrator)

Success/Failure Notification Of scenario/Package through Email.


I would describe how to generate scenario notification through email.
In figure below there is main package which contains two scenario's A & B and two email notifications one for success & other for failure.

Failure email notification will be generated when the execution of the main package fails & similarly the Success email notification will be generated when the package is executed successfully. 


1.Just Drag the Scenario A & B within the main package for which success/failure notification is to be sent.


2. From tool box drag the OdiSendMail & name it as SUCCESS.
3. From tool box drag the OdiSendMail & name it as FAILURE.
4. Open the properties tab of SUCCESS  add the following details.



Mail Server: 162.22.120.56
From:         ODI@gmail.com
To:                  [neeraj.singh@gmail.com]
Cc:                neeraj.singh@gmail.com
BCc:              neeraj.singh@gmail.com
Subject::  SUCESSFULLY EXECUTED:<%=odiRef.getSession( "SCEN_NAME" )%>  Load.
Attcahment: ABC.xml
Body:
Session Name: <%=odiRef.getSession( "SCEN_NAME" )%>
Session Version: <%=odiRef.getSession( "SCEN_VERSION" )%>
Session No: <%=odiRef.getSession( "SESS_NO" )%> 
Context: <%=odiRef.getSession( "CONTEXT_NAME" )%>
Session Begin: <%=odiRef.getSession("SESS_BEG")%>


4. Open the properties tab of FAILURE add the following details.

Mail Server: 162.22.120.56

From:         ODI@gmail.com

To:                  [neeraj.singh@gmail.com]

Cc:                neeraj.singh@gmail.com

BCc:              neeraj.singh@gmail.com

Subject::  FAILED :<%=odiRef.getSession( "SCEN_NAME" )%>  Load.

Body:

Session Name: <%=odiRef.getSession( "SCEN_NAME" )%>

Session Version: <%=odiRef.getSession( "SCEN_VERSION" )%>

Session No: <%=odiRef.getSession( "SESS_NO" )%> 

Context: <%=odiRef.getSession( "CONTEXT_NAME" )%>

Session Begin: <%=odiRef.getSession("SESS_BEG")%>

5. Link Scenario  "B" to the step "SUCCESS"  through the "OK" ( Next step on success) present on the upper pane 
6. Link Scenario  "B" to the step "FAILURE"  through the "KO" ( Next step on failure) present on the upper pane 




7. Now generate the scenario for the main package example "C"  with version "001".




Execute the Scenarion "C".


If it is successfully executed you will recieve the success email.

If it is failed you will recieve the Failure email.

You can add & modify the notification reports. For this you can do some tricky things
for example you do the following tasks.
can export the log through attachment.
can create the log of the previous step through "<%=odiRef.getPrevStepLog( <> )%>"
can the add the target schema & source schema deatils through


"<%=odiRef.getSchemaName("D")%>" & "<%=odiRef.getCatalogName( "D")%>"