ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

July 30, 2010
by kdevendr
6 Comments

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

Single Post View


SCD TYPE 3

The below post shows the  an easy method to create a SCD TYPE 3 functionality.

Step 1 . Create a copy of the IKM Oracle Incremental Update and rename to IKM Oracle Incremental SCD Type 3

Step 2 . Add the following codes as a command and name it ” Historical Update Rows ” and scroll the level to before  ” Update existing Rows ”

UPDATE    <%=odiRef.getTable("L", "TARG_NAME", "A")%> T
set <%=odiRef.getColList("", "T.[COL_NAME]", ",nt", "", "(UD2)")%> =
<%=odiRef.getColList("", "T.[COL_NAME]", ",nt", "", "(UD1)")%>
where    (<%=odiRef.getColList("","T.[COL_NAME]", ", ", "", "UK")%>)
    in    (
select    <%=odiRef.getColList("","T.[COL_NAME]", ",nttt", "", "UK")%>
from     <%=odiRef.getTable("L", "TARG_NAME", "A")%> T ,
<%=odiRef.getTable("L", "INT_NAME", "A")%> S
where   <%=odiRef.getColList("","T.[COL_NAME]", ", ", "", "UK")%> =
<%=odiRef.getColList("","S.[COL_NAME]", ", ", "", "UK")%> AND
<%=odiRef.getColList("", "T.[COL_NAME]", ",nt", "", "(UD1)")%>!=
<%=odiRef.getColList("", "S.[COL_NAME]", ",nt", "", "(UD1)")%>
        )

image

Step 3.  Go to your interface and assign UD1 to  Column having current Value  say for ex Salary and UD2 to column to be maintained for historical value  say for ex Previous Salary (Uncheck Insert and Update and check only UD2)

Step 4 . Lets take a look at an example for a complete overview.

Here i have two columns Salary and Previous Salary . I have marked the Salary as UD1  and previous_salary as UD2(uncheck Insert and Update and check only UD2)

image

image

image

I am updating the salary to 8210 and 7710 respectively at the source side .

Step 5 . Save and run the interface .

Let me change salary data for two records and lets see if it comes through.

image

It does. Have fun and look for odiexperts for more fun and interesting ideas.

July 30, 2010
by kdevendr
1 Comment

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

Tags: ,

Single Post View


Best practice of ODI

Some of the best practice,we feel in day to day usage of ODI have been presented below.

1. Provide the Developer SNPS_LOGIN_WORK.XML and SNPS_LOGIN_SECURITY.XML, rather than providing the ODI_MASTER password. This way administrator can stop developer to play with repository tables.

2. Change the supervisor password and also make another SUPERUSER (SUPERVISOR PRIVILEGE) , use them in ODIPARAMS and other place instead of SUPERVISOR.

3. The Developers have to deal with KM and so there are higher chances of corruption, in order to be more consistent, make the copy of the default KM and associate project names to , say for example if your project name is XMT so the IKM Sql control Append will be ‘ IKM Sqlcontrol Append_XMT ‘ and also provide the Date and administrator name who created them in KM . Also store them in a separate area , so that no developer can replace it by mistake .If they need to change the code , they can do on a duplicate and once approved, bring in the code into the default one. This way KM consistency is maintained.

4. Don’t import all the KM , only import as required.

5. Have Single Development Work Repository and Execution Work Repository for Testing, UAT and Production system. In case if two Development Work Repository is required make sure the codes are intact. As we have seen as time near UAT and Production , the Testing Development Work Repository acts like Main source of development . In such a case move the codes  to Source Development Box.

6. Add a Sentence or two in Description for each objects ( Eg – Variable , Interface etc ) so that in future if some other developer looks at the Code or some time when we need to debug the codes we have developed few weeks or months ago, it  can have a better understanding of the relationship and usage of the object.

7. Use Memo to track missing action item or important points to other Developers. Say for ex – In Interface the column ‘ emp_desc ‘ needs to be mapped or other such pending or to be updated action items.

8. Use Global variables only when being used by multiple Projects or Multiple Developer.

9. ODI Procedure needs  to be Context Independent .

July 22, 2010
by Cezar Santos
9 Comments

Categories: Administration , Architecture , ODI , Tips and Tricks

Tags: , , , , , ,

Single Post View


11g Oracle Data Integrator – Part 1/11g – First Screen

Hi Guys! 

It’s really a pleasure write this post… because Oracle Data Integrator 11g just arrives!!!!!!! 

Well, I’m not sure if you know but I’m an ODI enthusiastic (Did you notice it?  😀 ) and, because of that, I will tell you my ODI 11g impression in 11 blog posts!  Isn’t original but liked the idea! hehehehehehe! 

Well guys, here it is! The first screen from ODI Studio!!! 

ODI Studio

 

There is a lot of differences… 

The first is that there is no more 4 separated modules, just one GUI (look into the image) and several commands are, now, accessed from a menu bar. 

Well, I now that is few information but is just the first of eleven… The next’s post will bring a lot of new information! 

Talk to you all soon! 

Cezar Santos

June 21, 2010
by kdevendr
11 Comments

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

Tags: , , ,

Single Post View


Automated ODI Export Import Scenarios

The whole main idea of this logic is to capture any created , regenerated and re process any failed scenarios while importing . Its just a simple Process to do the Export and Import of Scenarios from One Work Repository to another Work Repository with out manually doing any of the steps .
The Logic  in short, works like this , a small procedure (ODI_SCEN)  captures the created ,regenerated scenarios from the Source Work Repository and automatically determines whether the scenarios needs to imported or not . If so , using jython and startcmd , respective scenario is exported and later at the Target Work Repository  the scenarios are imported and finally the Exported Scenarios.

Presently this method is deployed to work with single Master and Multiple work repository.

I have developed this idea keeping into mind that after the development is done , the export and import of scenario to next stage is high since lots of code fixes ,updates and issues are resolved at this stage and so scenarios are regenerated and send to the next stage and for an administrator and even developer this is lots of work.

The below screenshot shows the big picture of the process and the are steps are as follows.

image

1. A table is created  called ODI_SCEN , for my example i have created into ODI_TEMP ( my work area) which keeps history of all scenarios processed  and even that got deleted .

2. An ODI procedure (ODI_SCEN) is run which collects all the data required  such what scenarios are being inserted and what  are updated  and what got deleted.  The information is collected from the previous work repository table (SNP_SCEN) tables and then stored into ODI_SCEN.

3. The variable V_REM_SCEN  is used to calculate if there is any scenarios to be run , else end the process.

4. V_GET_SCEN is used to retrieve the scenario name and version .

5. Export Scen is a jython code which gets the scenario name , version and uses  startcmd to export the XML from the previous(development) repository.

6. IMPORT_SCEN  is used to import the scen from the location where the export is stored and import in Insert_Update mode.

7. If the above step is successful the Import Flag in ODI_SCEN is updated with SUCCESS  else with SKIP.

8. V_REM_SCEN is called to check if there are any other scenarios to be processed or not and the loop is processed till all the scenarios are Imported and once done  finally SKIP_TO_FAILURE is called to mark all the skip to Failure so that these scenarios can be processed again in the next run .

9. Finally delete all the exported scenarios.

PROCEDURE ODI_SCEN

The first step is to create an ODI procedure called ODI_SCEN  and create the following options

  • PROCESS
  • SUCCESS
  • SKIP
  • SKIP_TO_FAILURE

of the type ‘ CHECK BOX ‘ and default value as ‘ NO’

image

image

image

image

and now created the following command and in the given order and required option as shown below in detailed.

image

CREATE TABLE

First step is to create a temporary table where we are going to record and analyze which scenarios to be imported and and which to skip . Please change the schema and  table name as required .

image

image

CREATE TABLE ODI_TEMP.ODI_SCEN
 (
    ROW_ID       NUMBER(10)  DEFAULT 1,
    SCEN_NAME    VARCHAR2(50),
    SCEN_VERSION VARCHAR2(10),
    SCEN_TYPE    VARCHAR2(20),
    SCEN_INSERT_DATE DATE,
    PROCESSED_DATE DATE,
    IMPORT_FLAG VARCHAR(50),
    CONSTRAINT ROW_ID_PK PRIMARY KEY (ROW_ID)
  )

The SCEN_NAME stores the scenario name , SCEN_VERSION  stores the scenario_version , SCEN_TYPE the object type such as variable  , procedure , Interface , SCEN_INSERT_DATE  stores the date when the scenarios gets created and PROCESSED_DATE stores the date when the scenario gets inserted into ODI_SCEN table .
UPDATE

Here this query checks to see if already existing scenarios have been regenerated and if so capture them so that we can import them .

image image

UPDATE ODI_TEMP.ODI_SCEN
SET
  (
    PROCESSED_DATE,
    SCEN_INSERT_DATE,
    IMPORT_FLAG
  )
  =
  (SELECT SYSDATE,
    SNP_SCEN.LAST_DATE,''
  FROM ODI_WORK.SNP_SCEN ,
    ODI_TEMP.ODI_SCEN
  WHERE SNP_SCEN.SCEN_NAME       =ODI_SCEN.SCEN_NAME
  AND SNP_SCEN.SCEN_VERSION      =ODI_SCEN.SCEN_VERSION
  AND ODI_SCEN.SCEN_INSERT_DATE != SNP_SCEN.LAST_DATE
   )
   WHERE (SCEN_NAME,SCEN_VERSION,ODI_SCEN.SCEN_INSERT_DATE) NOT IN
  ( SELECT SCEN_NAME,SCEN_VERSION,SNP_SCEN.LAST_DATE FROM ODI_WORK.SNP_SCEN
  )

INSERT

Here this query checks for newly created scenarios and capture them into the table and import them accordingly

image

DECLARE
  V_COUNT      NUMBER(5);
  V_MAX_ROW_ID NUMBER(5);
BEGIN
  SELECT COUNT(*)INTO V_COUNT
  FROM ODI_WORK.SNP_SCEN
  WHERE ( SCEN_NAME, SCEN_VERSION, LAST_DATE) NOT IN
    ( SELECT SCEN_NAME , SCEN_VERSION ,SCEN_INSERT_DATE
	FROM ODI_TEMP.ODI_SCEN
    );
  SELECT CASE WHEN MAX(ROW_ID)>1 THEN MAX(ROW_ID) ELSE 0 END
	INTO V_MAX_ROW_ID FROM ODI_TEMP.ODI_SCEN ;
  WHILE (V_COUNT>0) LOOP
  INSERT INTO ODI_TEMP.ODI_SCEN
  SELECT V_MAX_ROW_ID+ROWNUM,
    SCEN_NAME ,
    SCEN_VERSION ,
    CASE
      WHEN I_POP IS NOT NULL
      THEN 'INTERFACE'
      WHEN I_PACKAGE IS NOT NULL
      THEN 'PACKAGE'
      WHEN I_TRT IS NOT NULL
      THEN 'PROCEDURE'
      WHEN I_VAR IS NOT NULL
      THEN 'VARIABLE'
    END,
    LAST_DATE,
    SYSDATE,
   ''
  FROM ODI_WORK.SNP_SCEN
  WHERE ( SCEN_NAME, SCEN_VERSION, LAST_DATE) NOT IN
    (SELECT SCEN_NAME , SCEN_VERSION ,SCEN_INSERT_DATE
	FROM ODI_TEMP.ODI_SCEN) ;
    V_COUNT := V_COUNT -1 ;
    COMMIT;
END LOOP;
END;

MARK FOR DELETION

some time few scenarios are dropped or renamed to a newer scenario  and this way we capture  the dropped scenarios  and marked with deleted flag. Here there is no process to drop the scenarios already existing in the target repository , which have to carried out manually.

image

UPDATE ODI_TEMP.ODI_SCEN SET IMPORT_FLAG='DELETED'
WHERE
SCEN_INSERT_DATE IS NULL AND
PROCESSED_DATE IS NULL

IGNORE AUTOMATED SCEN

This step is to ignore to capture the main scenario through which all the automated action of export and import is carried out.

image

image

UPDATE ODI_TEMP.ODI_SCEN
SET IMPORT_FLAG='DONT PROCESS'
WHERE SCEN_NAME='AUTOMATE_ODI_SCEN'

MARK FOR SUCCESS

This query is used to mark the Import_Flag to Success if the Import is successful

image

UPDATE ODI_TEMP.ODI_SCEN
SET IMPORT_FLAG='SUCCESS'
WHERE ROW_ID IN
  (SELECT ROW_ID
  FROM ODI_TEMP.ODI_SCEN
  WHERE TRUNC(ODI_SCEN.PROCESSED_DATE)=TRUNC(SYSDATE)
  AND ROWNUM =1
 AND SCEN_INSERT_DATE IS NOT NULL
  AND (IMPORT_FLAG IS NULL
   OR IMPORT_FLAG='FAILURE')
)

MARK_SKIP

This query is used to mark the Import_Flag to SKIP if the Import is failed

image

UPDATE ODI_TEMP.ODI_SCEN
SET IMPORT_FLAG='SKIP'
WHERE ROW_ID IN
  (SELECT ROW_ID
  FROM ODI_TEMP.ODI_SCEN
  WHERE TRUNC(ODI_SCEN.PROCESSED_DATE)=TRUNC(SYSDATE)
  AND ROWNUM =1
  AND SCEN_INSERT_DATE IS NOT NULL
  AND (IMPORT_FLAG IS NULL OR IMPORT_FLAG='FAILURE')
)

SKIP_TO_FAILURE

This query update all the skipped scenarios import flag to failure ,reason being that since apart being null i am also reprocessing failed scenarios , if in the above step i use import flag as failure it will keep on processing the same failed record  and it goes into an infinite loop .

image

UPDATE ODI_TEMP.ODI_SCEN
SET FLAG='FAILURE'
WHERE FLAG='SKIP'

V_REM_SCEN

this query is used to check for the remaining number of records to be processed.

image

SELECT COUNT(*)
FROM ODI_TEMP.ODI_SCEN
WHERE TRUNC(SYSDATE)  =TRUNC(ODI_SCEN.PROCESSED_DATE)
AND SCEN_INSERT_DATE IS NOT NULL
AND (IMPORT_FLAG     IS NULL
OR IMPORT_FLAG        ='FAILURE')

V_GET_SCEN

this query is used to retrieve the scenario name and other detail so we can Export and Import scenarios

image

SELECT 'SCEN_'||SCEN_NAME||SCEN_VERSION||'.XML'
FROM ODI_TEMP.ODI_SCEN
WHERE TRUNC(ODI_SCEN.PROCESSED_DATE)=TRUNC(SYSDATE)
AND ROW_ID                         IN
  (SELECT ROW_ID
  FROM ODI_TEMP.ODI_SCEN
  WHERE TRUNC(ODI_SCEN.PROCESSED_DATE)=TRUNC(SYSDATE)
  AND( IMPORT_FLAG                   IS NULL
  OR IMPORT_FLAG                      ='FAILURE')
  )

STARTCMD

ODI Integrator tools can be called from the command or terminal using startcmd.bat or startcmd.sh , in the below step using the jython code and startcmd,  i am exporting the scenario .The difference between the UNIX and WINDOWS is just calling the right startcmd.sh or startcmd.bat depending on the environment.

Note : Make sure the odiparams file is updated with source Master and Work repository details and startcmd is calling the right odiparams file, else you will encounter error in this step.

If the agent is based on  Unix ,  i would recommend to use the Unix drive for temporary storage of Exported scenarios as you may encounter slow performance while using local agent.

image

Unix

——–

import os
scen='#V_GET_SCEN'
scen_name=scen[5:-7]
scen_ver=scen[-7:-4]
file_name='/home/oracle/odi_scen_dmp/#V_GET_SCEN'
odiscen="./startcmd.sh OdiExportScen -SCEN_NAME="+scen_name+"
-SCEN_VERSION="+scen_ver+" -FILE_NAME="+file_name
if os.system(odiscen) <> 0:
    raise odiscen

Windows

————–

import os
scen='#V_GET_SCEN'
scen_name=scen[5:-7]
scen_ver=scen[-7:-4]
file_name='C:/ODI_SCEN_DMP/#V_GET_SCEN'
odiscen="startcmd.bat OdiExportScen -SCEN_NAME="+scen_name+"
-SCEN_VERSION="+scen_ver+" -FILE_NAME="+file_name
if os.system(odiscen) <> 0:
    raise odiscen

Steps to create the above Process Flow.

———————————————–

Once we have build the Procedure , Variables . Now we are ready to integrate into one smart Solution.

Step 1

Drag in the ODI_SCEN procedure and apply the Following option as shown below and name it as ‘PROCESS_SCEN’ . Here we are going to collect the detail of list of Scenarios to be Processed.
image

Step 2

Drag the Variable V_REM_SCEN in refresh Mode.

Step 3

Evaluate the variable V_REM_SCEN in evaluate mode where > 0 and when true then connect to V_REM_SCEN else End the process.

image

Here we are checking whether we have any new or Failed scenarios to be processed else we exit the Process

Step 4

Drag the Variable V_GET_SCEN in refresh Mode.

Step 5

Drag and Drop the Procedure StartCMD .This ODI Procedure will go the required Work Repository and fetch the required Scenario to be imported and call  the step name as ‘ EXPORT_SCEN

Step 6

Use the OdiImportScen and point to the File Location Specified in StartCMD and use the Insert/Update Mode .

image

Step 7

Drag in the ODI_SCEN procedure and apply the Following option as shown below and name it SUCCESS. If the Import is successful then ODI will update the ODI_SCEN table with SUCCESS Flag.

image

Step 8

Drag in the ODI_SCEN procedure and apply the Following option as shown below and name it SKIP . If the OdiImportScen fails due to some reason it will update the table with the Flag Skip ,so that the particular scenario is skipped for this round of process.

image

Step 9

Drag the Variable V_REM_SCEN in refresh Mode.

Step 10

Evaluate the variable V_REM_SCEN in evaluate mode where = 0 and when true then connect to SKIP_TO_FAILURE else call V_GET_SCEN

Step 11

Drag in the ODI_SCEN procedure and apply the Following option as shown below and name it SKIP_TO_FAILURE. Here i am changing the Flag from SKIP to FAILURE so that the failed scenarios can be processed again in future run .

image

Step 12

Delete the Scenarios exported using OdiFileDelete

image

Step 13

Finally connect the Variables and Procedure in this way.

image

Note: You might require to use DBLINK to read Work repository table across repositories.

Lets test the above process on various scenarios.

First import the ‘ AUTOMATE_ODI_SCEN ‘

image

image

image

image

Scenario – Creating a New Scenario in my source repository.

I  am creating a new scenario called SRC_AGE_GROUP and lets see if this comes through

image

and it works

image

image

Scenario –  Deleting a scenario

For this example i am deleting the above scenario

image

Scenario – Regenerated  EMPLOYEES  and created a new scenario – REGIONS 002

image

Please find the XML required and codes can be modified accordingly .

PACKAGE AUTOMATE_ODI_SCEN

PROCEDURE ODI_SCEN

PROCEDURE STARTCMD

VARIABLE V_REM_SCEN

VARIABLE V_GET_SCEN

June 21, 2010
by kdevendr
27 Comments

Categories: Knowledge Modules , ODI , Tips and Tricks

Tags: , , ,

Single Post View


LKM OdiSqlUnload(File) to Oracle ( SQLLDR )

There are lots of time , we have issues in dealing with huge load  and we break the process in to multiple steps like First unloading using OdiSqlunload or some native Unloader and then using the SQLLDR to load the file . The process can be time consuming and some time we don’t get the exact result.

To solve these part we have integrated the complete OdiSqlUnload with SQLLDR into one LKM with support to load directly into Target table or into the Work table as required. We have tried to make these as smart and easy it can be  like single KM for both unix and windows environment , Sqlldr log in the operator so you dont have to log into the server to check what is the error due to , Filter or join multiple tables yet getting the performance of odisqlunload .

This KM is created to move huge data from source to Target using ( odisqlunload to unload table into File ) and Loading this file using SQLLDR to oracle target , and is based on the integration of OdiSqlUnload and LKM File to Oracle ( SQLLDR ) .

The LKM can be used to load into C$ or directly into the Target Table.

The Options  are as follows

image

CREATE_WORK_TABLES
This option enables you to create appropriate Control File ,so that SQLLDR can load directly into the Target Table else into Work Table ( C$_). By Default  WORK_TABLES  value is  NO (ie. No C$ will be created will be loaded directly into Target table using Sqlldr )
IKM Dummy is required if  WORK_TABLES=NO else select the appropriate IKM.

DIR_PATH
Please  select  the appropriate Directory where the OdiSqlUnload can unload(File)  the data from Source Tables.

DELETE SQLLDR FILES
This options enables you to delete the SQLLDR  and OdiSqlUnload Files .

DATE_FORMAT
Please enter the valid Date format used by SQLLDR to convert the date into appropriate Format.
The default Format is YYYY-MM-DD HH24:MI:SS

TIMESTAMP_FORMAT

Please enter the Valid Timestamp format used by SQLLDR to convert the Timestamp into appropriate Format.
The default Format is YYYY-MM-DD HH24:MI:SS.FF

UNIX_ENVIRONMENT
Please select the  Environment  where the OdiSqlUnload will unload  ( Unix / Windows ) used into CTL file.
This options enables to have for Unix  (ODISQLUNLOAD) ROW_SEP =n and in(CTL file) =str X’0A’
and for Windows  (ODISQLUNLOAD) ROW_SEP =nr and in(CTL file) =str X’0D0A’ .

Also for deleting files according to the environment( rm / del )

The commands of the LKM are as follows –

UNLOADING TABLE

Unloading the source table into file ,delimited by (,) comma using OdiSqlUnload

OdiSqlUnload "-FILE=<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("","[TABLE_NAME]","","")%>.dat"
"-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=<% if (odiRef.getOption("UNIX_ENVIRONMENT").equals("1"))
{ %>n<%} else { %>rn<% } %>"
"-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"
"-FETCH_SIZE=<%=odiRef.getOption("FETCH_SIZE")%>"
"-QUERY=select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "")%>
<%=odiRef.getColList("", "[EXPRESSION]", "||','||", "", "")%> from <%=snpRef.getFrom()%>
where (1=1) <%=snpRef.getJoin()%>
<%=snpRef.getFilter()%> <%=snpRef.getGrpBy()%> <%=snpRef.getHaving()%>"

GENERATE CTL

If the work tables option – yes or No , then accordingly the CTL is created ie. whether the table to be loaded is C$ or Target table.

SnpsOutFile "-File=<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("", "[TABLE_NAME].ctl", "", "")%>"
OPTIONS (
	SKIP=<%=odiRef.getSrcTablesList("", "[FILE_FIRST_ROW]", "", "")%>,
	ERRORS=<%=odiRef.getUserExit("LOA_ERRORS")%>,
	DIRECT=<%=odiRef.getUserExit("LOA_DIRECT")%>
	)
LOAD DATA
INFILE "<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("", "[RES_NAME]", "", "")%>" "str X'
<% if (odiRef.getOption("UNIX_ENVIRONMENT").equals("1")) { %>0A<%} else {%>0D0A<% } %>'"
BADFILE "<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("", "[TABLE_NAME].bad", "", "")%>"
DISCARDFILE "<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("", "[TABLE_NAME].dsc", "", "")%>"
DISCARDMAX <%=odiRef.getUserExit("LOA_DISCARDMAX")%>
<% if (odiRef.getOption("CREATE_WORK_TABLES").equals("1")) { %>
INTO TABLE <%=odiRef.getTable("L", "COLL_NAME", "W")%>
FIELDS TERMINATED BY X'2C'
TRAILING NULLCOLS
(
	<%=odiRef.getColList("", "[CX_COL_NAME]
<? if (u0022[DEST_DT]u0022.equals(u0022DATEu0022))
{?> DATE '"+snpRef.getOption("DATE_FORMAT")+ "' <?}?>
<? if (u0022[DEST_DT]u0022.equals(u0022TIMESTAMPu0022))
{?> TIMESTAMP '"+snpRef.getOption("TIMESTAMP")+ "' <?}?>" , " ,nt", "","")%>

)
<% } else {%>
INTO TABLE <%=odiRef.getTargetTable("SCHEMA")%>.<%=odiRef.getTargetTable("TABLE_NAME")%>
FIELDS TERMINATED BY X'2C'
TRAILING NULLCOLS
(
	<%=odiRef.getColList("", "[COL_NAME]
<? if (u0022[DEST_DT]u0022.equals(u0022DATEu0022))
{?> DATE '"+snpRef.getOption("DATE_FORMAT")+ "' <?}?>
<? if (u0022[DEST_DT]u0022.equals(u0022TIMESTAMPu0022))
{?> TIMESTAMP '"+snpRef.getOption("TIMESTAMP")+ "' <?}?>" , " ,nt", "","")%>

)
<% } %>

CHANGE FILE PERMISSION

Changing the file permission so that SQLLDR and ODI can read the files

import os
<% if (odiRef.getOption("UNIX_ENVIRONMENT").equals("1")) { %>
if os.system(r"chmod 770 <%=odiRef.getOption("DIR_PATH")%>/*.* ") <> 0:
	raise 'File Permission Issues'
<%} else { %>
raise ' File Permission Not required for Windows environment '
<% } %>

CALL SQLLDR

import os
if os.system(r"sqlldr userid=<%=odiRef.getInfo("DEST_USER_NAME")%>/
<%=odiRef.getInfo("DEST_PASS")%>@<%=odiRef.getInfo("DEST_DSERV_NAME")%>
control=<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].ctl","")%>
log=<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].log", "")%> ") <> 0:
	openlog=open('<%=odiRef.getOption("DIR_PATH")%>/
	<%=odiRef.getSrcTablesList("[TABLE_NAME].log", "")%>','r')
	raise 'OS command has signalled errors.Please see the log for details ',
	openlog.readlines()
	openlog.close()

Here if the process fails and the log will be shows into Operator , enabling to see the log without logging into Unix Box.

Although the display is not perfect still it shows great

DELETE FILES

import os
<% if (odiRef.getOption("UNIX_ENVIRONMENT").equals("1")) { %>
if os.system(r"rm -f <%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].dat", "")%>
<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].ctl", "")%>
<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].bad", "")%>
<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].log", "")%> ") <> 0:
	raise ' ODI has failed to delete files Please remove them manually if required '
<%} else { %>
if os.system(r"cmd /c del <%=odiRef.getOption("DIR_PATH")%>
<%=odiRef.getSrcTablesList("[TABLE_NAME].dat", "")%>;
<%=odiRef.getOption("DIR_PATH")%>
<%=odiRef.getSrcTablesList("[TABLE_NAME].ctl", "")%>;
<%=odiRef.getOption("DIR_PATH")%>
<%=odiRef.getSrcTablesList("[TABLE_NAME].bad", "")%>;
<%=odiRef.getOption("DIR_PATH")%>
<%=odiRef.getSrcTablesList("[TABLE_NAME].log", "")%>") <> 0:
          raise 'ODI has failed to delete files Please remove them manually if required '
<% } %>

Lets look at an example ,

image

First run is No Work Tables , Delete Sqlldr files – No  and I am running in the Windows environment  so Unix _environment = No .

image

IKM Dummy have to selected if  Create Work Tables = No ( as we are loading directly into the Target table )

image

image

OdiSqlUnload

image

Control File

image

Files are not deleted

image

Now lets run the KM with the following option

Create Work Tables – Yes ,Delete SQLLDR Files – Yes , IKM SQL Control Append

image

Being the Create Work table option as yes the c$ is create and so the CTL is created with Flow table options and columns . Also the SQLLDR loads into the C$ table  and then finally using the selected IKM ( for my example IKM Sql control Append ) data is loaded from I$ to Target table.

image

Control File

image

Lets see the code in Unix Environment

image

image

With  Unix environment as Yes the row_sep is n and Infile ‘0A’

image

image

Being an Unix environment , there are file permission issue so change the permission for SQLLDR to read the files.

image

Here since the delete SQLLDR Files are yes , rm command is used to remove them

image

Also in case the SQLLDR encounters errors , i have provide the code to display the log files into the Operator, this way you don’t need to login into Unix to view the log file for error. Although the display is not proper but still it can give you enough information on what is the error due to ?

If there is no error the log file wont be displayed.

image

I have included the codes to support Joins and Filter , as you can see  in the OdiSqlUnload codes where condition, have the join and the filter.

image

image

Download the LKM below.

LKM OdiSqlUnload(File) to Oracle (SQLLDR)

Download XML

IKM Dummy

Download KM_IKM_DUMMY.xml

June 20, 2010
by kdevendr
4 Comments

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

Tags: , ,

Single Post View


Jython OS bug in Windows 7

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 – oracledilibscriptingLibjavaos.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 – oracledilibscriptingLibjavaos.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.

June 20, 2010
by kdevendr
3 Comments

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

Tags: ,

Single Post View


Writing SQL counts using Jython

Hi everyone , few post back we have shown how to get the count in Operator but there are many time you may want to get the count of records by email once the data got loaded. To solve this we have written a simple jython procedure to get and count and attach the file as Email and set up the process

Here is a simple Jython Procedure to  do that  .

import string
import java.sql as sql
import java.lang as lang
import re
sourceConnection = odiRef.getJDBCConnection("SRC")
output_write=open('c:/count_write.txt','w')
sqlstring = sourceConnection.createStatement()
print >> output_write, " The counts are .... 'n'"
#---------------------------------------------------------------
# QUERY 1 COUNT
#---------------------------------------------------------------

sqlstmt="select count(*) as rowcount from all_tables where owner='ODI_TEMP'"
result=sqlstring.executeQuery(sqlstmt)
while result.next():
count=result.getInt("rowcount")
rs='n'+str(sqlstmt)+'nn'+str(count)

print >> output_write ,rs

#---------------------------------------------------------------
# QUERY 2 COUNT
#---------------------------------------------------------------

sqlstmt="select count(*) as rowcount from all_tables where owner='ODI_WORK'"
result=sqlstring.executeQuery(sqlstmt)
while result.next():
count=result.getInt("rowcount")
rs='n'+str(sqlstmt)+'nn'+str(count)

print >> output_write ,rs

#---------------------------------------------------------------
# QUERY 3 COUNT
#---------------------------------------------------------------

sqlstmt="select count(*) as rowcount from all_tables where owner='SYSTEM'"
result=sqlstring.executeQuery(sqlstmt)
while result.next():
count=result.getInt("rowcount")
rs='n'+str(sqlstmt)+'nn'+str(count)

print >> output_write ,rs

sourceConnection.close()
output_write.close()

and you get the output in similar form.

image

Just replace the  sql count query according to your requirement.

and provide the  schema in the ‘Command on Source ‘ which have the select permission on the required table for which the counts are being retrieved.

image

Download the Codes here

Download XML

April 12, 2010
by Cezar Santos
18 Comments

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

Tags: , , , , , , , , ,

Single Post View


Getting one or several unknown files from a directory

Hi Friends…

Today I would like to share with you a way that I have been using for several years, to get unknown files from a directory in Oracle Data Integrator.

For instance, if you have files in a directory that had no controlled names but needs to be load by ODI, here there are an option:

The hole idea is based in create a file with contains all file names and read it from ODI.

(Windows example but pretty much the same at Unix/Linux)

  1. Create an ODI Procedure with a single step.
    • Source Tab
    • Technology: OS Command
    • Code:  cmd /c dir c:my_directory*.* /b /a:-d > c:my_directoryAll_Files_Names.txt
  2. Now use the technique describe at: http://odiexperts.com/?p=273 to read each line at time  using the All_Files_Names.txt as source.

So simple like that… all you need to do is, before go to the next line (loop) use the variable in the way that you want!

An example is use it as name for a datastore that is used as source in an interface, than you can call the interface once to each variable value allowing to load all files from that directory.

I hope this technique could help you guys…

Best Regards,

Cezar Santos

April 9, 2010
by Cezar Santos
32 Comments

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

Tags: , , , , , , , , , ,

Single Post View


Interface Parallel Execution – A new solution

Hi Friends,

Two days ago Devendra talk to me about parallel executions in ODI and we are discussing the solution from Oracle Metalink that works but, at true, I never was stopped to analyse.

In face of that, I decided try something in a  new way and it worked fine!

Let me share my 2 minutes approach.

Problem Description:

Oracle Data Integrator uses the target table name to build the work tables (I$, E$, C$) and, because of that if more then one interface that loads the same target table is executed at same time the work tables creation, insert and drop process got mixed and nothing works.

Solution:

The solution is pretty obvious… It is necessary to make the work table name variable and linked to each execution. The ODI path for that is the Session Number.

My approach:

All solutions that I saw until today uses an Oracle Data Integrator variable but got problems if the table name is near to the maximum name length allowed for the technology.

In face of that, I decide to use the ODI natural behavior to manage the lenght.

All that is necessary to do is:

1. Edit all your KM (IKM and LKM if some) and add a new step (move it to be the first step)

Step Name: Parallel Setup

Technology:  Java Bean Shell

Code:  <? String vSess = odiRef.getSession(“SESS_NO”); ?>

Importante note: this change can be done to any KM that will not interfier with process not parallel

2. Go to Topology Module, in the Physical Schema that will have parallel execution (or in all if you wish) and change the Work Table Prefix to:

E$_<?=vSess?>

C$_<?=vSess?>

I$_<?=vSess?>

Well my friends… that is all! Any Parallel or Single execution can be executed with no conflicts between work table name or length name  problem will be raise.

The best about it is be very simple!!!

See you all in next post!

Regards,

Cezar Santos

April 4, 2010
by kdevendr
5 Comments

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

Tags: , ,

Single Post View


Displaying SQL results in Operator

Few posts back i have shown ,how to display the value of the variable using Jython and today i am showing how to display  SQL  Query result  in the operator.

— Displays the Sql results of query into Operator –

import string
import java.sql as sql
import java.lang as lang
sourceConnection = odiRef.getJDBCConnection("SRC")
sqlstring = sourceConnection.createStatement()
result=sqlstring.executeQuery("select table_name,owner from all_tables where owner='ODI_TEMP'")
i=0
rs=[]
while (result.next()):
    rs.append(str(i)+'t'+str(result.getString("table_name"))+'tt'+str(result.getString("owner"))+' , ')
    i=i+1

res='n'.join(map(string.strip,rs))
raise res
sourceConnection.close()

In  sourceConnection = odiRef.getJDBCConnection(“SRC”), the connection details are retrieved from ODI , to do so in the ‘ command on  Source tab’  – Select the Technology and required Schema so that ODI can retrieve the connection details.

image

result=sqlstring.executeQuery(“select table_name,owner from all_tables where owner=’ODI_TEMP'”)

# Enter the query between the two double quotes or replace the above query .

rs.append(str(i)+’t’+str(result.getString(“table_name”))+’tt’+str(result.getString(“owner”))+’ , ‘)

# For every column you mention in the above query – get them under result.getString(“Column_Name”)

# as for my example i am having two column so iam calling getString twice , similarly do so for every column

# called in the Select statement

In the Target technology select  ‘  Jython ‘

image

Enable  the ‘ Ignore Errors’ option.

Sample Output

image

– Codes to display the Row count –

import string
import java.sql as sql
import java.lang as lang
sourceConnection = odiRef.getJDBCConnection("SRC")
sqlstring = sourceConnection.createStatement()
sqlstmt="select count(*) as rowcount from all_tables where owner='ODI_TEMP'"
result=sqlstring.executeQuery(sqlstmt)
while result.next():
    count=result.getInt("rowcount")

rs='n'+str(sqlstmt)+'nn'+str(count)
raise rs
sourceConnection.close()

Sample Output

image