DB2 sequences and transactions
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
DB2 sequences and transactions
We have DB2 sequences to control allocation of surrogate keys, but we need tens of thousands of keys at a time so we use ALTER SEQUENCE to increase the sequence value. It turns out that the NEXTVAL and ALTER commands cannot be wrapped in a transaction since the ALTER command is DDL. I would have expected that the chance of a clash (where process 1 requests NEXTVAL, process 2 requests NEXTVAL, p1 issues ALTER SEQUENCE and then p2 issues ALTER SEQUENCE) should be vanishingly small but when the database is heavily burdened p1 gets stuck waiting for the ALTER and then p2 catches up so what should be highly unlikely is raised to an almost certainty.
We are planning on fixing this by explicitly locking a table that exists only for the purpose of acting as a semaphore to prevent two such processes from getting into contention in this manner.
Can anyone think of an alternative mechanism for this? It needs to be robust in that we don't want an aborted job to leave the semaphore "on", the table lock gives us this as the database will clear the lock when the connection is terminated.
We are planning on fixing this by explicitly locking a table that exists only for the purpose of acting as a semaphore to prevent two such processes from getting into contention in this manner.
Can anyone think of an alternative mechanism for this? It needs to be robust in that we don't want an aborted job to leave the semaphore "on", the table lock gives us this as the database will clear the lock when the connection is terminated.
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
Sorry, I don't know squat about DB2 but don't sequences there support the caching concept that others like Oracle do? Specifically, if a sequence will need to be leveraged in large chunks (like your tens of thousands at a time) then you set a high cache value and the first NEXTVAL call reserves that many. A call to the same sequence from another process would get the NEXTVAL past the reserved cache and establish its own cache.
Am I understanding that you have to manage that manually with your ALTERs?
Am I understanding that you have to manage that manually with your ALTERs?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
We thought about this but the number of required values could be anywhere from just a few to over 250,000 so this wouldn't really be practical. We'd run out of the ~2,100,000,000 values before the prefix increments at the start of each month.chulett wrote:Sorry, I don't know squat about DB2 but don't sequences there support the caching concept that others like Oracle do? Specifically, if a sequence will need to be leveraged in large chunks (like your tens of thousands at a time) then you set a high cache value and the first NEXTVAL call reserves that many.
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
Our surrogate keys are BIGINTs, with the first 6 digits being YYYYMM, then a 4-digit supplier key, then a sequence generated by the database taking up the remaining 9 digits so no, we can't increase the size of the sequence value.qt_ky wrote:DB2 BIGINT range is -9,233,372,036,854,775,808 to 9,223,372,036,854,775,807.
Last edited by PhilHibbs on Fri Feb 24, 2012 10:54 am, edited 2 times in total.
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
I would guess in DB2 like Oracle you can create a trigger on insert that populates an ID using NEXTVAL in the sequence. Then it can become transparent to DataStage. Not sure how well it would work in parallel. Have you tried anything like that?
Choose a job you love, and you will never have to work a day in your life. - Confucius
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
We need the surrogate keys to be generated before we hit the database.qt_ky wrote:I would guess in DB2 like Oracle you can create a trigger on insert that populates an ID using NEXTVAL in the sequence. Then it can become transparent to DataStage. Not sure how well it would work in parallel. Have you tried anything like that?
marking as "workaround" because we implemented the table lock semaphore.
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
Ooh, I just had a terrible thought! Our application will fail in the year 9224! I better raise that on the risk register.PhilHibbs wrote:Our surrogate keys are BIGINTs, with the first 6 digits being YYYYMM, then a 4-digit supplier key, then a sequence generated by the database taking up the remaining 9 digits so no, we can't increase the size of the sequence value.qt_ky wrote:DB2 BIGINT range is -9,233,372,036,854,775,808 to 9,223,372,036,854,775,807.
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
Good point!PhilHibbs wrote:Our application will fail in the year 9224! I better raise that on the risk register.
You know, you're wasting a lot of digits by limiting the first 6 to YYYYMM. More efficient is MMMMMM for month number starting at year 0 going up through year 83333.
Choose a job you love, and you will never have to work a day in your life. - Confucius
I do the same at my current site. I use something like this
The 'X' becomes my dummy key to do a lookup on and get the key. This keeps the sequence object in sync as well.
Now since this is a sparse lookup it will become really slow for massive amounts of incoming data. For that I stick the same " NEXT VALUE FOR sequenceowner.sequenceobjectname" in the derivation of the column from the db2 stage itself and that takes care of it.
Code: Select all
select NEXT VALUE FOR sequenceowner.sequenceobjectname, 'X' from sysibm.sysdummy1
Now since this is a sparse lookup it will become really slow for massive amounts of incoming data. For that I stick the same " NEXT VALUE FOR sequenceowner.sequenceobjectname" in the derivation of the column from the db2 stage itself and that takes care of it.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.