Hi, I've been facing an issue in db2 for some time that has me very confused. We have a large-ish table of around 1.2 million records. Inserts to this table are sometimes quick, on the order of 10ms, and sometimes extremely slow, on the order of 2-5 full seconds. The table has a primary key, several foreign keys, one index on one of the foreign keys, and one check values constraint.

When queries are slow, they will be slow for quite some time. The server itself is mainly idle, certainly not heavily loaded. Other queries do not seem to show such painfully slow behaviour. We're using DB2 v9.5.

I'm at a loss as to what could be causing the issue.

From these descriptions, the first isuue that came in my mind was "index splitting".
But, the difference was too big(sometimes extremely slow, on the order of 2-5 full seconds.).
So, I'm not sure.

It may be worth to try to ajust PCTFREE(and LEVEL 2 PCTFREE in some environment)
and to see the number of inserted rows between periods of getting slow.

Those points may also be analyzed, if index splitting was in doubt.
- Key distribution of inserted rows in the indexes.
- Number of index leaf and higher level pages.
- Muximum and average index entries per page.

I would expect index splitting to incur a cost only on one query, though, whereas my situation is more like slow for one day, quick for a few days, slow for another couple of days.

Once index splitting occured, it is highly possible that index splitting would continue.

Consider a case with PCTFREE 0.
If roandom keys were inserted, most keys would be inserted into different leaf pages.
So, almost every inserts would cause index splitting.
Then after enough keys inserted, almost every index pages would be splittied,
The splitted pages have enough spaces for new keys to be inserted.
Later inserts wouldn't cause index splitting.

But, if more keys were inserted, splittied pages would be gradualy filled up.
Then repeat again first scenario.

PCTFREE is listed as -1, which apparently causes the default to apply. I'm not sure what the default is, though.

I've stepped up the reorg indexes schedule to run every night instead of weekly. We'll see if that helps at all.

Does this table have a clustering index?


Here is how db2 looks for a page during INSERT:
IBM DB2 9.7 for Linux, UNIX and Windows Information Center

It doesn't have a clustering index, no. It does have an ORGANIZE BY clause, on a column for which there are 3 potential values.

That explaines data page search algorithm, not for the index pages.
Also, PCTFREE menthioned in the article was for TABLE, not for INDEX.

Although, some options for table may affect the performance of insert and/or load,
it is not clear the relationship with the performance getting worse periodicaly.

when i analyse ur query...i found that...it might that sometimes more users are connected to ur server or accessing the same table so that it will produce more i/o operations due to which it slows down....and another reason is ofcourse index splitting........it is recommended to make more indexes to improve query performance

I didn't post a query, so I would certainly be impressed if you'd managed to analyse something. It's almost like you're just making things up.