DSXchange

Hi all,

I have a field in the target table namly row_id integer, which should hold the sequence numbers starting from 1..n for every new record (Insert records).
For ex:


Are there any builtin functions in Data Stage or any server processings?

Please let me know what would be the best approach?


Thanks much!

You might want to look at the Routines>>sdk>>KeyMgt----KeyMgtGetNextValueConcurrent or KeyMgtGetNextValue.

The most efficient, if you want to start from 1 for each run, is to use the system variable @OUTROWNUM, since it is keeping that count in any case.

However, if you want a genuine sequence (starting again from where it left off), then either the SDK key management routines as mentioned, or a database sequence or auto-incrementing data type, can be used.

ray.wurlod wrote:The most efficient, if you want to start from 1 for each run, is to use the system variable @OUTROWNUM, since it is keeping that count in any case.

However, if you want a genuine sequence (starting again from where it left off), then either the SDK key management routines as mentioned, or a database sequence or auto-incrementing data type, can be used.

Hi, you mentioned a database sequence, how would you use database sequence in datastage?

While inserting into the table, the value for the Sequence number column would be seq.nextval, where 'seq' is the sequence created using CREATE SEQUENCE statement.

SPA_BI wrote:Hi, you mentioned a database sequence, how would you use database sequence in datastage?

Do NOT provide a key value from the job. Use user-defined SQL. There are three columns defined in the DataStage job in this example.

Hi
I need to create rowid ( sequence numbers) into target table. i am using KeyMgtGetNextValue() function to populate rowid, which is always giving same ids. What my requirement is i need one similat to Informatica's Sequence generater transformation. is there any way we can generate id's like incrementally?

Day 1: starts with 1 and max would be 2009
Day 2: starts with 2010 and increment by 1 max would be 5545
Day 3 should start 5546

CAn some one help me to building my sequence generator logic which i mensioned above in Datastage.

Hi John,

KeyMgtGetNextValue is the function that you need. It works exactly as you have laid out in your Day 1, Day 2, Day 3 scenario. You can take a look at the code for the routine/transform using DataStage Manager. I'm not sure what you could be doing wrong, but do note that you have to pass the same value to the function each day (I usually recommend using the table name). I have seen some new developers try to pass a number to the function.

Mike

Or look under the surrogate keys folder of the functions I gave you. There's an ...AssignSkeyParallel function that works in blocks for parallel assignment if you call it that way (any blocksize other than 1 will fetch numbers in that block range). Shoot me an email if you have any questions.

Is there some permissions around the function KeyMgtGetNextValue. If I run a job, they run fine, however if someone else in my group runs that job, the number is -1 (over and over).

-1 means "bad handle" - they are perhaps trying to test the routine rather than calling it from a job.