Getting surrogate keys back from a db2 sequence
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 141
- Joined: Tue Mar 16, 2004 8:22 am
- Location: HSBC - UK and India
- Contact:
Getting surrogate keys back from a db2 sequence
I've got a job which uses a lookup to a db2 stage. The db2 stage is set as a sparse lookup and uses the user defined sql
SELECT (NEXTVAL for SEQUENCE1) as SURROGATEKEY
FROM SYSIBM.SYSDUMMY1
I've outputed the results to a file and found that I get the same value for the surrogate key on all the records on a particular node going into the lookup.
There are 3 nodes on database so I only get 3 different values returned over all the records going into the lookup.
How do I set this up so I get a unique value for each record?
SELECT (NEXTVAL for SEQUENCE1) as SURROGATEKEY
FROM SYSIBM.SYSDUMMY1
I've outputed the results to a file and found that I get the same value for the surrogate key on all the records on a particular node going into the lookup.
There are 3 nodes on database so I only get 3 different values returned over all the records going into the lookup.
How do I set this up so I get a unique value for each record?
Why not pull a single value with a lookup dummy field, join it, use a surrogate key generator stage to generate surrogate key, and do the math (surrkey + generated_key).
This will be a MUCH more performant solution than what you are doing originally, and even far more so than what you are doing now.
This will be a MUCH more performant solution than what you are doing originally, and even far more so than what you are doing now.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
WHERE ORCHESTRATE.INPUTFIELD > -1
Because you're working on a lookup, you have to have a WHERE clause, one that will always return a row. Elsewhere on the Forum (perhaps you found it in a search) I posted a similar solution for Oracle, from memory WHERE ROWNUM = :1 and provided 1 as the reference key expression.
Because you're working on a lookup, you have to have a WHERE clause, one that will always return a row. Elsewhere on the Forum (perhaps you found it in a search) I posted a similar solution for Oracle, from memory WHERE ROWNUM = :1 and provided 1 as the reference key expression.
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.
Hi HSBCDev,HSBCdev wrote:There could be other jobs creating new surrogate keys on the same table at the same time. I presume the solution you suggest relies on this being the only job creating the key.
I have same kind of requirement and i need the sequence number to write to a file so that there are other processes using this number down the line. I created container as you said but still it is creating the same number for all the row. I am using Oracle as a database.
Could you please share how did you achive this?
Any help from other is appreciated!
Thanks
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
I don't like this design as the sparse lookup is a very expensive overhead on your job. You've got to ask what advantage you are getting from using a DB2 sequence when you have to retrieve it for every row. I think you would be better off generating keys within your job using a parallel counter, retrieve the maximum value from the DB2 sequence and pass it into your job as a parameter, update this sequence after you have loaded all rows.
We currently retrieve values from a DB2 sequence as we insert rows into tables by putting the NEXTVAL into the insert statement.
We currently retrieve values from a DB2 sequence as we insert rows into tables by putting the NEXTVAL into the insert statement.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Hi,vmcburney wrote:I don't like this design as the sparse lookup is a very expensive overhead on your job. You've got to ask what advantage you are getting from using a DB2 sequence when you have to retrieve it for every row. I think you would be better off generating keys within your job using a parallel counter, retrieve the maximum value from the DB2 sequence and pass it into your job as a parameter, update this sequence after you have loaded all rows.
We currently retrieve values from a DB2 sequence as we insert rows into tables by putting the NEXTVAL into the insert statement.
as the same table been used by other modules that is why we planned to use sequence from Oracle database.
but, could you please eloborate your last statement - "We currently retrieve values from a DB2 sequence as we insert rows into tables by putting the NEXTVAL into the insert statement" - how do you get the the sequence number while inserting, i am also expecting the same.
I need to write sequence number along with other fields to a seq file to use as input in other jobs.
your help is appreciated.
Thanks,
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi Ray,ray.wurlod wrote:When using NEXTVAL in the INSERT statement you don't get the value back. That's the whole point. It's faster.
but i need the latest Sequence number to write out to a file, can i use CURRVAL after the NEXTVAL (Insert statement) - in next stage, can it give the recently inserted value? please suggest me is it best way to do?
Thanks
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Then you can't use NEXTVAL in the INSERT statement, and need to use some other mechanism.
CURRVAL is not available within the job, which is probably (a) using NEXTVAL on all partitions, (b) implementing row buffering, (c) not committing every row.
Probably the most efficient mechanism for your particular requirement is to lock the table, generate the keys within the job, and reset the sequence to the new "next value" when done before releasing the table-level lock.
Or you can use NEXTVAL in the INSERT statement provided that you have some other means to identify the rows (such as run batch identifier and/or timestamp) so that you can retrieve those rows from target for ongoing processing.
CURRVAL is not available within the job, which is probably (a) using NEXTVAL on all partitions, (b) implementing row buffering, (c) not committing every row.
Probably the most efficient mechanism for your particular requirement is to lock the table, generate the keys within the job, and reset the sequence to the new "next value" when done before releasing the table-level lock.
Or you can use NEXTVAL in the INSERT statement provided that you have some other means to identify the rows (such as run batch identifier and/or timestamp) so that you can retrieve those rows from target for ongoing processing.
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.