Surrogate Key Generator - Create State File

Surrogate keys are numeric keys that can replace business keys in the datawarehouse.  A surrogate key has the following characteristics:

1) It is typically an integer.
2) Surrogate keys are unique.
3) Surrogate keys allow you to combine data from tables with incompatible keys.
4) It has no meaning. You will not be able to know the meaning of that row of data based on the surrogate key value.
5) It is not visible to end users. End users should not see a surrogate key in a report.

I will not go in details of surrogate key, I will post it later, But we will see how to generate surrogate keys in DataStage.

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.

What is State File -
State File is a internal format file which is used for key management, it can keep track of previous values all by itself. It retrieve the last key used, generating new keys and writing the last key used back to the state file.

Create State File -
For Generating/Creating State file, we will need only a single stage 'Surrogate Key Generator' and fill the properties like below screen.

Surrogate Key Stage Properties -    

1. Set the Key Source Action property to Create.
2. Type or browse for the source name which must be a existing flat file ( Usually empty file but we can use a file which contain the old keys ) or a database sequence.
3. Select the source type.

Now, Save, Compile the job and Run the job. This will create Surrogate Key State file which we used as a source file for the Surrogate Key Stage. You can not see the content of State File in text editors, It is converted into internal format State File.

Like the Facebook Page & join Group
https://www.facebook.com/DataStage4you
https://www.facebook.com/groups/DataStage4you