I want to generate sequence numbers starting from 1 every time the job run.
Number should be generated in sequence without gaps in 4 node job.
Thing i have already tried
1) (@inrownum - 1) * @numpartitions + @partitionnum + 1
The above formula only works in 2 node.
2) Column generator - It creates gaps between numbers in 4 nodes and the numbers are not in sequence.
3) Surrogate Key - It takes the last highest value and starts from there. I want it to start from 1 for each job run. Is there any way to reset the key ?
Any suggestions is welcome.
Thanks in Advance.
Generate sequence number using parallel job
Moderators: chulett, rschirm, roy
Generate sequence number using parallel job
Software Engineer
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Generate sequence number using parallel job
Then it would seem you do not have a need for any kind of surrogate key 'generator' but simply need a counter. At its most simple, you could use a stage variable for that, initial value of zero, increment for each record... as long as you handled the partitioning properly.rish0201 wrote:I want it to start from 1 for each job run.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: Generate sequence number using parallel job
As inferred by @Mike as well this method requires data to be evenly distributed across all partition hence it works well with Round Robin partitioning. If not it will generate sequence with "gaps" (i.e. what you are observing).
Use other methods as suggested by craig.
1) Use Stage variable.
2) If source is seq. file stage generate row number.
3) If database use row_number function.
4) Enable transformer stage to run on a single node and use @INROWNUM
Use other methods as suggested by craig.
1) Use Stage variable.
2) If source is seq. file stage generate row number.
3) If database use row_number function.
4) Enable transformer stage to run on a single node and use @INROWNUM
- Neelabh Barve
- We aspire! Universe Conspires!!
- We aspire! Universe Conspires!!