surrogate key generation in server jobs
Moderators: chulett, rschirm, roy
surrogate key generation in server jobs
Hi,
the query for us is how we can generate surogate key for output sequential file and for output database table?
the query for us is how we can generate surogate key for output sequential file and for output database table?
Re: surrogate key generation in server jobs
Hi,prerana wrote:Hi,
the query for us is how we can generate surogate key for output sequential file and for output database table?
Use KeyMgmtGetNextSurrogate key function.It is already in place.
Re: surrogate key generation in server jobs
I think it is better to use @OUTROWNUM instead of KeyMgtGetNextValue as you can avoid resetting the SDKSequences hash file.rumu wrote:Hi,prerana wrote:Hi,
the query for us is how we can generate surogate key for output sequential file and for output database table?
Use KeyMgmtGetNextSurrogate key function.It is already in place.
But each run of your job will have @OUTROWNUM starting at 1 and counting up and this method will generate duplicate keys....
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
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
gateleys
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
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..
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..
Aim high
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..
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..
Aim high
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
(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