surrogate key generation in server jobs

[prerana]

Hi,
the query for us is how we can generate surogate key for output sequential file and for output database table?

[rumu]

Hi,
the query for us is how we can generate surogate key for output sequential file and for output database table?

Hi,
Use KeyMgmtGetNextSurrogate key function.It is already in place.

[balajisr]

Hi,
the query for us is how we can generate surogate key for output sequential file and for output database table?

Hi,
Use KeyMgmtGetNextSurrogate key function.It is already in place.

I think it is better to use @OUTROWNUM instead of KeyMgtGetNextValue as you can avoid resetting the SDKSequences hash file.

[ArndW]

But each run of your job will have @OUTROWNUM starting at 1 and counting up and this method will generate duplicate keys....

[balajisr]

But each run of your job will have @OUTROWNUM starting at 1 and counting up and this method will generate duplicate keys.... ...

That's correct.
Sorry ArndW.
Thanks for your clarification.

[kumar_s]

Welcome aboard :D
Search for the same in the forum you can find lot of informations related to this. You can derive you own logic to generate the surrogate key using transforer. That may even include the avaiulable keymanagement utility as a part concatinating with the run cycle number...etc based on the requirement.

[gateleys]

As rumu has suggested, use the KeyMgmt sdk function. You could use your database to generate the sequence for you, but we want as much work as possible to be done within DataStage. Further, you also want to load it to a sequential file, so the KeyMgmt option would be just right. Besides, that's why it is made available.

gateleys

[sb_akarmarkar]

Hi prerana,

We can generate surogate key using routine but it increases our maintance ,so i suggest better to use auto generated key in database...
like identity column for SQL server and Sequence generator in Oracle.


Thanks,

--Anupam

[kumar_s]

But for many case surrogate key would be requred even before the target for change detection. If source is not from database, it would be required to generate a unique key for identification.

[nkln@you]

Hi,
I am generating surrogate keys using a sequence and trigger in Oracle. During an insert in the table, the trigger is fired and it assigns next value of the sequence to a column(primary key). But it has been suggested that we should use Datastage Sequence generator for the same. So Just wanted to know which one is better.. using datastage sequence generator or using sequence and trigger from oracle..

[nkln@you]

Hi,
I am generating surrogate keys using a sequence and trigger in Oracle. During an insert in the table, the trigger is fired and it assigns next value of the sequence to a column(primary key). But it has been suggested that we should use Datastage Sequence generator for the same. So Just wanted to know which one is better.. using datastage sequence generator or using sequence and trigger from oracle..

[chulett]

Neither one is 'better', both are perfectly valid techniques. Some shops will have no truck with triggers and prefer to not use them, so find other mechanisms. One such is generating the surrogate inside the DataStage job stream, another is to use custom sql in the insert to leverage SeqName.nextval to populate the column.

If you need to know the value of the surrogate for, say, child record processing then you would need to generate it yourself in the job. That rather than come up with some way to find them after the fact.

Best to start a new post for times like this, rather than find a somewhat similar post and jump on the end of it.

[shamshad]

In order to be sure that nextkeyvalue is not messed up, I always use this option:

(1) Once I am at the transformer where all database column is ready, I drag a query and do a select MAX(KeyCol) from Table and then get that value through a lookup. Once you get the MAX value from target table, you can keep adding +1 to the value.

In order to avoid hitting the database table again and again, you can also design a small job that has database table as source, select max(Key) from table and dump it in a hash file and then in the next job, simply use this hash file to get the MAX value.

It has worked for me everytime and never had any issue with UNIQUE CONSTRAINT

[chulett]

Yes, that can work - as long as your job runs in isolation and no other process is inserting rows to the target table at the same time.