Gaps in sequence numbers

Mark Fleming

Gaps in sequence numbers
Okay, let's get those creative juices flowing. I'm about out of ideas on this one. First, the usual: z/OS 1.04 platform, DB2 V7.

Summary

I'm losing 35% of my sequence values (lost 830K out of 2.3M just two days ago). Any ideas why? Or what I can look for?

Background

I have several (20+) tables set up for assigning sequence numbers. They all have the same structure, two columns: an IDENTITY column and a timestamp column. A stored procedure is used to insert a row, which causes a new sequence to be assigned; it uses the IDENTITY_VAL_LOCAL() function to retrieve the value, and then the row is deleted. The tables are all basically empty, they are a means to an end only: to get sequence values. The tables are set up with row-level locking, and most of the tables are in the same table space. I intend to separate them each into their own table space later (that's a different story) to reduce the impact of an outage on the others. Each table was originally defined with a starting value of 1, an increment of 1, and a cache of 20.

We have a robust mix of applications using this procedure with many batch, online, and DB2Connect users/applications. There are usually about 20 WLM environments running, any of which have applications that may be using this procedure. Each WLM is set up with 20 TCBs. The DB2 is a data sharing member with 3 members set up. Let's see, that should do it for the environment background.

Problem

While I know that IDENTITY columns are guaranteed to be unique (GENERATED ALWAYS), they aren't guaranteed to be consecutive. For the most part they are, but gaps can occur. I accept this, but... I'm losing about 35% of my values. Read on...

I'm monitoring the activity for one of the tables, because it is hit fairly hard. Let's call it table A. There are about 2.3 million inserts to this table each day. I used Platinum Log Analyzer to produce a report of all the activity from the DB2 log (only insert and deletes, no updates occur to table A) for a 24-hour period. I did this on each of the 3 data sharing members and get the same results. Using SAS, I analyzed the gaps. When the difference between the last sequence number and the current number is > 1, it's a gap... and yes, the data is sorted before I calculate gaps. I'm seeing varying size gaps. Most frequently, they are in amounts equivalent to a multiple of the cache size, with a heavy concentration on 20, then 40. Sometimes 60, 80, or more, but they are less frequent. A smaller gap size (say < 20) occurs infrequently. The overall amount of sequences 'lost' are substantial, amounting to just under 830,000. That's nearly 35%.

So far I am unable to account for why we're losing so much. I do not see a ton of deadlock/timeout activity that may have prompted applications to rollback their work (and hence discard any sequences they used during that UOW). The WLMs often stay up for days, the volume fluctuates, but the point being that there aren't tons of SPASes spawned, nor are there frequent WLM refreshes done. Why would this matter, you ask? Because lets say each of the 20 WLMs running were assigned a separate cache of 20 values. Worst case is I'd lose 20 x 20, or 400 values. The WLM's are NOT refreshed 2075 times a day.

Here's another weird twist. The sequences from this table (via the stored procedure) are inserted to another table as it's primary key. I analyzed the [sequence] values on this table (let's call it table B) and found that many of the values that fall into a 'gap' from the Log Analyzer analysis of table A are actually on table B. How can that be? How can it show up on table B when the DB2 log doesn't show an insert to table A, which is the only way that the value is assigned via the identity column? The applications are NOT assigning values outside the process described above. I am convinced of this. Any ideas?

Mark Fleming
Data Base Administration
847-667-5690



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: Gaps in sequence numbers
(in response to Mark Fleming)
Mark

Well, I don't know the reason for that, maybe some ideas. First of all, the
cached values are member wide, not for each WLM adress space. So each DB2
datasharing member caches 20 values.

Maybe data was loaded into table A and then mass deleted. The LOAD-utility
updates the MAXASSIGNVALUE with a higher value than the INCREMENT-value. I
don't know the algorithm behind that. Did you looked at SYSCOPY?

That you saw values in table B, for which you don't see any entries in the
log, I only have one explanation: a bug in the log analyser tool or in your
SAS analysis.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark Fleming

Re: Gaps in sequence numbers
(in response to Walter Janißen)
Walter,

Thanks for the thoughts. No LOADs are ever run against these tables. I know the LOAD utility uses a cache size of 1000. I'm not seeing gaps like that, but tons of cache size and smaller gaps. Another thought is that in many cases (not all) the UOW has MQ Series activity. That is, ID's are assigned and then sent into a message queue. Kind of late breaking news, I admit. It's possible that the original UOW is being rolled back, for whatever reason, but the message in the queue is being applied since that is asynchronous. I know almost nothing about MQ Series and message queues. I suspect that a DB2 COMMIT will not necessarily do anything for the queue, likewise, a ROLLBACK may not affect the queue either. Let's assume for the moment, that this line a reasoning is accurate, that MQ processing is contributing to the problem. Is there some setting or some configuration or something that I can check into to see if there's a way to coordinate rollback activity between the queue and DB2. I also must entertain the idea that even if there is a rollback in DB2, that a rollback of the MQ data may not be desired.

Mark Fleming
Data Base Administration
847-667-5690


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Walter Janißen
Sent: Monday, December 13, 2004 2:51 AM
To: [login to unmask email]
Subject: Re: Gaps in sequence numbers


Mark

Well, I don't know the reason for that, maybe some ideas. First of all, the
cached values are member wide, not for each WLM adress space. So each DB2
datasharing member caches 20 values.

Maybe data was loaded into table A and then mass deleted. The LOAD-utility
updates the MAXASSIGNVALUE with a higher value than the INCREMENT-value. I
don't know the algorithm behind that. Did you looked at SYSCOPY?

That you saw values in table B, for which you don't see any entries in the
log, I only have one explanation: a bug in the log analyser tool or in your
SAS analysis.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm