Sequence Number

umamahes · Post by **umamahes** » Wed Feb 21, 2007 6:34 pm

I need to generate a sequence number.we have any option like KeymgmtGetNextvalue in parallel jobs.Surrogate key generator stage is not suitable for my requirement.

Thanks
uma

ArndW · Post by **ArndW** » Wed Feb 21, 2007 6:57 pm

Why doesn't the surrogate key generation work for you? The answer to that might help in finding a something that you can use.

ray.wurlod · Post by **ray.wurlod** » Wed Feb 21, 2007 10:16 pm

Stop thinking like a server job developer. Give some thought to how you might construct a sequence that is unique over all partitions, apart from the simple-minded approach of sequential execution of the generator. Hint: you will need to include the partition number and partition count in your calculations. You may, when you Search in the forum, discover that other people have already solved this, both with and without a Surrogate Key Generator stage. There is no SDK for parallel jobs, but there is nothing to stop you creating your own parallel routine. Just beware that the same parallelism restrictions will apply in your routine as well.

DSguru2B · Post by **DSguru2B** » Wed Feb 21, 2007 11:20 pm

Vincent has an FAQ entry on the same.

JoshGeorge · Post by **JoshGeorge** » Thu May 03, 2007 8:48 pm

How about this:
Put an external filter stage and use UNIX command cat -n
(Propagate a column from the previous stage for this with blank probably - varchar of the size you want)

ray.wurlod · Post by **ray.wurlod** » Thu May 03, 2007 11:46 pm

Only if you ensure that the External Source stage executes in sequential mode. Otherwise you'll get a separate sequence of numbers starting from 1 on each processing node.

nick.bond · Post by **nick.bond** » Fri May 04, 2007 12:09 am

I would go with vincent's FAQ solution. No point mkaing forcing data into sequential mode just to get a sequence number.

JoshGeorge · Post by **JoshGeorge** » Fri May 04, 2007 11:11 pm

nick.bond wrote:I would go with vincent's FAQ solution. No point mkaing forcing data into sequential mode just to get a sequence number.

"No point" ?? Definitly you earned some points for the above post

In this case External filter execute on sequential mode even if you specify as parallel. If you see the dump score report you can see that.

ray.wurlod · Post by **ray.wurlod** » Sat May 05, 2007 1:46 am

Wrong. You can change execution mode to parallel on the Advanced tab of the stage properties. Indeed (at version 7.5.1A) the default execution mode for an External Source stage is parallel. The same is true of the External Filter stage, but I'm not sure how this crept into the discussion.

JoshGeorge · Post by **JoshGeorge** » Sat May 05, 2007 3:09 am

I did some testing on this! I specified explicitly 'parallel' in the external filter stage. Checked the dump score report in the director. External filter ran on single node where as other 2 stages ran parallely on multiple nodes. Apparently the name of the external filter itself is showing as sequential external filter in the score report.

This is the job

Row generator (parallel mode) --> External filter (parallel mode) --> Peek