Look for available number - routine question

[tonystark622]

You can do the same thing in DataStage, but I don't know if it will be any faster:

1) Create a new server job.
2) Add a transformer and a sequential file stage.
3) Create an output link from the transformer to the sequential file stage.
4) Create a new stage variable in the transformer stage. Call it InitCount, with an initial value of 0.
5) In the derivation of the stage variable put : InitCount + 1
6) Create an output column in the output link.
7) In the derivation for the output column put the Stage Variable name: InitCount.
8) In the constraint for the output link put : InitCount < 10001
(where 10000 is the number of rows that you want in the output file.)
9) fill in the information for the sequential file : name, format, etc.
10) Compile and run.

You could probably create a Job parameter and use it in the constraint so that you could specify how many rows you want to output. (Yep, you can I tested it. Create Job Parameter RowCount and set it to 10001. Change the constraint to InitCount < RowCount.)

I know that this looks odd, because the transformer doesn't have any input links, but it should work. Works nicely in v7.1, anyway.

Good Luck,
Tony

[kudaka]

Thanks for all your great ideas. I didn't know that server job works without input. rownum also works great.

I will be very happy if you guys can help me on how to fetch unused numbers one at time (like stack). Also how do I delete the number after using it from the file?

Thanks for all your help.

[kcbland]

There's lots of ways to feed the unused numbers in. One method is to put them into a hash file using a primary key column derived using @OUTROWNUM. In your transformation job, when it needs a surrogate key, simply does a lookup against this hashfile using @OUTROWNUM as the keyexpression and returns the column containing the next surrogate key.

Another method would be to use a function utilizing a COMMON set of variables, one the array of numbers in the file and the other variable a indexing value to hold the current position in the array you are on. The first time the function is called the file is loaded, subsequent calls simply return the next value.

LOTS of ways to do this.

[kudaka]

I am not quite understood about using @OUTROWNUM.
Can you explain it again? How to do lookup and what we write in the derivation?
Thanks

[kudaka]

kcbland ,
Will you be able to explain this again? I could not make @OUTROWNUM work for me. It gives sequence numbers.
What should be in key expression?
and what is the derivation?
Thanks
-Kud

[kcbland]

I suggested that you put the list of unused numbers into a hash file. So, have a job read the sequential text file of unused numbers. The hash file should be defined with two columns:

<1> OUTROWNUM (check this as the key to the hash file)
<2> UNUSED_NUMBER (this will be an integer)

Now, in the transformer, map the unused number read from the sequential file into the unused_number column in the hash file. For the OUTROWNUM column, use @OUTROWNUM in the derivation.

In the job that needs to assign the next unused number, do a lookup against the unused number hash file. In the key expression for the hash file, use the value @INROWNUM.

What this does is for every inbound row, lookup a value from the hash file based on the inbound row number. So, the first row in your transformation job will lookup row #1 in the hash file. The second row will lookup row #2 in the hash file, and so on. Just make sure you have plenty of numbers in your hash file, so that your transformation job doesn't run out of numbers.

Myself, I'd prefer to use a function to read the sequential text file ala the previous posts, but I suspect that you are very new to DataStage and instead posted here a solution you might have an easier time developing.

[kudaka]

I have not used the @INROWNUM, @OUTROWNUM in the solution to this problem. Instead, I have created a routine to open the unused number hash file and return the first number with every call then delete that number from hash file. This is very simple approach, but don't know why I could not thought about it in the first place.

Thanks for all your help.

[kcbland]

Your solution is going to be very slow, I hope your volumes are low.