To generate artificial keys in Data Integrator, you can use either Key_Generation Transform or Key_Generation Function. It fetches max. existing key value from the table and uses it as a starting value. Based on this start key, transform/function increments the key value for each row.
There are three options for key generation-
- Table Name : You should provide table name along with the Datastore and Owner (DATASTORE.OWNER.TABLE)
- Generated Key Column : The new artificial keys are inserted into this column. Remember that your key column should be in any numeric datatype (REAL, FLOAT, DOUBLE, INTEGER, DECIMAL), if it is any other data type, then Data Integrator will throw an error-
- Increment value : Specify your interval for system generated key values. Surrogate key will be incremented based on this interval. From 11.7 version onward, you can specify variables also.
In below example, customer information is being populated. There is a primary key called Customer_ID in both the source & target tables, but SURROGATE_KEY will be maintained to illustrate the key generation transform-
The source data looks like below-
Key generation transform always expects a SURROGATE_KEY column in SCHEMA IN
After completion of the job execution, below is the customer dimension target data with surrogate key values-
The map operation does the insert and the key generation transform creates a new key for each inserted record.