Get next value from DB Seq

kaps · Participant Joined: 10 May 2005 Posts: 452 Points: 4239

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 · Posted: Mon Dec 19, 2011 10:28 pm

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

pandeesh · Posted: Mon Dec 19, 2011 10:52 pm

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.

singhald · Posted: Mon Dec 19, 2011 11:22 pm

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

kaps · Participant Joined: 10 May 2005 Posts: 452 Points: 4239

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 · Posted: Tue Dec 20, 2011 6:41 pm

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.

kaps · Participant Joined: 10 May 2005 Posts: 452 Points: 4239

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 ""}
}
}

pandeesh · Posted: Tue Dec 20, 2011 10:41 pm

Hi,

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

prasadgv · Posted: Tue Dec 20, 2011 11:05 pm

Hi,

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

Prasad GV

prasadgv · Posted: Tue Dec 20, 2011 11:06 pm

Hi,

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

Prasad GV

pandeesh · Posted: Tue Dec 20, 2011 11:09 pm

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

Kirtikumar · Posted: Wed Dec 21, 2011 12:47 am

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.

pandeesh · Posted: Wed Dec 21, 2011 1:01 am

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?

kaps · Participant Joined: 10 May 2005 Posts: 452 Points: 4239

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 · Posted: Wed Dec 21, 2011 6:53 pm

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