How to generate sequence no

sivap · Post by **sivap** » Mon May 30, 2005 7:02 pm

Hi

I want to generate a sequence no staring from 1. How to do that?

SeqFile -------> Xfrm -------> HashFile.

After completion this job, I want to generate one more seq no in second job i.e max no in the first job+1.

Thanks and Regards

ray.wurlod · Post by **ray.wurlod** » Mon May 30, 2005 7:47 pm

You must keep knowledge of where the first job left off. The easiest way is to design so that this is kept in a file (text file or hashed file). Before the first run, initialize the value in this file to 0. From your job, maintain your sequence however you like. Create an output link that passes only the sequence value into an Aggregator stage, and derive the Last value of it, which you write to your storage location. The job picks up the starting value from the storage location.

Or you can leverage the SDK routines, such as KeyMgtNextValue, which do exactly what I've described above. Why reinvent the wheel?

sivap · Post by **sivap** » Mon May 30, 2005 8:32 pm

ray.wurlod wrote:You must keep knowledge of where the first job left off. The easiest way is to design so that this is kept in a file (text file or hashed file). Before the first run, initialize the value in this file to 0. From your job, maintain your sequence however you like. Create an output link that passes only the sequence value into an Aggregator stage, and derive the Last value of it, which you write to your storage location. The job picks up the starting value from the storage location.

Or you can leverage the SDK routines, such as KeyMgtNextValue, which do exactly what I've described above. Why reinvent the wheel?

Thank ray

I tried with KeyMgtNextValue , but i am gettig starting value 687 ,2124,...
but i want starting value with 1 to n(no of rows in the sorce file).when ever i run this job the no should get 1 to n. which is best way to do it?

chulett · Post by **chulett** » Mon May 30, 2005 8:53 pm

sivap wrote:i want starting value with 1 to n(no of rows in the sorce file).when ever i run this job the no should get 1 to n. which is best way to do it?

Not sure why you'd want it to always start over at 1 each time, but if you do there's an easy way to do that: the @INROWNUM System Variable. This tracks the record number coming into any given transformer, so it will 'number' all of the records from 1 to n. You can then use that as the 'surrogate'.

FYI, There is also an @OUTROWNUM as well.

ray.wurlod · Post by **ray.wurlod** » Mon May 30, 2005 11:55 pm

You've changed your mind!

In your first post you specified "After completion this job, I want to generate one more seq no in second job i.e max no in the first job+1." That's the solution I provided.

To begin from 1 each time, you can use @INROWNUM or @OUTROWNUM as Craig suggested, or reset the sequence name used by KeyMgtNextValue. Search the forum for how to accomplish this using a before-stage subroutine.