I have a DB2 v72 database that is fed by IBM's Tivoli monitoring application. There are multiple tables in this db. One table - endpoints - records each endpoint identified. Each endpoint is assigned an EID. This field is an integer and is set to increment sequentially (identity=yes). The EID is the key that links to other tables.

The first 200 endpoints have a sequential EID that starts from 1, then there is a gap of about 400. We then have sequential numbers up until around 1800. At this point, it has become much more sporadic where maybe a few numbers are actually sequential before seeing another gap. Some gaps are huge. My endpoint table contains 2025 records but the EID number is above 548,000.

At this point, I believe this may be causing network issues or the result of network issues but am unfamiliar with the logfiles and do not know how to display them.

How can I start to identify what is causing this?

If I was to hazard a guess, you have defined a cache value on the identiry column. Every time the database is stopped, either manually or automatically when no one is connected, the cache is lost. Then when the DB is started again the new start value is the last value that was cached, thus making your gaps.

If you want to eliminate the gaps from occurring this way, remove the cache for the identity column.

Andy

Would be nice if I did that, but as far as I can tell, nothing has been changed in the table. It was created by the Tivoli install process. I don't know how to define a cache value on an identity column and the original gap (+400) occurred before anyone even opened the database in DB2 (Control Center). I had attached to it via Microsoft Access up until then. Can't alter anything that way.

I try to schedule a nightly backup but it fails, indicating the db is always in use so as far as I can tell, it's never stopped unless the server is rebooted.

I am sorry I cannot be of further help. The cache issue is the only thing I have seen to cause the gaps. You can use db2look to get the definition of the table. You can also look in the catalog tables for the cache value (I do not know where it is in V7.x).

Andy

What parameters would I use with db2look and what would I look for to identify the cache issue?

db2look -d dbname -z <schema name> -t <table name> -x -e

The Identity stuff should be obvious.

Andy