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



Joined: 10 May 2005
Posts: 452

Points: 4239

Post Posted: Mon Dec 19, 2011 1:34 pm Reply with quote    Back to top    

DataStage® Release: 7x
Job Type: Parallel
OS: Unix
I have a scenario where I need to generate numbers from database sequencer for every record.

In server job, we usually call the DB sequencer in DB2 stage as a lookup with transformer and in lookup DB2 stage we will have the SQL to fetch the next number from the DB swquencer which works.

But how do I achieve the same in Parallel job ? I have a job where I read from a sequential file and using lookup stage and DB2EE stage is connected to the lookup stage with Sparse lookup set. What happens here is that since I run in 2 node config file 50% of records gets one number and other 50% gets another number which is not what I am looking for. I want the number to be generated for every incoming record.

Input:

2
3
4
5
6
7

Desired Output is :

2,1
3,2
4,3
5,4
6,5
7,6

Output out of the above mentioned job design is :

2,1
3,1
4,1
5,2
6,2
7,2


Can someone tell me if there is a way to accomplish this ?

Thanks
Kirtikumar
Participant



Joined: 15 Oct 2004
Posts: 437
Location: Pune, India
Points: 3612

Post Posted: Mon Dec 19, 2011 10:28 pm Reply with quote    Back to top    

Can you tell us the query written in the Sparse and is the job generating any warning?

_________________
Regards,
S. Kirtikumar.
Rate this response:  
Not yet rated
pandeesh



Group memberships:
Premium Members, Heartland Usergroup

Joined: 24 Oct 2010
Posts: 1399
Location: CHENNAI, TAMIL NADU
Points: 9855

Post Posted: Mon Dec 19, 2011 10:52 pm Reply with quote    Back to top    

I guess you are running the job in 3 node configuration.
Thats the reason you are getting the same key for 3 records.
Try to run the job in single node.
You can find the difference.

_________________
pandeeswaran
Rate this response:  
Not yet rated
singhald
Participant

Group memberships:
Heartland Usergroup

Joined: 23 Aug 2005
Posts: 180
Location: Bangalore
Points: 1325

Post Posted: Mon Dec 19, 2011 11:22 pm Reply with quote    Back to top    

execute your db2 stage in sequential mode, may be you are running your reference stage in parallel mode.

go to stage propertise and click advance and change execution mode to sequential mode

_________________
Regards,
Deepak Singhal
Everything is okay in the end. If it's not okay, then it's not the end.
Rate this response:  
Not yet rated
kaps
Participant



Joined: 10 May 2005
Posts: 452

Points: 4239

Post Posted: Tue Dec 20, 2011 6:24 pm Reply with quote    Back to top    

Following is the SQL in DB2 lookup stage.

SELECT 1 as ID1, NEXTVAL FOR SEQ_NAME as NEXTVALUE FROM sysibm.sysdummy1

I tried with Sequential mode in the Lookup stage but it gives me just one value for all records and similar output If I run with 1node config file.

Job is generating the below warning but am not sure that affects the output.

Lookup_13: When checking operator: When binding output interface field "NEXTVALUE" to field "NEXTVALUE": Implicit conversion from source type "int64" to result type "int32": Possible range limitation.


Can someone tell me what am I doing wrong ?

Thanks
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

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

Joined: 23 Oct 2002
Posts: 54601
Location: Sydney, Australia
Points: 296091

Post Posted: Tue Dec 20, 2011 6:41 pm Reply with quote    Back to top    

Your metadata specifies Integer as the data type for the field, but NEXTVAL delivers a BigInt. Specify BigInt as the data type for the field and the warning will disappear.

_________________
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Rate this response:  
Not yet rated
kaps
Participant



Joined: 10 May 2005
Posts: 452

Points: 4239

Post Posted: Tue Dec 20, 2011 10:34 pm Reply with quote    Back to top    

Yes. Changing the data type to Bigint eliminated the warning but I am still getting the same output. Moreover I have 16 records as input and I get 8 records fetching one value and other 8 fetching another value though I run in 1 node config file. Can someone explain this behaviour ?

following is the config gile from job log.

Quote:

main_program: APT configuration file: /opt/dsadm/Ascential/DataStage/Configurations/1node.apt
{
node "node0"
{
fastname "etl1"
pools "" "node1" "etl1" "mnode"
resource disk "/opt/dsadm/Ascential/DataStage/Datasets" {pools ""}
resource scratchdisk "/opt/dsadm/Ascential/DataStage/Scratch" {pools ""}
}
node "db2node0"
{
fastname "db01"
pools "db2"
resource disk "/tmp" {pools ""}
resource scratchdisk "/tmp" {pools ""}
}
}
Rate this response:  
Not yet rated
pandeesh



Group memberships:
Premium Members, Heartland Usergroup

Joined: 24 Oct 2010
Posts: 1399
Location: CHENNAI, TAMIL NADU
Points: 9855

Post Posted: Tue Dec 20, 2011 10:41 pm Reply with quote    Back to top    

Hi,

Just change the setting in db2 stage to sequential mode(since it's reference)and try,

_________________
pandeeswaran
Rate this response:  
Not yet rated
prasadgv
Participant



Joined: 22 Jul 2008
Posts: 2
Location: bangalore
Points: 8

Post Posted: Tue Dec 20, 2011 11:05 pm Reply with quote    Back to top    

Hi,

Change the partition type to "Entire" for Reference Data
and check it. You will get correct, i think.

Prasad GV
Rate this response:  
Not yet rated
prasadgv
Participant



Joined: 22 Jul 2008
Posts: 2
Location: bangalore
Points: 8

Post Posted: Tue Dec 20, 2011 11:06 pm Reply with quote    Back to top    

Hi,

Change the partition type to "Entire" for Reference Data
and check it. You will get correct, i think.

Prasad GV
Rate this response:  
Not yet rated
pandeesh



Group memberships:
Premium Members, Heartland Usergroup

Joined: 24 Oct 2010
Posts: 1399
Location: CHENNAI, TAMIL NADU
Points: 9855

Post Posted: Tue Dec 20, 2011 11:09 pm Reply with quote    Back to top    

For any reference link, even if you specify as "AUTO", datastage internally considers that as "ENTIRE".
SO there wont be any difference

_________________
pandeeswaran
Rate this response:  
Not yet rated
Kirtikumar
Participant



Joined: 15 Oct 2004
Posts: 437
Location: Pune, India
Points: 3612

Post Posted: Wed Dec 21, 2011 12:47 am Reply with quote    Back to top    

Are you sure you have selected the lookup type as sparse? Because sparse lookup (I think) needs a condition in the where clause. As you said your SQL is:
Code:
SELECT 1 as ID1, NEXTVAL FOR SEQ_NAME as NEXTVALUE FROM sysibm.sysdummy1


But then where is the SQL clause? And what is the key column used - ID1? Then add a where clause as:
Code:
SELECT 1 as ID1, NEXTVAL FOR SEQ_NAME as NEXTVALUE FROM sysibm.sysdummy1 WHERE 1 = $ORCHESTRATE.ID1


Make sure the column name is same on stream link and Ref link for ID1.

Let us know how it goes.

_________________
Regards,
S. Kirtikumar.
Rate this response:  
Not yet rated
pandeesh



Group memberships:
Premium Members, Heartland Usergroup

Joined: 24 Oct 2010
Posts: 1399
Location: CHENNAI, TAMIL NADU
Points: 9855

Post Posted: Wed Dec 21, 2011 1:01 am Reply with quote    Back to top    

it's something like:

Code:

SELECT 1 as ID1, NEXTVAL FOR SEQ_NAME as NEXTVALUE FROM sysibm.sysdummy1 WHERE ID1=ORCHESTRATE.ID1


I suspect ,you are making mistakes somewhere.
Are you fetching NEXTVAL to output?
And also are you sure, in both input and reference the ID1 has 1 as always?

_________________
pandeeswaran
Rate this response:  
Not yet rated
kaps
Participant



Joined: 10 May 2005
Posts: 452

Points: 4239

Post Posted: Wed Dec 21, 2011 6:16 pm Reply with quote    Back to top    

I can't use WHERE ID1=ORCHESTRATE.ID1 as ID1 field is not there in sysdummy table but used WHERE 1=ORCHESTRATE.ID1 and got the same result meaning just two numbers generated from the sequencer.
Out of 18, 9 got 1 seq number and rest got another seq number.

I know I am doing something wrong but wondering what. May be we can't do this in Prallel...

All views are appreciated.

Thanks
Rate this response:  
Not yet rated
shamshad



Group memberships:
Premium Members

Joined: 25 Aug 2004
Posts: 147
Location: Detroit,MI
Points: 1383

Post Posted: Wed Dec 21, 2011 6:53 pm Reply with quote    Back to top    

Even if you are using multiple node, you can force the file to be read sequentially so that each row gets the next value. Look into the file property and there is a setting to read it sequentially

_________________
Datawarehouse Consultant
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