DSXchange

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.

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?

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.

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.

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.

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?

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 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.

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.

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.