DSXchange

I would like to generate a nine digit sequence number in my job. is it possible using any function in transformer. is there any way that i can generate a nine digit sequence number using datastage. And as well the sequence number should be incremented for every run of the job(ofcourse sequence number is which increments or decrements after every run). can i do this in datastage . or should i use a oracle sequencer in datastage. if i need to use an oracle sequencer, how can i built tat oracle sequencer into the datastage.

Given that you are using PX and wish to have a unique sequence number across run youre best choice is to use an Oracle sequence. You can get the next sequence number by doing a lookup to an Oracle Enterprise stage which has user-written SQL, i.e. and returning UNIQUEKEY to your lookup stage.

That was a good reply.. But right now i dont have sufficient privileges to create a sequence and call it from datastage. if there are any other methods please suggest.

That was a good reply.. But right now i dont have sufficient privileges to create a sequence and call it from datastage. if there are any other methods please suggest.

just4u_sharath wrote:That was a good reply.. But right now i dont have sufficient privileges to create a sequence and call it from datastage. if there are any other methods please suggest.

There are many ways to achieve what you want. There are some built-in routines provided by datastage. You can use them. Also you can do this by the use of a hash file. Store the initial value in a hash file and then increment everytime the job runs.

You will have to read and write on the same file.

Not a good answer in a parallel job.

And it's "hashed" file, not "hash" file.

i dont have sufficient privileges to create an oracle sequence. then how can i generate a sequence number. can i use built in routine keymgtgetnextvalue. but i am working in enterprise edition and creating parallel jobs. can i use thar routine in parallel jobs

No, you cannot effectively use the KeyMgtGetNextValue() routine in a PX job. Generate a unique sequence number in your job can be done by using the stage variables @INROWNUM and @PARTITIONNO (did I remember that correctly?). That is easy, now you need to figure out a way to make that number unique on each run. You have many options - here are just 2

- Pass in the starting number as parameter. Use "(@INROWNUM*(@PARTITIONNUM+1)+StartingNumber)" as you derivation. Store the maximum value into either a sequential file or table at the end of the job, and read it from there prior to calling the job and pass it in as StartingNumber.

- Use a big numeric sequence and always start it at YYYYMMDDHHMMSS generated from the job's start date/time. That is assuming you don't start this job as a multi-instance at the same time.

Even easier is to use a Column Generator stage or a Surrogate Key stage. You can set the initial value via a job parameter added to the partition number. Set the increment to the number of partitions.