DSXchange

Hi

I am generating sequence numbers using the below code: It is generating the numbers but there was gaps in between. Actual rows are 15. But it is giving up to 17 means 15 and 16 are missing.

I believe this is partioning issue as partions are not balanced before the tranformer stage., however i am using round robin in the transformer.

My Job as follows below:

Sequential file-->Transformer(Auto)-->Lookup(Auto)-->Joiner(Hash)-->Lookup(Auto)-->Transformer(Round Robin)-->Sequential file

Preserve partition is clear every where in the Job.

Can somebody help me on this?

Try by changing the Execution mode as "sequential" in transformer stage.

In transformer stage :- STAGE ---->Advanced----->Execution mode

Change that to Sequential.

Please let me know the results.

Yeah, It works fine with this option. But we loose the parallelism thats what i was worrying about this. If we go for this method, needless to use below code i would like to use parallelism.

Then use Round Robin partitioning.

Thanks Ray,

I had done as below:

Sequential file-->Transformer(Auto)-->Lookup(Auto)-->Joiner(Hash)-->Lookup(Auto)-->Transformer(Round Robin)-->Sequential file

Preserve partition is clear every where in the Job.

.But could not get what i was expecting meand There was breaks in teh numberss.

Do i need to chagne in the above partition methods.

Could you please advise?

Hi,

Try the OUTROWNUM variable instead of INROWNUM, as below :

((@NUMPARTITIONS * (@OUTROWNUM -1)) + @PARTITIONNUM) + 1

I will try and let you know

Thanks
Prasad

Result is same.

Can anybody advise on this?

If your requirements are that you do NOT have any gaps in the generated sequence of numbers, you will need to run that stage in sequential mode, or at least with a number of partitions that evenly divides (remainder = 0) the number of rows being processed.

If you would rather have the parallelism, you risk the gaps. You can also use the surrogate key generator stage with a block size of 1, but performance will be poor, perhaps worse than running your transformer sequentially.

Regards,

You cannot generate the numbers like how you expect with this. I have tried before but did not work. You should run the transformer sequentially. And when you run like that, you dont need to use this formula, you can just use @INROWNUM or @OUTROWNUM.