Incrementing surrogate key

[bhargav_dd]

Hi,

I have a flat file which will be loaded into DB tables
everyday.Suppose for example, if I get 10 records today in the
flatfile,I need to assign a Surrogate Key to these records and
load it into the DB table.So next day, when I get new set of
records I need to track the highest Surrogate Value in the DB
table and then assign surrgate key values to these new records
starting from the highest value.

I am new to data Stage and am struggling to implement this
logic.Can any one of you give explain how it can implemented in
Server Edition?

[piyu]

In 7x you can do it two ways : get the max value from the table, and have it incremented in the transformer for each row being inserted, OR always store the max value in a text file on unix and have it exported to the job environment.

8x provides the functionality in the surrogate key stage.

[vivekgadwal]

You could also use the Key Management utility routines provided by DataStage. Look for "KeyMgmt..." routines (unsure about the name) and you need to provide a parameter to them (a unique one which will keep track of the previous SK values generated).

[chulett]

The biggest question you need to answer regards concurreny - will your load run in isolation or could others be loading into the target as well? That will drive your available strategies.

[gateleys]

Use the built-in transform, KeyMgtGetNextValue('YourSequenceName'). In case there is concurrent writing to the same table, you could use KeyMgtGetNextValueConcurrent('YourSequenceName')

[natashab]

Design the job as shown below:
------------------Surrogate table


FlatFile -------> Lookup ---------->Transformer---------> DB2

............................... ..................... Aggregator--------->Surrogate Table

In the lookup stage ,lookup the table_name(target table name) field and retrieve the Max_val(from surrogate table)for that table_name.

The transformer stage will load data to the target db2 table through one link where it will generate unique key values for the primary key using @rownum+1 function and through the other link max_val data for that particular target table_name will be loaded to the surrogate table via the aggregator stage .In the aggregator stage get the maximum value for the surrogate/primary key field.

[chulett]

Which would certainly work... or you could use one of the noted 'Key Management' routines, save yourself some of the shenanigans.

[arunkumarmm]

Design the job as shown below:
------------------Surrogate table


FlatFile -------> Lookup ---------->Transformer---------> DB2

............................... ..................... Aggregator--------->Surrogate Table

In the lookup stage ,lookup the table_name(target table name) field and retrieve the Max_val(from surrogate table)for that table_name.

The transformer stage will load data to the target db2 table through one link where it will generate unique key values for the primary key using @rownum+1 function and through the other link max_val data for that particular target table_name will be loaded to the surrogate table via the aggregator stage .In the aggregator stage get the maximum value for the surrogate/primary key field.

Thought it was a server job...

[chulett]

It is... just seems to be a badly drawn one.

And even if they meant a PX design, it is easy enough to do the equivalent in Server with a direct reference lookup off the Transformer, although I would use a hashed file for that.

[natashab]

The page didn't allow me to draw the design of the job perfectly.
That's PX job design.
If it's a server job then a hash file can be used to do the lookup.

[chulett]

Yah, ASCII Art here can be a little challenging as the forum software automagically removes whitespace from everything that is not wrapped in code tags. So what you need to do is use the code tags and then liberally Preview what your output is going to look like in order to get it right. It won't line up like you think it will due to the proportional font used here, so it will need to look a little goofy on your side to line up after it has been posted.