generate sequence number

just4u_sharath · Post by **just4u_sharath** » Thu Oct 18, 2007 7:00 pm

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.

ArndW · Post by **ArndW** » Thu Oct 18, 2007 7:46 pm

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.

Code: Select all

SELECT CAST(MySequence.nextval AS decimal(9))  AS UNIQUEKEY FROM DUAL

and returning UNIQUEKEY to your lookup stage.

just4u_sharath · Post by **just4u_sharath** » Thu Oct 18, 2007 9:57 pm

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 · Post by **just4u_sharath** » Thu Oct 18, 2007 9:58 pm

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.

baglasumit21 · Post by **baglasumit21** » Thu Oct 18, 2007 10:44 pm

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.

ray.wurlod · Post by **ray.wurlod** » Fri Oct 19, 2007 8:53 am

Not a good answer in a parallel job.

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

just4u_sharath · Post by **just4u_sharath** » Fri Oct 19, 2007 1:14 pm

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

ArndW · Post by **ArndW** » Fri Oct 19, 2007 4:48 pm

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.

ray.wurlod · Post by **ray.wurlod** » Sat Oct 20, 2007 2:19 pm

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.

DSXchange