Surrogate Key Generator In Datastage

/ Comments off

May 07, 2013  surrogate-key-generation in Transformer Create a unique counter in datastage This entry describes various ways of creating a unique counter in DataStage jobs. A unique and common surrogate key is a one-field numeric key which is shorter, easier to maintain and understand, and independent from changes in source system than using a business key. Also, if a surrogate key generation process is implemented correctly, adding a new source system to the data warehouse processing will not require major efforts. Rsps 718 generating rsa keys.

The Surrogate Key Generator stage is a processing stage that generates surrogate key columns and maintains the key source.
A surrogate key is a unique primary key that is not derived from the data that it represents, therefore changes to the data will not change the primary key. In a star schema database, surrogate keys are used to join a fact table to a dimension table.
  • 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

Feb 27, 2016  Scenario 15: How to populate the surrogate key in target table using sequence generator transformation. It contains two output ports that you can connect to one or more transformations. Apr 26, 2011  Generate Surrogate Key without Surrogate Key Stage April 26, 2011 ukatru Leave a comment Go to comments Use the following formula in Transformer stage to generate a surrogate key. Jun 04, 2014  In this post, We will see how to generate surrogate key for data, where we have to use surrogate key stage. A) Design: Below design is a demo design of job. Here our data source is a row generator which is generating rows. In real time scenario, Source can be a flat file, DB stages, Passive Stage or can be a Active stage also. Aug 19, 2018  Surrogate Key Generator Stage in DataStage The Surrogate Key Generator stage is a processing stage that generates surrogate key columns and maintains the key source. A surrogate key is a unique primary key that is not derived from the data that it represents, therefore changes to the data will not change the primary key.

Generated keys are 64 bit integers and the key source can be stat file or database sequence.
Drag the surrogate key stage from palette to parallel job canvas with no input and output links.
Double click on the surrogate key stage and click on properties tab.
Properties:
Source Type : FlatFile or Database sequence(in this case we are using FlatFile)
When you run the job it will create an empty file.
If you want to the check the content change the View Stat File = YES and check the job log for details.
skey_genstage,0: State file /tmp/skeycutomerdim.stat is empty.
if you try to create the same file again job will abort with the following error.
skey_genstage,0: Unable to create state file /tmp/skeycutomerdim.stat: File exists.
To update the stat file add surrogate key stage to the job with single input link from other stage.
We use this process to update the stat file if it is corrupted or deleted.
1)open the surrogate key stage editor and go to the properties tab.
If the stat file exists we can update otherwise we can create and update it.
We are using SkeyValue parameter to update the stat file using transformer stage.
Now we have created stat file and will generate keys using the stat key file.
Click on the surrogate keys stage and go to properties add add type a name for the surrogate key column in the Generated Output Column Name property
Rowgen we are using 10 rows and hence when we run the job we see 10 skey values in the output.
I have updated the stat file with 100 and below is the output.
If you want to generate the key value from begining you can use following property in the surrogate key stage.
  1. If the key source is a flat file, specify how keys are generated:
    • To generate keys in sequence from the highest value that was last used, set the Generate Key from Last Highest Value property to Yes. Any gaps in the key range are ignored.
    • To specify a value to initialize the key source, add the File Initial Value property to the Options group, and specify the start value for key generation.
    • To control the block size for key ranges, add the File Block Size property to the Options group, set this property toUser specified, and specify a value for the block size.
  2. If there is no input link, add the Number of Records property to the Options group, and specify how many records to generate.

License Key Generator


Serial Key Generator

COURTESY: http://datastage4u.wordpress.com/2012/01/16/surrogate-key-generator-stage-infosphere-information-server-8-5/