Numbers issue

prasad v · Post by **prasad v** » Mon May 30, 2011 3:55 am

Hi

I am generating sequence numbers using the below code:

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

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?

jyothisdasms · Post by **jyothisdasms** » Mon May 30, 2011 4:13 am

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.

prasad v · Post by **prasad v** » Mon May 30, 2011 4:26 am

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

Code: Select all

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

i would like to use parallelism.

ray.wurlod · Post by **ray.wurlod** » Mon May 30, 2011 5:10 am

Then use Round Robin partitioning.

prasad v · Post by **prasad v** » Mon May 30, 2011 5:19 am

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?

google · Post by **google** » Mon May 30, 2011 6:52 am

Hi,

Try the OUTROWNUM variable instead of INROWNUM, as below :

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

prasad v · Post by **prasad v** » Tue May 31, 2011 8:21 am

I will try and let you know

Thanks
Prasad

prasad v · Post by **prasad v** » Tue May 31, 2011 8:28 am

Result is same.

Can anybody advise on this?

jwiles · Post by **jwiles** » Tue May 31, 2011 1:31 pm

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,

arunkumarmm · Post by **arunkumarmm** » Tue May 31, 2011 3:41 pm

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.