Get next value from DB Seq
Moderators: chulett, rschirm, roy
Get next value from DB Seq
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
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
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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 ""}
}
}
Re: Get next value from DB Seq
Hi,
Change the partition type to "Entire" for Reference Data
and check it. You will get correct, i think.
Prasad GV
Change the partition type to "Entire" for Reference Data
and check it. You will get correct, i think.
Prasad GV
Re: Get next value from DB Seq
Hi,
Change the partition type to "Entire" for Reference Data
and check it. You will get correct, i think.
Prasad GV
Change the partition type to "Entire" for Reference Data
and check it. You will get correct, i think.
Prasad GV
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
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:
But then where is the SQL clause? And what is the key column used - ID1? Then add a where clause as:
Make sure the column name is same on stream link and Ref link for ID1.
Let us know how it goes.
Code: Select all
SELECT 1 as ID1, NEXTVAL FOR SEQ_NAME as NEXTVALUE FROM sysibm.sysdummy1
Code: Select all
SELECT 1 as ID1, NEXTVAL FOR SEQ_NAME as NEXTVALUE FROM sysibm.sysdummy1 WHERE 1 = $ORCHESTRATE.ID1
Let us know how it goes.
Regards,
S. Kirtikumar.
S. Kirtikumar.
it's something like:
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?
Code: Select all
SELECT 1 as ID1, NEXTVAL FOR SEQ_NAME as NEXTVALUE FROM sysibm.sysdummy1 WHERE ID1=ORCHESTRATE.ID1
Are you fetching NEXTVAL to output?
And also are you sure, in both input and reference the ID1 has 1 as always?
pandeeswaran
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
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