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).

ODI Actions Using getSrcTableList

Using getSrcTableList

The following example concatenates the list of the source tables of an interface for logging purposes.
For following KM code:

insert into MYLOGTABLE
(
INTERFACE_NAME,
DATE_LOADED,
SOURCE_TABLES
)
values
(
'<%=odiRef.getPop("POP_NAME")%>',
current_date,
'' <%=odiRef.getSrcTablesList("|| ", "'[RES_NAME]'", " || ',' || ", "")%>
)

Generates the following statement:

insert into MYLOGTABLE
(
INTERFACE_NAME,
DATE_LOADED,
SOURCE_TABLES
)
values
(
'Int. CUSTOMER',
current_date,
'' || 'SRC_CUST' || ',' || 'AGE_RANGE_FILE' || ',' || 'C$0_CUSTOMER'
)

ODI Using getColList in an Insert values statement

Using getColList in an Insert values statement

The following example shows how to use column listing to insert values into a table.
For following KM code:

insert into MYTABLE
(
<%=odiRef.getColList("", "[COL_NAME]", ", ", "\n", "INS AND NOT TARG")%>
<%=odiRef.getColList(",", "[COL_NAME]", ", ", "", "INS AND TARG")%>
)
Values
(
<%=odiRef.getColList("", ":[COL_NAME]", ", ", "\n", "INS AND NOT TARG")%>
<%=odiRef.getColList(", ", "[EXPRESSION]", ", ", "", "INS AND TARG")%>
)

Generates the following statement:

insert into MYTABLE
(
CUST_ID, CUST_NAME, ADDRESS, CITY, COUNTRY_ID
, ZIP_CODE, LAST_UPDATE
)
Values
(
:CUST_ID, :CUST_NAME, :ADDRESS, :CITY, :COUNTRY_ID
, 'ZZ2345', current_timestamp
)

ODI Substitution Methods sample examples getTargetColList to create a table

Using getTargetColList to create a table

The following example shows how to use a column list to create a table.
The following KM code:

Create table MYTABLE
<%=odiRef.getTargetColList("(\n", "\t[COL_NAME] [DEST_WRI_DT]", ",\n", "\n)")%>

Generates the following statement:

Create table MYTABLE
(
        CUST_ID numeric(10),
   CUST_NAME varchar(250),
   ADDRESS varchar(250),
   CITY varchar(50),
   ZIP_CODE varchar(12),
   COUNTRY_ID varchar(3)
)

In this example:

• Start is set to "(\n": The generated code will start with a parenthesis followed by a carriage return (\n).
  
• Pattern is set to "\t[COL_NAME] [DEST_WRI_DT]": The generated code will loop over every target column and generate a tab character (\t) followed by the column name ([COL_NAME]), a white space and the destination writable data type ([DEST_WRI_DT]).
  
• The Separator is set to ",\n": Each generated pattern will be separated from the next one with a comma (,) and a carriage return (\n)
  
• End is set to "\n)": The generated code will end with a carriage return (\n) followed by a parenthesis.
  

ODI Action Call Methods for Actions

The following Action Call Methods are available for Actions:

• addAKs(): Call the Add Alternate Key action for all alternate keys of the current table.
  
• dropAKs(): Call the Drop Alternate Key action for all alternate keys of the current table.
  
• addPK(): Call the Add Primary Key for the primary key of the current table.
  
• dropPK(): Call the Drop Primary Key for the primary key of the current table.
  
• createTable(): Call the Create Table action for the current table.
  
• dropTable(): Call the Drop Table action for the current table.
  
• addFKs(): Call the Add Foreign Key action for all the foreign keys of the current table.
  
• dropFKs(): Call the Drop Foreign Key action for all the foreign keys of the current table.
  
• enableFKs(): Call the Enable Foreign Key action for all the foreign keys of the current table.
  
• disableFKs(): Call the Disable Foreign Key action for all the foreign keys of the current table.
  
• addReferringFKs(): Call the Add Foreign Key action for all the foreign keys pointing to the current table.
  
• dropReferringFKs(): Call the Drop Foreign Key action for all the foreign keys pointing to the current table.
  
• enableReferringFKs(): Call the Enable Foreign Key action for all the foreign keys pointing to the current table.
  
• disableReferringFKs(): Call the Disable Foreign Key action for all the foreign keys pointing to the current table.
  
• addChecks(): Call the Add Check Constraint action for all check constraints of the current table.
  
• dropChecks(): Call the Drop Check Constraint action for all check constraints of the current table.
  
• addIndexes(): Call the Add Index action for all the indexes of the current table.
  
• dropIndexes(): Call the Drop Index action for all the indexes of the current table.
  
• modifyTableComment(): Call the Modify Table Comment for the current table.
  
• AddColumnsComment(): Call the Modify Column Comment for all the columns of the current table.