Overview of the XREF KM Process
The overall process can be divided into the following three main phases:
Loading Phase (LKM)
During the loading phase, a Source Primary Key is created using columns from the source table. This Source Primary Key is computed using a user-defined SQL expression that should return a VARCHAR value. This expression is specified in the SRC_PK_EXPRESSION KM option.
For example, for a source Order Line Table (aliased OLINE in the interface) you can use the following expression: TO_CHAR(OLINE.ORDER_ID) || '-' || TO_CHAR(OLINE.LINE_ID)
This value will be finally used to populate the cross-reference table.
Integration and Cross-Referencing Phase (IKM)
During the integration phase, a Common ID is created for the target table. The value for the Common ID is computed from the expression in the XREF_SYS_GUID KM option. This expression can be for example:
A database sequence (<SEQUENCE_NAME>. NEXTVAL) A function returning a global unique Id (SYS_GUID() for Oracle, NewID() for SQL Server)
This Common ID is pushed to the target columns of the target table that are marked with the UD1 flag.
Both the Common ID and the Source Primary Key are pushed to the cross-reference table (XREF_DATA). In addition, the IKM pushes to the cross-reference table a unique Row Number value that creates the cross- reference between the Source Primary Key and Common ID. This Row Number value is computed from the XREF_ROWNUMBER_EXPRESSION KM option, which takes typically expressions similar to the Common ID to generate a unique identifier.
The same Common ID is reused (and not re-computed) if the same source row is used to load several target tables across several interfaces with the Cross-References KMs. This allows the creation of cross-references between a unique source row and different targets rows.
Updating/Deleting Processed Records (LKM)
This optional phase (parameterized by the SRC_UPDATE_DELETE_ACTION KM option) deletes or updates source records based on the successfully processed source records:
If SRC_UPDATE_DELETE_ACTION takes the DELETE value, the source records processed by the interface are deleted. If SRC_UPDATE_DELETE_ACTION takes the UPDATE value, the source column of the source records processed by the interface is updated with the SQL expression given in the SRC_UPD_EXPRESSION KM option. The name of this source column must be specified in the SRC_UPD_COL KM option.
Installation and Configuration
There is no specific Oracle Data Integrator configuration for using the ESB Cross-Reference KMs.
Working with XREF using the Oracle Data Integrator ESB Cross-References KMs
Knowledge Modules Reference Guide