29 Eylül 2013 Pazar

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:





Hiç yorum yok:

Yorum Gönder