DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
rish0201
Participant



Joined: 18 Jun 2017
Posts: 2

Points: 23

Post Posted: Sun Jun 18, 2017 11:19 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
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.

_________________
Software Engineer
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54072
Location: Sydney, Australia
Points: 293282

Post Posted: Sun Jun 18, 2017 6:23 pm Reply with quote    Back to top    

In what way does the given expression "not work" with four nodes?

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
rish0201
Participant



Joined: 18 Jun 2017
Posts: 2

Points: 23

Post Posted: Wed Jun 21, 2017 3:02 am Reply with quote    Back to top    

In 4 nodes the numbers are not generated in sequence. It leaves gaps in between.

_________________
Software Engineer
Rate this response:  
Not yet rated
Mike



Group memberships:
Premium Members

Joined: 03 Mar 2002
Posts: 1011
Location: Omaha, NE
Points: 6517

Post Posted: Wed Jun 21, 2017 7:08 am Reply with quote    Back to top    

Did you use round robin partitioning method?

Mike
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42283
Location: Denver, CO
Points: 217108

Post Posted: Wed Jun 21, 2017 7:51 am Reply with quote    Back to top    

rish0201 wrote:
I want it to start from 1 for each job run.

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.

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
Neelabh
Participant



Joined: 14 Jun 2017
Posts: 3

Points: 19

Post Posted: Thu Jun 22, 2017 4:59 am Reply with quote    Back to top    

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

_________________
- Neelabh Barve

- We aspire! Universe Conspires!!
Rate this response:  
Not yet rated
boxtoby



Group memberships:
Premium Members

Joined: 13 Mar 2006
Posts: 126
Location: UK
Points: 1328

Post Posted: Thu Jun 22, 2017 5:45 am Reply with quote    Back to top    

Hi,

You weren't far out with your original idea.

Here's what I use: (@PARTITIONNUM+1)*10 : @OUTROWNUM

Use @OUTROWNUM rather than @INROWNUM to aviod problems with funnel stages etc.

Hope that helps!

Cheers,
Bob.

_________________
Bob Oxtoby
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours