ODI 10g Scheduler Agent on Unix

Hello



Well first of all you should go to your odiparam.sh file which inside of your folder.In this file you have to change the following path




ODI_SECU_DRIVER=driver name that suits with ur database that used as Work Rep.
ODI_SECU_URL=url of your work rep. database
ODI_SECU_USER=database user...
ODI_SECU_ENCODED_PASS=this is the encoded password of ur database user....
ODI_SECU_WORK_REP=name of your work rep.
ODI_USER=SUPERVISOR
ODI_ENCODED_PASS=LELKIELGLJMDLKMGHEHJDBGBGFDGGH
ODI_JAVA_HOME= u should write the exact path of any JDK that is already inside of your machine.... 5.1 or newer versions.



Here it is an example ::




ODI_SECU_DRIVER=oracle.jdbc.driver.OracleDriverODI_SECU_URL=jdbc:oracle:thin:@20.2.99.147:1521:DWXX
ODI_SECU_USER=mYusER
ODI_SECU_ENCODED_PASS=encoded_passODI_SECU_WORK_REP=myREP
ODI_USER=SUPERVISOR
ODI_ENCODED_PASS=LELKIELGLJMDLKMGHEHJDBGBGFDGGH
ODI_JAVA_HOME=/product/10g/db/jdk



your ODI_SECU_ENCODED_PASS should be look like ODI_ENCODED_PASS... which is the encoded password of ODI for SUPERVISOR

Oracle - Clean Up duplicate keys

Case:
Assume that you have  a table with duplicate ids , now you want to fix duplicate ids and want to make it all id values unique

Solution:

Step1: First  collect a dataset for duplicate IDs

CREATE TABLE  TMP AS

SELECT
ROWID  DEST_ROWID ,
ID PREV_ID ,
( SELECT  MAX( ID ) FROM TABLE_TEST  S  ) + ROWNUM  NEW_ID
FROM TABLE_TEST
WHERE
ID IN
(
     SELECT DISTINCT ID
     FROM TABLE_TEST
     GROUP BY ID
     HAVING COUNT(1)>1
)

Step2: Now we have a table that contains DUPLICATE_IDs and ROWID value of our table ( which is useful for updates )


Step3: Assing PK to  DEST_ROWID column on TMP table ( need for update )



Final Step:



UPDATE
(
SELECT  A.ID TARGET_ID , B.NEW_ID UPD_ID
FROM
TABLE_TEST  A , TMP B
WHERE
A.ROWID= B.DEST_ROWID
)
SET TARGET_ID = UPD_ID;
COMMIT;

INFORMATICA How to DENSE_RANK

EXAMPLE:

We want to make RANK object which is  group by COL1,COL2  and ORDER BY COL4 ASC

STEP1:

CREATE TABLE RANKER_TMP   -- SOURCE_TABLE

(

COL1  NUMBER,

COL2  VARCHAR2(100 CHAR) ,

COL3  VARCHAR2( 100 CHAR),

COL4 NUMBER

)

CREATE TABLE RANKER_TMP2   --- TARGET_TABLE

(

RANKER NUMBER,

COL1  NUMBER,

COL2  VARCHAR2(100 CHAR) ,

COL3  VARCHAR2( 100 CHAR),

COL4 NUMBER

)

STEP2:

Insert into RANKER_TMP

   (COL1, COL2, COL3, COL4)

Values

   (1, 'V', 'yes', 3);

Insert into RANKER_TMP

   (COL1, COL2, COL3, COL4)

Values

   (1, 'V', 'no', 3);

Insert into RANKER_TMP

   (COL1, COL2, COL3, COL4)

Values

   (1, 'V', 'YES', 2);

Insert into RANKER_TMP

   (COL1, COL2, COL3, COL4)

Values

   (2, 'b', 'yes', 1);

Insert into RANKER_TMP

   (COL1, COL2, COL3, COL4)

Values

   (2, 'b', 'no', 6);

Insert into RANKER_TMP

   (COL1, COL2, COL3, COL4)

Values

   (2, 'a', 'no', 2);

COMMIT;

STEP3:

In order to make DENSE_RANK we gonna use SORT  and EXPRESSION objects together.Mapping should be look like this

*SORTER  has to be SORTER BY COL1 ,COL2 , COL4  ASC ORDER

*In Informatica , EXPRESSION object  calculates values  from TOP to BOTTOM  

( it first calculates INPUTs then VARIABLEs and last  OUTPUTs. As a result , we can catch previous values by  defining VARIABLE which map to COLUMN values )

EXPRESSION PORTS should be look like this :

* VAL_DIFF used for calculating RANK values. Formula should be look like this:

*Result :

INFORMATICA Function get previous month first_day and last_day

Previous_month_first_day:
TO_CHAR
(
SET_DATE_PART
(
ADD_TO_DATE(sysdate,'MM',-1),
'DD',
1
)
, 'DD.MM.YYYY'
)


Previous_month_last_day:
TO_CHAR(

LAST_DAY( ADD_TO_DATE(sysdate,'MM',-1)   )

,

'DD.MM.YYYY'
)

ODI Knowledge Module For UPDATE WITHOUT EXISTS OR IN CLAUSE

Hi

In this case i will show how to get rid of exists check over update sql statement and i will explain a knowledge module that impelements following kind of sql statement.

Enviroment : Oracle DB  & ODI 11g

Case Study:
I have an employee_list table which contains basic information about employees and now i want to make an update over job title which also triggers update over his or her salary and region information.

EMPLOYEE_LIST:

CREATE TABLE REPORTING.EMPLOYEE_LIST

(

EMP_ID NUMBER,

NAME VARCHAR2(100 CHAR),

SALARY  NUMBER,

TITLE_ID NUMBER,

LOCATION_ID NUMBER

)

TABLES USED FOR UPDATE:

CREATE TABLE REPORTING.EMPLOYEE_UPDATE

(

EMP_ID NUMBER,

SALARY  NUMBER, 

TITLE_ID  NUMBER,

LOCATION_ID  NUMBER

)

CREATE TABLE REPORTING.EMPLOYEE_TITLE

(

TITLE_ID NUMBER,

TITLE  VARCHAR2(100 CHAR)

)

CREATE TABLE REPORTING.EMPLOYEE_LOCATION

(

LOCATION_ID NUMBER,

REGION  VARCHAR2(100 CHAR),

CITY    VARCHAR2(100 CHAR)

)

Normally sql statement should be like this:

UPDATE  REPORTING.EMPLOYEE_LIST 

SET( SALARY  ,  TITLE_ID  ,   LOCATION_ID  )= 

(

select EMPLOYEE_LIST.SALARY +  EMPLOYEE_UPDATE.SALARY  AS SALARY_1 ,

       EMPLOYEE_UPDATE.TITLE_ID  AS TITLE_ID_1 , EMPLOYEE_UPDATE.LOCATION_ID  AS LOCATION_ID_1  

       from REPORTING.EMPLOYEE_LIST   EMPLOYEE_LIST, REPORTING.EMPLOYEE_LOCATION   EMPLOYEE_LOCATION, 

       REPORTING.EMPLOYEE_TITLE   EMPLOYEE_TITLE, REPORTING.EMPLOYEE_UPDATE   EMPLOYEE_UPDATE   

       where 1=1  And (EMPLOYEE_LIST.EMP_ID=EMPLOYEE_UPDATE.EMP_ID) 

       AND (EMPLOYEE_LIST.TITLE_ID=EMPLOYEE_TITLE.TITLE_ID) AND (EMPLOYEE_LIST.LOCATION_ID=EMPLOYEE_LOCATION.LOCATION_ID) 

)

WHERE  EMPLOYEE_LIST.EMP_ID IN

(

      select  EMPLOYEE_UPDATE.EMP_ID

       from REPORTING.EMPLOYEE_LIST   EMPLOYEE_LIST, REPORTING.EMPLOYEE_LOCATION   EMPLOYEE_LOCATION, 

       REPORTING.EMPLOYEE_TITLE   EMPLOYEE_TITLE, REPORTING.EMPLOYEE_UPDATE   EMPLOYEE_UPDATE   

       where 1=1  And (EMPLOYEE_LIST.EMP_ID=EMPLOYEE_UPDATE.EMP_ID) 

       AND (EMPLOYEE_LIST.TITLE_ID=EMPLOYEE_TITLE.TITLE_ID) AND (EMPLOYEE_LIST.LOCATION_ID=EMPLOYEE_LOCATION.LOCATION_ID)   

)

This statement can work but  we have to read each table twice so it is not costly efficient approach.

Instead i did like this:

 UPDATE 

 ( 

       select EMPLOYEE_LIST.SALARY  ,EMPLOYEE_LIST.TITLE_ID  ,EMPLOYEE_LIST.LOCATION_ID  , EMPLOYEE_LIST.SALARY +  EMPLOYEE_UPDATE.SALARY  AS SALARY_1 ,

       EMPLOYEE_UPDATE.TITLE_ID  AS TITLE_ID_1 , EMPLOYEE_UPDATE.LOCATION_ID  AS LOCATION_ID_1  

       from REPORTING.EMPLOYEE_LIST   EMPLOYEE_LIST, REPORTING.EMPLOYEE_LOCATION   EMPLOYEE_LOCATION, 

       REPORTING.EMPLOYEE_TITLE   EMPLOYEE_TITLE, REPORTING.EMPLOYEE_UPDATE   EMPLOYEE_UPDATE   

       where 1=1  And (EMPLOYEE_LIST.EMP_ID=EMPLOYEE_UPDATE.EMP_ID) 

       AND (EMPLOYEE_LIST.TITLE_ID=EMPLOYEE_TITLE.TITLE_ID) AND (EMPLOYEE_LIST.LOCATION_ID=EMPLOYEE_LOCATION.LOCATION_ID)     

 )   TRG     

SET  TRG.SALARY = SALARY_1, TRG.TITLE_ID = TITLE_ID_1,

TRG.LOCATION_ID = LOCATION_ID_1

------------------------------------------------------------------------------------------------

Like this no need to EXISTS or IN clause 

Note: In this approach our lookup tables has to have PRIMARY_KEYS which are

TITLE_ID  FOR  EMPLOYEE_TITLE

LOCATION_ID  FOR EMPLOYEE_LOCATION

EMP_ID  FOR    EMPLOYEE_UPDATE

 

Otherwise we will face  non-preserved key  error.

-------------------------------------------------------------------------------

Now I am gonna Implement this update statement over ODI , at first i am creating an interface like a normal select - Insert  script.( i am createing inner select statement which i specified above ) 

( Target table is used in both source and target part of the mapping)

In target mapping for the Columns , which do not be used in statement , update and insert flags has to be set to uncheck. In my example  EMP_ID and NAME should be look like this:

For the ones ( SALARY , TITLE_ID , LOCATION_ID)  which will be used in update statement look like this:  ( update flag is checked )

and i choose the KM i wrote 

For detail you can download the KM from the link below.

http://www.speedyshare.com/8fUEJ/KM-IKM-Oracle-Update-Special-Case.xml

After Execution  view of EMP_LIST table as follows:

KNOWLEDGE MODULE EXECUTE IMMEDIATE AND LOG TABLE

In this entry i will show you how to create your own log table mechanism inside of ODI. Also i will use EXECUTE IMMEDIATE ( package like approach) in this knowledge module. As an example i am gonna using IKM Knowlege Module for Oracle DB

Environment : ODI 11g , Oracle 10g database


Step1: I create a log table in Oracle Db

CREATE TABLE  DUMMY
(
INTERFACE_ID   NUMBER, -- interface_id  ( interface based uniqueID given by ODI)
SESSION_NO   NUMBER,    --  ( session_based uniqueID given by ODI can be used as PK)
INTERFACE_NAME  VARCHAR2( 500 CHAR),  -- name of the interface
QUERY   LONG,   -- query that suppose to be run inside of ODI knowledge Module
AFFECTED_NOF_ROWS NUMBER, -- affected nof rows after query execution
RUN_START_DATE  date,  -- execution start_date
RUN_END_DATE  date,  -- execution end_date
STATUS  NUMBER  -- 0 for fails   1 for success
)

Step2: I created a dummy interface and choose the Knowledge Module I wrote
(IKM SQL Control Append LogTable) which is a version of IKM SQL Control Append.

IKM SQL Control Append LogTable : This knowlege module basically has an Oracle package body kind of script.Only different thing that i did is to combine oracle coding with odi methods such as getsession , getInterfaceID etc..

For detail about script you can download KM from the link below:
http://speedy.sh/wT5Dm/KM-IKM-SQL-Control-Append-LogTable.xml

Step3: After execution completed by sample log table as follows:

Informatica Load Into table with Index Error : WRT-8229 ORA-26002: Table string has index defined upon it

Solution:
Change TARGET_LOAD_TYPE from BULK to Normal  ( Workflow-->Session-->Mapping  )
it will work fine , or you can drop indexes and run with bulk mode too.

Oracle SUBSTRING USING SPECIFIC CHARACTER

Just combine two function as follows.

Example:

SELECT SUBSTR('ABCD_EFG', 0, INSTR(TABLE_NAME, '_')-1) AS output
FROM dual

Result:
ABCD

Oracle - Date Conversion Sample

For those who always needs :

 SELECT TO_CHAR(TO_DATE('19981215','YYYYMMDD'),'YYYY' )  FROM DUAL  --Result  1998
   
 SELECT TO_CHAR(TO_DATE('19981215','YYYYMMDD'),'MM' )  FROM DUAL    --Result  12
   
 SELECT TO_CHAR(TO_DATE('19981215','YYYYMMDD'),'DD' )  FROM DUAL     --Result  15

Oracle - Trim Leading Zeros

*Assuming the data you are working on is CHARACTER :

TO_CHAR( TO_NUMBER( COLUMN_A ) )

ODI Knowledge Module for UNION , UNION_ALL, INTERSECT

Hi all

In this entry , i will explain how to make UNION , UNION_ALL, INTERSECT style operations without making extra insertion or temp table creation.


 *Case : What i want to do is to gather all employee information which currently holds in different tables.
 ( Tested enironment : Oracle 10g database , ODI 11g )

 At first we will create a table which holds the SQLs for final statement.

CREATE TABLE REPORTING.INTERFACE_QUERY
 (

INTERFACE_NAME VARCHAR2(2000 BYTE),
EXE_SCRIPT LONG

 )


 * This table contains two columns ,one interface name which holds the name of the interface and  EXE_SCRIPT which holds the SQL created by ODI interface.

*What we gonna do is  to extract source tables called EMPLOYEE_PRE , EMPLOYEE_VICE_PRE, EMPLOYEE_PRG  and fill them into a table called EMPLOYEE_ALL


*Now creating  interfaces which are their target tables are dummy ( yellow interfaces )

* In this interface we will add EMPLOYEES who are currently presidents and with the help of  IKM knowledge module called  IKM SQL Control Append-SubQuery

Link for Download
http://rapidshare.com/files/815556971/KM_IKM_SQL_Control_Append_SubQuery.xml

SQL statement which is created by the interface will be inserted into REPORTING.INTERFACE_QUERY table.Same operation will be done for vice president and programmer tables .

As a result we have following data in our reference table ( Interface_Query ) and we have 3 temp interface.

*Now what we have to do create a main interface which gathers all sql statements above and  load data into our target table.

*I will prepare the allignment of the columns for my target table which has to be matched with my subquery columns.

Before:

After:

Then i created a new interface called Interface_main. Mapping looked like this

* I drag 3 temp interfaces as a source and in order to maintain integrity  connecting them with dummy join ( department_name )  and adding my target table with mappings ( those mapping added as dummy too )

* now i am assigning the knowledge module  called  IKM SQL Control Append-Union-Minus-Intersect-Union All

Link for Download
http://rapidshare.com/files/1050531261/KM_IKM_SQL_Control_Append_Union_Intersect_Union_All.xml

*Parameters for this Knowledge Module look like this

*Basically  knowledge module  gathers SQL statements from REPORTING.INTERFACE_QUERY  and depends on the parameter we choose, it makes UNION , UNION ALL or INTERSECT and creates a resultset which load into our target table. As a result following sql statement has been created and executed.

*