ODI Interface Mapping Quick Edit All The fields information

Parameter Value Description

 INS

• LKM: Not applicable (*)
• IKM: Only for mapping expressions marked with insertion
  
• CKM: Not applicable
  

UPD

• LKM: Not applicable (*)
• IKM: Only for mapping expressions marked with update
  
• CKM: Not applicable
  

TRG

• LKM: Not applicable (*)
• IKM: Only for mapping expressions executed on the target
  
• CKM: Not applicable
  

NULL

• LKM: Not applicable (*)
• IKM: All mapping expressions loading not nullable columns
  
• CKM: All target columns that do not accept null values
  

PK

• LKM: Not applicable (*)
• IKM: All mapping expressions loading the primary key columns
  
• CKM: All the target columns that are part of the primary key
  

UK

• LKM: Not applicable (*)
• IKM: All the mapping expressions loading the update key column chosen for the current interface
  
• CKM: Not applicable
  

REW

• LKM: Not applicable (*)
• IKM: All the mapping expressions loading the columns with read only flag not selected
  
• CKM: All the target columns with read only flag not selected
  

UD1

• LKM: Not applicable (*)
• IKM: All mapping expressions loading the columns marked UD1
  
• CKM: Not applicable
  

UD2

• LKM: Not applicable (*)
• IKM: All mapping expressions loading the columns marked UD2
  
• CKM: Not applicable
  

UD3

• LKM: Not applicable (*)
• IKM: All mapping expressions loading the columns marked UD3
  
• CKM: Not applicable
  

UD4

• LKM: Not applicable (*)
• IKM: All mapping expressions loading the columns marked UD4
  
• CKM: Not applicable
  

UD5

• LKM: Not applicable (*)
• IKM: All mapping expressions loading the columns marked UD5
  
• CKM: Not applicable
  

MAP

• LKM: Not applicable
• IKM: Not applicable
  
• CKM:
  

Flow control:   All columns of the target table loaded with expressions in the current interface


Static control:    All columns of the target table

SCD_SK	LKM, CKM, IKM: All columns marked SCD Behavior: Surrogate Key in the data model definition.
SCD_NK	LKM, CKM, IKM: All columns marked SCD Behavior: Natural Key in the data model definition.
SCD_UPD	LKM, CKM, IKM: All columns marked SCD Behavior: Overwrite on Change in the data model definition.
SCD_INS	LKM, CKM, IKM: All columns marked SCD Behavior: Add Row on Change in the data model definition.
SCD_FLAG	LKM, CKM, IKM: All columns marked SCD Behavior: Current Record Flag in the data model definition.
SCD_START	LKM, CKM, IKM: All columns marked SCD Behavior: Starting Timestamp in the data model definition.
SCD_END	LKM, CKM, IKM: All columns marked SCD Behavior: Ending Timestamp in the data model definition.
NEW	Actions: the column added to a table, the new version of the modified column of a table.
OLD	Actions: The column dropped from a table, the old version of the modified column of a table.
WS_INS	SKM: The column is flagged as allowing INSERT using Data Services.
WS_UPD	SKM: The column is flagged as allowing UDATE using Data Services.
WS_SEL	SKM: The column is flagged as allowing SELECT using Data Services.

Loading (LKM)

All active mapping expressions that are executed in the current source set, as well as all the columns from the current source set used in the mapping, filters and joins expressions executed in the staging area appear in this list. The list is sorted by POS, FILE_POS.
If there is a journalized datastore in the source of the interface, the three journalizing pseudo columns JRN_FLAG, JRN_DATE, and JRN_SUBSCRIBER are added as columns of the journalized source datastore.

Integration (IKM)

All current active mapping expressionsin the current interface appear in the list.
The list contains one element for each column that is loaded in the target table of the current interface. The list is sorted by POS, FILE_POS, except when the target table is temporary. In this case it is not sorted.
If there is a journalized datastore in the source of the interface, and it is located in the staging area, the three journalizing pseudo columns JRN_FLG, JRN_DATE,and JRN_SUBSCRIBER are added as columns of the journalized source datastore.

Check (CKM)

All the columns of the target table (with static or flow control) appear in this list.
To distinguish columns mapped in the current interface, you must use the MAP selector.

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