Sequence generator

jdmiceli · Post by **jdmiceli** » Wed Sep 20, 2006 3:01 pm

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!

kris007 · Post by **kris007** » Wed Sep 20, 2006 3:09 pm

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

ray.wurlod · Post by **ray.wurlod** » Wed Sep 20, 2006 3:28 pm

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.

SPA_BI · Post by **SPA_BI** » Wed Sep 20, 2006 5:10 pm

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?

kumar_s · Post by **kumar_s** » Wed Sep 20, 2006 6:05 pm

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.

ray.wurlod · Post by **ray.wurlod** » Thu Sep 21, 2006 2:07 am

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.

Code: Select all

INSERT INTO tablename (keycol, col1, col2, col3) VALUES (seqname.NEXTVAL, :1, :2, :3)

There are three columns defined in the DataStage job in this example.

jdmiceli · Post by **jdmiceli** » Sun Sep 24, 2006 12:41 pm

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.

Mike · Post by **Mike** » Sun Sep 24, 2006 2:47 pm

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

kcbland · Post by **kcbland** » Sun Sep 24, 2006 3:20 pm

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.

Triton46 · Post by **Triton46** » Thu Oct 05, 2006 9:23 am

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).

ray.wurlod · Post by **ray.wurlod** » Thu Oct 05, 2006 2:49 pm

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