Select your staging area in the Flow tab of the interface and select the IKM SQL Control Append (ESB XREF). Specify the KM options as follows: XREF_TABLE_NAME – Enter the name of the source table that will be stored in the reference table. XREF_COLUMN_NAME – This is the name of the source primary key that will be stored in the XREF table. XREF_SYS_GUID_EXPRESSION – Expression to be used to computing the Common ID. This expression can be for example: a database sequence (<SEQUENCE_NAME>.NEXTVAL) a function returning a global unique Id (SYS_GUID() for Oracle and NewID() for SQL Server) XREF_ROWNUMBER_EXPRESSION – This is the value that is pushed into the Row Number column of the XREF_DATA table. Use the default value of GUID unless you have the need to change it to a sequence. FLOW_CONTROL - Set to YES in order to be able to use the CKM Oracle.
If the target table doesn’t have any placeholder for the Common ID and you are for example planning to populate the source identifier in one of the target columns, you must use the standard mapping rules of Oracle Data Integrator to indicate which source identifier to populate in which column.
If the target column that you want to load with the Common ID is a unique key of the target table, it needs to be mapped. You must put a dummy mapping on that column. At runtime, this dummy mapping will be overwritten with the generated common identifier by the integration Knowledge Module. Make sure to flag this target column with UD1.
7. 8 .
Select optionally the CKM Oracle in the Control tab of the interface. Click OK to save and close the interface .
Knowledge Module Options Reference LKM SQL to SQL (ESB XREF)
Indicates what action to take on source records after integrating data into the target. Valid values for this option are:
- NONE: No action is taken on source records - UPDATE: Source records flag is updated according to SRC_UPD_COL and SRC_UPD_EXPRESSION - DELETE: Source records are deleted after integration
Expression that concatenates values from the PK to have them fit in a single large varchar column. Example: for the source Orderline Table (aliased OLINE in the interface) you can use expression:
SRC UPDATE DEL NONE|UPDATE
S R C _ P K _ E X P R E S S C o n c a t e n a t i n g
TO_CHAR(OLINE.ORDER_ID) || '-' TO_CHAR(OLINE.LINE_ID)
Knowledge Modules Reference Guide