Some DataStage Job design

Updating the Master Reports List (MRL) in Datastage

Create a Job which has a Row Generator stage linking to an ODBC or DB2 stage. Change the parameter in the Row Generator stage to output zero records. Write the SQL to update the MRL table as 'After SQL' in the output ODBC or DB2 stage.

Surrogate Key Generator - Generate Surrogate Key for Data

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.
In Row Generator Stage, we are generating a col "Name".

Surrogate Key Generator - Delete State File

Adding this post as well with Surrogate Key walk through, In this post we will see how to delete a Surrogate Key State file. Its quite simple as creation.

a) Design :
Again, for the deletion of state file, we need only 1 stage in design and that is Surrogate Key Stage.

Surrogate Key Generator - Create/Update State File

In last post ( Surrogate Key Generator - Create State File ) we have seen how to generate the surrogate key file, In this post we will discuss about what are the other ways to generate it and how to update state file

Update Surrogate State File (Flat File) :-
To update the state file, add a Surrogate Key Generator stage to a job with a single input link from another stage.

 

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.