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")%>"

Shortcut To Load XML file in Oracle table

There are many ways to load the XML file in the Oracle table. Many of the users are facing the Time constraint issue while loading/extracting data from XML file.
Many ETL tools also provide automated solution . But if the size of the XML data file is large than 60 MB then a lot of time is consumed. Over here there is a solution for this & in this solution the loading of XML file in Oracle tables is divided in two parts.
For example FAMILY.XML is an raw file.
So first we will transform the XML file into the Text file and this would be done through the XSLT. To more know about the XLST please refer to the link http://www.w3.org/TR/xslt .
In second phase we load the TEXT file with the help of Control file /Sqlloader in Oracle database tables.

The Design of the workflow would be like this :-

  
Suppose FAMILY.XML  file contains attributes ;


<Family>
<FAMILY> 1</FAMILY>
<VHCL_TYPE>C</VHCL_TYPE>
<FAMILY_DESC>Colt</FAMILY_DESC>
<DESC_ORIGIN_ID>10200</DESC_ORIGIN_ID>
<DESC_TAG>0</DESC_TAG>
<ACTIVE_IND>1</ACTIVE_IND>
<UPDATE_DATE>2000-01-11T09:59:26</UPDATE_DATE>
</Family>

Add this code to the beginning of the XML file:


<?xml version="1.0" standalone="yes"?>
<?xml-stylesheet type='text/xsl' href='FAMILY.xslt'?>
<Family xmlns="http://www.ibm.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.ibm.com FAMILY.xsd">

-- Over here in the above statement  href='FAMILY.xslt'?  &  xsi:schemaLocation="http://www.ibm.com FAMILY.xsd"> 

 is the xslt file name  & xsd file name which is to be created fpr this Family.xml file.

After appending this code to the XML , file appears like this : ---

<?xml version="1.0" standalone="yes"?>

<?xml-stylesheet type='text/xsl' href='FAMILY.xslt'?>

<Family xmlns="http://www.ibm.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.ibm.com FAMILY.xsd">

<Family>

<FAMILY> 1</FAMILY>

<VHCL_TYPE>C</VHCL_TYPE>

<FAMILY_DESC>Colt</FAMILY_DESC>

<DESC_ORIGIN_ID>10200</DESC_ORIGIN_ID>

<DESC_TAG>0</DESC_TAG>

<ACTIVE_IND>1</ACTIVE_IND>

<UPDATE_DATE>2000-01-11T09:59:26</UPDATE_DATE>

</Family>

Now the second task is to create XSLT file i.e FAMILY.XSLT

<?xml version="1.0" encoding="iso-8859-1"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" 

xmlns:lxslt="http://xml.apache.org/xslt" xmlns:sL="http://www.ibm.com" 

exclude-result-prefixes="sL">

  

  <xsl:variable name="vrFamily"/>

  <xsl:variable name="vrFAMILY"/>

  <xsl:variable name="vrVHCL_TYPE"/>

  <xsl:variable name="vrFAMILY_DESC"/>

  <xsl:variable name="vrDESC_ORIGIN_ID"/>

  <xsl:variable name="vrDESC_TAG"/>

 <xsl:template match="sL:FamilyCollection">    

     <xsl:for-each select="sL:Family">

        <xsl:variable name="vrFAMILY">

<xsl:value-of select="sL:FAMILY"/>

        </xsl:variable>

<xsl:variable name="vrVHCL_TYPE">

<xsl:value-of select="sL:VHCL_TYPE"/>

        </xsl:variable>

<xsl:variable name="vrFAMILY_DESC">

<xsl:value-of select="sL:FAMILY_DESC"/>

        </xsl:variable>

        <xsl:variable name="vrDESC_ORIGIN_ID">

<xsl:value-of select="sL:DESC_ORIGIN_ID"/>

        </xsl:variable>

        <xsl:variable name="vrDESC_TAG">

<xsl:value-of select="sL:DESC_TAG"/>

        </xsl:variable>

        <xsl:variable name="vrACTIVE_IND">

<xsl:value-of select="sL:ACTIVE_IND"/>

        </xsl:variable>

        <xsl:variable name="vrUPDATE_DATE">

<xsl:value-of select="sL:UPDATE_DATE"/>

        </xsl:variable>

<xsl:value-of select="$vrFAMILY"/>

<xsl:text>|</xsl:text>

<xsl:value-of select="$vrVHCL_TYPE"/>

<xsl:text>|</xsl:text>

<xsl:value-of select="$vrFAMILY_DESC"/>

<xsl:text>|</xsl:text>

<xsl:value-of select="$vrDESC_ORIGIN_ID"/>

<xsl:text>|</xsl:text>

<xsl:value-of select="$vrDESC_TAG"/>

         <xsl:text>###</xsl:text>

</xsl:for-each>

  </xsl:template>

</xsl:stylesheet>

Once the XSLT file is created then keep both the FAMILY.XSLT & FAMILY.XML file on the same location in the directory folder on your local system.

Double click the on the FAMILY.XML file then the TXT file would be automatically  opened in the internet explorer window . You can save this by the name of FAMILY.TXT file.

This process can be automated......................... according to various requirements.

Now  create a control file  FAMILY.CTL file

LOAD DATA

CHARACTERSET UTF8

INFILE '%FileName' "STR '###'"

REPLACE

INTO TABLE FAMILY

FIELDS TERMINATED BY '|' TRAILING NULLCOLS

(

FAMILY CHAR TERMINATED BY '|',

VHCL_TYPE CHAR TERMINATED BY '|',

FAMILY_DESC CHAR TERMINATED BY '|',

DESC_ORIGIN_ID CHAR TERMINATED BY '|',

DESC_TAG CHAR TERMINATED BY '###',

FEEDFile_ID CONSTANT '$$FF'

) 

%FileName : In the above pass /write  the directory path of the FAMILY.TXT file above.

Then run the Sql Loader command to load the data from TXT file to Oracle tables

sqlldr username@server/password control='FAMILY.CTL'  log = 'FAMILY.log'

control = path of the control file.

Through this approach any user reduce it' costs by at least 3 times of another approach.
Execution time of loading XML data into Oracle tables would be reduced by three times of another approach