How to understand Knowledge Modules in ODI or How to Customise Knowledge Module in ODI?

Using Below all parameters we can understand the Knowledge Modules.

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

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.

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)

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

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

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

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

                )

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.

getObjectName(“L”, “MY_OBJECT”, “D”)

All KMs and procedures

The target datastore getTable(“L”, “TARG_NAME”, “A”) LKM, CKM, IKM, JKM

The “I$” datastore getTable(“L”, “INT_NAME”, “A”) LKM, IKM

The “C$” datastore getTable(“L”, “COLL_NAME”, “A”) LKM

The “E$” datastore getTable(“L”, “ERR_NAME”, “A”) LKM, CKM, IKM

The checked datastore getTable(“L”, “CT_NAME”, “A”) CKM

The datastore referenced  by a foreign key

getTable(“L”, “FK_PK_TABLE_NAME”, “A”) CKM