Get next value from DB Seq

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

kaps
Participant
Posts: 452
Joined: Tue May 10, 2005 12:36 pm

Get next value from DB Seq

Post by kaps »

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
Posts: 437
Joined: Fri Oct 15, 2004 6:13 am
Location: Pune, India

Post by Kirtikumar »

Can you tell us the query written in the Sparse and is the job generating any warning?
Regards,
S. Kirtikumar.
pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

Post by pandeesh »

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
singhald
Participant
Posts: 180
Joined: Tue Aug 23, 2005 2:50 am
Location: Bangalore
Contact:

Post by singhald »

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.
kaps
Participant
Posts: 452
Joined: Tue May 10, 2005 12:36 pm

Post by kaps »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
kaps
Participant
Posts: 452
Joined: Tue May 10, 2005 12:36 pm

Post by kaps »

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.
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 ""}
}
}
pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

Post by pandeesh »

Hi,

Just change the setting in db2 stage to sequential mode(since it's reference)and try,
pandeeswaran
prasadgv
Participant
Posts: 2
Joined: Tue Jul 22, 2008 7:47 am
Location: bangalore

Re: Get next value from DB Seq

Post by prasadgv »

Hi,

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

Prasad GV
prasadgv
Participant
Posts: 2
Joined: Tue Jul 22, 2008 7:47 am
Location: bangalore

Re: Get next value from DB Seq

Post by prasadgv »

Hi,

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

Prasad GV
pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

Post by pandeesh »

For any reference link, even if you specify as "AUTO", datastage internally considers that as "ENTIRE".
SO there wont be any difference
pandeeswaran
Kirtikumar
Participant
Posts: 437
Joined: Fri Oct 15, 2004 6:13 am
Location: Pune, India

Post by Kirtikumar »

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 all

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 all

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.
pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

Post by pandeesh »

it's something like:

Code: Select all

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
kaps
Participant
Posts: 452
Joined: Tue May 10, 2005 12:36 pm

Post by kaps »

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
shamshad
Premium Member
Premium Member
Posts: 147
Joined: Wed Aug 25, 2004 1:39 pm
Location: Detroit,MI

Post by shamshad »

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
Post Reply