A Surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from the application data. In a star schema database, surrogate keys are used to join a fact table to a dimension table.
The surrogate key is internally generated by the system but is nevertheless visible to the user of the application.
Surrogate Key Generator stage
The Surrogate Key Generator stage is a processing stage that generates surrogate key columns and maintains the key source.
The Surrogate Key Generator stage can have a single input link, a single output link, both an input link and an output link, or no links. Job design depends on the purpose of the stage.
You can use a Surrogate Key Generator stage to perform the following tasks:
- Create or delete the key source before other jobs run
- Update a state file with a range of key values
- Generate surrogate key columns and pass them to the next stage in the job
- View the contents of the state file
Generated keys are unsigned 64-bit integers. The key source can be a state file or a database sequence. If you are using a database sequence, the sequence must be created by the Surrogate Key stage. You cannot use a sequence previously created outside of DataStage.
You can use the Surrogate Key Generator stage to update a state file, but not a database sequence. Sequences must be modified with database tools.