Delete Target Table in ODI Substitution parameters

Delete Target Table

This task deletes the data from the target table. This command runs in a transaction and is not committed. It is executed if the DELETE_ALL Knowledge Module option is selected.

Command on Target

delete from <%=odiRef.getTable("L","INT_NAME","A")%>

ODI Oracle Tools Send Mail Example

Oracle Tools Send Mail Example

Procedure Details for Sending Multiple Emails

Source Technology	Oracle
Source Logical Schema	ORACLE
Source Command	Select FirstName FNAME, EMailaddress EMAIL From <%=odiRef.getObjectName("L","USERS","D")%>
Target Technology	ODITools
Target Logical Schema	None
Target Command	OdiSendMail -MAILHOST= tgrtechnologies.com  -FROM=admin@tgrtechnologies.com “-TO=#EMAIL” “-SUBJECT=Job Failure” Dear #FNAME, This is sample program in TGR Technologies, because session <%=snpRef.getSession(“SESS_NO”)%> has just started! -Admin

One Example For Substitution Methods

One Example For Substitution Methods

Procedure Details for Loading Data from a Remote SQL Database

Source Technology	Oracle
Source Logical Schema	SOURCE
Source Command	select ENAME V_ENAME,EMPNO V_EMPNO from   <%=odiRef.getObjectName("L","SCOTT","D")%>
Target Technology	Teradata
Target Logical Schema	TERADATA_DWH
Target Command	insert into PARTS (ENAME,EMPNO) values (:V_ENAME,:V_EMPNO)

OD Substitution methods Parameter pProperty:

Parameter  pProperty:

    ID: Datastore identifier.

    TARG_NAME: Full name of the target datastore. In actions, this parameter returns the name of the current table handled by the DDL command. If partitioning is used on the target datastore of an interface, this property automatically includes the partitioning clause in the datastore name.

    RES_NAME: Physical name of the target datastore. In actions, this parameter returns the name of the current table handled by the DDL command. This property does not include the partitioning information.

    COLL_NAME: Full name of the loading datastore.

    INT_NAME: Full name of the integration datastore.

    ERR_NAME: Full name of the error datastore.

    CHECK_NAME: Name of the error summary datastore.

    CT_NAME: Full name of the checked datastore.

    FK_PK_TABLE_NAME: Full name of the datastore referenced by a foreign key.

    JRN_NAME: Full name of the journalized datastore.

    JRN_VIEW: Full name of the view linked to the journalized datastore.

    JRN_DATA_VIEW: Full name of the data view linked to the journalized datastore.

    JRN_TRIGGER: Full name of the trigger linked to the journalized datastore.

    JRN_ITRIGGER: Full name of the Insert trigger linked to the journalized datastore.

    JRN _UTRIGGER: Full name of the Update trigger linked to the journalized datastore.

    JRN_DTRIGGER: Full name of the Delete trigger linked to the journalized datastore.

    SUBSCRIBER_TABLE: Full name of the datastore containing the subscribers list.

    CDC_SET_TABLE: Full name of the table containing list of CDC sets.

    CDC_TABLE_TABLE: Full name of the table containing the list of tables journalized through CDC sets.

    CDC_SUBS_TABLE: Full name of the table containing the list of subscribers to CDC sets.

    CDC_OBJECTS_TABLE: Full name of the table containing the journalizing parameters and objects.

    <flexfield_code>: Flexfield value for the current target table.

ODI Substitution Parameters L,W,D,P,S usage and meanings

ODI Substitution Parameters L,W,D,P,S usage and meanings

Parameter pMode

L:

use the local object mask to build the complete path of the object.

 R:

Uses the object mask to build the complete path of the object.

use the remote object mask to build the complete path of the object.

Note: When using the remote object mask, getObjectName always resolved the object name using the default physical schema of the remote server.

A:

 Automatic: Defines automatically the adequate mask to use.

 Parameter Location

   W:

Returns the complete name of the object in the physical catalog and the "work" physical schema that corresponds to the specified tuple (context, logical schema)

    D:

Returns the complete name of the object in the physical catalog and the data physical schema that corresponds to the specified tuple (context, logical schema)

A:

Lets Oracle Data Integrator determine the default location of the object. This value is used if pLocation is not specified.

    P:

Qualify object for the partition provided in pPartitionName

    S:

Qualify object for the sub-partition provided in pPartitionName

ODI Substitution Methods list for Journalizing Knowledge Modules

Journalizing Knowledge Modules

In addition to the methods from in the "Global Methods" list, the following methods can be used specifically in Journalizing Knowledge Modules (JKM):

• etJrnFilter() Method
  
• getJrnInfo() Method
  
• getSubscriberList() Method
  
• getTable() Method
  
• getColList() Method
  
  
  getJrnFilter() Method
  
  
  
  
  Examples

  <%=odiRef.getJrnFilter()%>
  

  
  
  getJrnInfo() Method
  
  
  
  

  The table being journalized is <%=odiRef.getJrnInfo("FULL_TABLE_NAME")%>

  
  
  getSubscriberList() Method
  
  
  Here is list of Subscribers: <%=odiRef.getSubscriberList("\nBegin List\n", "- [SUBSCRIBER]", "\n", "\nEnd of List\n")%>
  
  
  
  getTable() Method
  
  
  
  
  
  <%=odiRef.getTable("L", "COLL_NAME", "W")%>   
  tempdb.temp_owner.CZ_0CUSTOMER
  
  
  <%=odiRef.getTable("R", "COLL_NAME", "D")%>
  MyServer:db_odi.dbo.CZ_0CUSTOMER
  
  
  <%=odiRef.getTable("L", "INT_NAME", "W")%>
  tempdb.temp_owner.I$_CUSTOMER
  
  
  <%=odiRef.getTable("R", "ERR_NAME", "D")%>
  MyServer:db_odi.dbo.ERR_CUSTOMER
  
  
  
  
  getColList() Method
  
  
  If the CUSTOMER table contains the columns (CUST_ID, CUST_NAME, AGE) and we want to generate the following code:
  
  
  create table CUSTOMER (CUST_ID numeric(10) null,
  CUST_NAME varchar(50) null, AGE numeric(3) null)
  
  The following code is sufficient:
  
  
  create table CUSTOMER
  <%=odiRef.getColList("(", "[COL_NAME] [SOURCE_CRE_DT] null", ", ", ")", "")%>
  
  
  
  getLoadPlanInstance() Method
  
  
  
  
  The current Load Plan <%=odiRef.getLoadPlanInstance("LOAD_PLAN_NAME")%> started execution at <%=odiRef.getLoadPlanInstance("START_DATE")%>
  
  
  
  
  
  
  

  
  

ODI KM Templates Load Tracking Records query

Load Tracking Records

This task loads data in the tracking table.

Command on Target

insert into <%=odiRef.getTable("L","TARG_NAME","A")%>_RGC(JOBID,JOBNAME,OPERATIONDATE,OPERATIONTYPE,<%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "")%>)select <%=odiRef.getSession("SESS_NO")%> /* JOBID */,<%=odiRef.getSession("SESS_NAME")%> /* JOBNAME */,Current_timestamp /* OPERATIONDATE */,Case when IND_UPDATE = 'I' then 'Insert' else 'Update' end /* OPERATIONTYPE */,<%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "")%>from <%=odiRef.getTable("L","INT_NAME","A")%>where IND_UPDATE <> 'N'

This customization could be extended of course by creating automatically the tracking table using the IKM if it does not exist yet.

ODI KM Templates Create Backup Table query

Create Backup Table

This task creates and populates the backup table.

Command on Target

Create table <%=odiRef.getTable("L","TARG_NAME","A")%>_BCK 

as

select <%=odiRef.getTargetColList("", "[COL_NAME]", ",", "")%>from <%=odiRef.getTable("L","TARG_NAME","A")%>

ODI KM Templates Insert New Rows query

Insert New Rows

This task insert rows from the staging table into the target table. This command runs in the same transaction as all operations made on the target and is not committed. A final Commit transaction command triggers the commit on the target.
Note that this commands selects the data from the different datasets defined for the interface. Using a for loop, it goes through all the datasets, generates for each dataset a SELECT query. These queries are merged using set-based operations (UNION, INTERSECT, etc.) and the resulting data flow is inserted into the target table.

Command on Target

insert into       <%=odiRef.getTable("L","TARG_NAME","A")%> (      <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%>    <%=odiRef.getColList(",", "[COL_NAME]", ",\n\t", "", "((INS and TRG) and REW)")%> ) select    <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%>     <%=odiRef.getColList(",", "[EXPRESSION]", ",\n\t", "", "((INS and TRG) and REW)")%> FROM (        <%for (int i=0; i < odiRef.getDataSetCount(); i++){%><%=odiRef.getDataSet(i, "Operator")%>select        <%=odiRef.getPop("DISTINCT_ROWS")%>      <%=odiRef.getColList(i,"", "[EXPRESSION] [COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%> from  <%=odiRef.getFrom(i)%>where     <% if (odiRef.getDataSet(i, "HAS_JRN").equals("1")) { %> JRN_FLAG <> 'D        '<%} else {%>    (1=1)    <% } %><%=odiRef.getJoin(i)%><%=odiRef.getFilter(i)%><%=odiRef.getJrnFilter(i)%><%=odiRef.getGrpBy(i)%><%=odiRef.getHaving(i)%><%}%>)

ODI KM Templates Delete Target Table query

Delete Target Table

This task deletes the data from the target table. This command runs in a transaction and is not committed. It is executed if the DELETE_ALL Knowledge Module option is selected.

Command on Target

delete from <%=odiRef.getTable("L","INT_NAME","A")%>

ODI KM Templates Drop Work Table query

Drop Work Table

This task drops the loading table. This command is executed if the DELETE_TEMPORARY_OBJECTS knowledge module option is selected. This option will allow to preserve the loading table for debugging.

Command on Target

drop table <%=snpRef.getTable("L", "COLL_NAME", "A")%>

ODI KM Template Load Data query

Load Data

This task reads data on the source connection and loads it into the loading table. This command is always executed.

The loading phase is always using auto commit, as ODI temporary tables do not contain unrecoverable data.

Command on Source

Note the use of the getFilter, getJoin, getFrom, etc. methods. these methods are shortcuts that return contextual expressions. For example, getFilter returns all the filter expressions executed on the source. Note also the use of the EXPRESSION property of getColList, that will return the source columns and the expressions executed on the source. These expressions and source columns are aliases after CX_COL_NAME, which is the name of their corresponding column in the loading table.

This select statement will cause the correct transformation (mappings, joins, filters, etc.) to be executed by the source engine.

select    <%=snpRef.getPop("DISTINCT_ROWS")%>      <%=snpRef.getColList("", "[EXPRESSION]\t[ALIAS_SEP] [CX_COL_NAME]", ",\n\t", "", "")%>from  <%=snpRef.getFrom()%>where      (1=1)<%=snpRef.getFilter()%><%=snpRef.getJrnFilter()%><%=snpRef.getJoin()%><%=snpRef.getGrpBy()%><%=snpRef.getHaving()%>

Command on Target

Note here the use of the biding using :[CX_COL_NAME]. The CX_COL_NAME binded value will match the alias on the source column.

insert into <%=snpRef.getTable("L", "COLL_NAME", "A")%>(   <%=snpRef.getColList("", "[CX_COL_NAME]", ",\n\t", "","")%>)values(   <%=snpRef.getColList("", ":[CX_COL_NAME]", ",\n\t", "","")%>)

ODI KM Templates Create Work Table query

Create Work Table

This task drops the loading table. This command is always executed.
Note the use of the property COLL_NAME of the getTable method that returns the name of the loading table.

Command on Target

create table <%=snpRef.getTable("L", "COLL_NAME", "A")%>(  <%=snpRef.getColList("", "[CX_COL_NAME]\t[DEST_WRI_DT] " + snpRef.getInfo("DEST_DDL_NULL"), ",\n\t", "","")%>)

ODI Drop Work Table LKM template code

Drop Work Table

This task drops the loading table. This command is always executed and has the Ignore Errors flag activated. It will not stop the LKM if the loading table is not found.

Command on Target

drop table <%=snpRef.getTable("L", "COLL_NAME", "A")%>

ODI Insert PK Errors using substitution methods

Insert PK Errors

This task inserts into the error (E$) table the errors detected while checking a primary key. This command always runs, has the Primary Key checkbox active and has Log Counter set to Error to count these records as errors.

Note:

When using a CKM to perform flow control from an interface, you can define the maximum number of errors allowed. This number is compared to the total number of records returned by every command in the CKM of which the Log Counter is set to Error.

Note the use of the getCollist method to insert into the error table the whole record being checked and the use of the getPK and getInfo method to retrieve contextual information.

Command on Target (Oracle)

insert into <%=odiRef.getTable("L","ERR_NAME", "W")%>
(
        ODI_PK,
        ODI_SESS_NO,
        ODI_ROW_ID,
        ODI_ERR_TYPE,
        ODI_ERR_MESS,
        ODI_ORIGIN,
        ODI_CHECK_DATE,
        ODI_CONS_NAME,
        ODI_CONS_TYPE,
        <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "", "MAP")%>
)
select  SYS_GUID(),
        <%=odiRef.getSession("SESS_NO")%>, 
        rowid,
        '<%=odiRef.getInfo("CT_ERR_TYPE")%>', 
        '<%=odiRef.getPK("MESS")%>',
        '<%=odiRef.getInfo("CT_ORIGIN")%>',
        <%=odiRef.getInfo("DEST_DATE_FCT")%>,
        '<%=odiRef.getPK("KEY_NAME")%>',
        'PK',   
        <%=odiRef.getColList("", odiRef.getTargetTable("TABLE_ALIAS")+".[COL_NAME]", ",\n\t", "", "MAP")%>
from     <%=odiRef.getTable("L", "CT_NAME", "A")%> <%=odiRef.getTargetTable("TABLE_ALIAS")%>
where   exists  (
                select  <%=odiRef.getColList("", "SUB.[COL_NAME]", ",\n\t\t\t", "", "PK")%>
                from    <%=odiRef.getTable("L","CT_NAME","A")%> SUB
                where   <%=odiRef.getColList("", "SUB.[COL_NAME]="+odiRef.getTargetTable("TABLE_ALIAS")+".[COL_NAME]", "\n\t\t\tand ", "", "PK")%>
                group by        <%=odiRef.getColList("", "SUB.[COL_NAME]", ",\n\t\t\t", "", "PK")%>            having  count(1) > 1
                )
<%=odiRef.getFilter()%>

ODI Delete Errors from Controlled Table

Delete Errors from Controlled Table

This task removed from the controlled table (static control) or integration table (flow control) the rows detected as erroneous.
This task is always executed and has the Remove Errors option selected.

Command on Target (Oracle)

delete from       <%=odiRef.getTable("L", "CT_NAME", "A")%>  T
where    exists         (
                select   1
                from    <%=odiRef.getTable("L","ERR_NAME", "W")%> E
                where ODI_SESS_NO = <%=odiRef.getSession("SESS_NO")%>
                and T.rowid = E.ODI_ROW_ID
                )

ODI Temporary $E table for Errors creation

Create Error Table

This task creates the error (E$) table. This command always runs and has the Ignore Errors flag activated. It will not stop the CKM if the error table already exist.
Note the use of the getCollist method to add the list of columns from the checked to this table structure.

Command on Target (Oracle)

...
create table <%=odiRef.getTable("L","ERR_NAME", "W")%>
(
        ODI_ROW_ID                UROWID,
        ODI_ERR_TYPE               <%=odiRef.getDataType("DEST_VARCHAR", "1", "")%>
<%=odiRef.getInfo("DEST_DDL_NULL")%>,
        ODI_ERR_MESS              <%=odiRef.getDataType("DEST_VARCHAR", "250", "")%>
<%=odiRef.getInfo("DEST_DDL_NULL")%>,
        ODI_CHECK_DATE     <%=odiRef.getDataType("DEST_DATE", "", "")%>
<%=odiRef.getInfo("DEST_DDL_NULL")%>, 
        <%=odiRef.getColList("", "[COL_NAME]\t[DEST_WRI_DT] " + odiRef.getInfo("DEST_DDL_NULL"), ",\n\t", "", "")%>,
        ODI_ORIGIN               <%=odiRef.getDataType("DEST_VARCHAR", "100", "")%>
<%=odiRef.getInfo("DEST_DDL_NULL")%>,
        ODI_CONS_NAME      <%=odiRef.getDataType("DEST_VARCHAR", "35", "")%>
<%=odiRef.getInfo("DEST_DDL_NULL")%>,
        ODI_CONS_TYPE              <%=odiRef.getDataType("DEST_VARCHAR", "2", "")%>
<%=odiRef.getInfo("DEST_DDL_NULL")%>,
        ODI_PK                    <%=odiRef.getDataType("DEST_VARCHAR", "32", "")%> PRIMARY KEY,  ODI_SESS_NO               <%=odiRef.getDataType("DEST_VARCHAR", "19", "")%>
)

ODI temporary $ tables for Check creation

Create Check Table

This task creates the error summary table. This command always runs and has the Ignore Errors flag activated. It will not stop the CKM if the summary table already exist.

Command on Target (Oracle)

...
create table <%=odiRef.getTable("L","CHECK_NAME","W")%>
(
        CATALOG_NAME      <%=odiRef.getDataType("DEST_VARCHAR", "100", "")%>
<%=odiRef.getInfo("DEST_DDL_NULL")%> ,
        SCHEMA_NAME      <%=odiRef.getDataType("DEST_VARCHAR", "100", "")%>
<%=odiRef.getInfo("DEST_DDL_NULL")%> ,
        RESOURCE_NAME    <%=odiRef.getDataType("DEST_VARCHAR", "100", "")%>
<%=odiRef.getInfo("DEST_DDL_NULL")%>,
        FULL_RES_NAME      <%=odiRef.getDataType("DEST_VARCHAR", "100", "")%>
<%=odiRef.getInfo("DEST_DDL_NULL")%>,
        ERR_TYPE          <%=odiRef.getDataType("DEST_VARCHAR", "1", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%>,
        ERR_MESS          <%=odiRef.getDataType("DEST_VARCHAR", "250", "")%>
<%=odiRef.getInfo("DEST_DDL_NULL")%> ,
        CHECK_DATE       <%=odiRef.getDataType("DEST_DATE", "", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%>,
        ORIGIN           <%=odiRef.getDataType("DEST_VARCHAR", "100", "")%> <%=odiRef.getInfo("DEST_DDL_NULL")%>,
        CONS_NAME        <%=odiRef.getDataType("DEST_VARCHAR", "35", "")%>
<%=odiRef.getInfo("DEST_DDL_NULL")%>,
        CONS_TYPE                 <%=odiRef.getDataType("DEST_VARCHAR", "2", "")%>
<%=odiRef.getInfo("DEST_DDL_NULL")%>,
        ERR_COUNT                 <%=odiRef.getDataType("DEST_NUMERIC", "10", "")%>
<%=odiRef.getInfo("DEST_DDL_NULL")%>
)

ODI Substitution methods using Working with Datasets

Working with Datasets

Oracle Data Integrator supports datasets. Each dataset represents a group of joined and filtered sources tables, with their mappings. Datasets are merged into the target datastore using set-based operators (UNION, INTERSECT, etc) at the integration phase.
During the loading phase, the LKM always works on one dataset. Duringthe integration phase, when all datasets need to merged, certain odiRef APIs that support working on a specific dataset are called using an index that identifies the dataset.
The following example explains how this dataset merging is done.

<%for (int i=0; i < odiRef.getDataSetCount(); i++){%>
<%=odiRef.getDataSet(i, "Operator")%>
select  <%=odiRef.getUserExit("OPTIMIZER_HINT")%>
        <%=odiRef.getPop("DISTINCT_ROWS")%>
        <%=odiRef.getColList(i,"", "[EXPRESSION]", ",\n\t", "", "(((INS or UPD) and !TRG) and REW)")%>,
<% if (odiRef.getDataSet(i, "HAS_JRN").equals("1")) { %>
        JRN_FLAG IND_UPDATE
<%} else {%>
        'I' IND_UPDATE
<%}%>
from     <%=odiRef.getFrom(i)%>
where   (1=1)
<%=odiRef.getJoin(i)%>
<%=odiRef.getFilter(i)%>
<%=odiRef.getJrnFilter(i)%>
<%=odiRef.getGrpBy(i)%>
<%=odiRef.getHaving(i)%>
<%}%>

A Java For loop iterates over the datasets. The number of datasets is retrieved using the getDataSetCount method. For each dataset, a SELECT statement is issued, each statement separated from the previous one by the dataset's set-based operator retrieved using the getDataSet method.

ODI Substitution methods Generating the Source Select Statement

Generating the Source Select Statement

LKMs and IKMs both manipulate a source result set. For the LKM, this 
result set represents the pre-transformed records according to the 
mappings, filters and joins that need to be executed on the source. For 
the IKM, however, the result set represents the transformed records 
matching the mappings, filters and joins executed on the staging area.

select <%=odiRef.getPop("DISTINCT_ROWS")%>
<%=odiRef.getColList("", "[EXPRESSION]\t[ALIAS_SEP] [CX_COL_NAME]", ",\n\t", "", "")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getFilter()%>
<%=odiRef.getJrnFilter()%>
<%=odiRef.getJoin()%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>

To obtain the result set from any SQL RDBMS staging area server to build your final flow data, you would use the following SELECT statement in your IKM. Note that the getColList is filtered to retrieve only expressions that are not executed on the target and that are mapped to writable columns.

select <%=odiRef.getPop("DISTINCT_ROWS")%>
<%=odiRef.getColList("", "[EXPRESSION]", ",\n\t", "", "(not TRG) and REW")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoin()%>
<%=odiRef.getFilter()%>
<%=odiRef.getJrnFilter()%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>

As all filters and joins start with an AND, the WHERE clause of the SELECT statement starts with a condition that is always true (1=1).