DB2 sequences and transactions

PhilHibbs · Post by **PhilHibbs** » Sun Jan 15, 2012 4:18 am

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.

chulett · Post by **chulett** » Sun Jan 15, 2012 8:56 am

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?

PhilHibbs · Post by **PhilHibbs** » Sun Jan 15, 2012 12:31 pm

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.

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.

qt_ky · Post by **qt_ky** » Sun Jan 15, 2012 4:05 pm

Would it help to use a BIGINT (64-bit) instead of an INTEGER (32-bit)?

DB2 BIGINT range is -9,233,372,036,854,775,808 to 9,223,372,036,854,775,807.

PhilHibbs · Post by **PhilHibbs** » Tue Jan 17, 2012 3:26 am

qt_ky wrote:DB2 BIGINT range is -9,233,372,036,854,775,808 to 9,223,372,036,854,775,807.

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 · Post by **qt_ky** » Sun Feb 05, 2012 1:36 pm

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?

PhilHibbs · Post by **PhilHibbs** » Fri Feb 24, 2012 10:50 am

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?

We need the surrogate keys to be generated before we hit the database.

marking as "workaround" because we implemented the table lock semaphore.

PhilHibbs · Post by **PhilHibbs** » Fri Feb 24, 2012 10:56 am

PhilHibbs wrote:
qt_ky wrote:DB2 BIGINT range is -9,233,372,036,854,775,808 to 9,223,372,036,854,775,807.
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.

Ooh, I just had a terrible thought! Our application will fail in the year 9224! I better raise that on the risk register.

qt_ky · Post by **qt_ky** » Fri Feb 24, 2012 11:07 am

PhilHibbs wrote:Our application will fail in the year 9224! I better raise that on the risk register.

Good point!

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.

DSguru2B · Post by **DSguru2B** » Fri Feb 24, 2012 11:21 am

I do the same at my current site. I use something like this

Code: Select all


select NEXT VALUE FOR sequenceowner.sequenceobjectname, 'X' from sysibm.sysdummy1

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.