Generating Next val from table lookup

[ag_ram]

Hello Folks

Here is a brief background of the the problem that we have . We have 2 links , an dataset input link that contain some key values and a database that stores a sequence number . The requirement is get the sequence for the first record in the dataset , and for each subsequent row from the dataset this sequence should be incremented .
The easiest way is used to use a Sparse look-up and generate subsequent sequence based on the first value , however this has a big overhead as # of records can be in millions (and in essence you will have a million hits to the DB)

What will be easiest way of doing this in DataStage

[sud]

Hello Folks

Here is a brief background of the the problem that we have . We have 2 links , an dataset input link that contain some key values and a database that stores a sequence number . The requirement is get the sequence for the first record in the dataset , and for each subsequent row from the dataset this sequence should be incremented .
The easiest way is used to use a Sparse look-up and generate subsequent sequence based on the first value , however this has a big overhead as # of records can be in millions (and in essence you will have a million hits to the DB)

What will be easiest way of doing this in DataStage

By sequence of first record in dataset you must be meaning that you are capturing the max sequence number from the database and starting generation of the remaining ones with that number. Why don't you handle finding the starting sequence number in a pre job and pass that number as a parameter to this job.

[ray.wurlod]

The easiest way to do it in DataStage would be to select that starting value from the target table in a reference input to a Lookup stage, with a constant key, and using Entire partitioning to get it onto every partition.

Code: Select all

SELECT 'X' AS DUMMYKEY, MAX(KeyCol) + 1 AS NEXTVAL FROM targettable

Then add it to a generated sequence downstream of the Lookup stage.