Josh-D. S. Davis

Xaminmo / Omnimax / Max Omni / Mad Scientist / Midnight Shadow / Radiation Master

Previous Entry Share Next Entry
DB2 10.5.0.1 negative colcard
Josh 201604 KWP
joshdavis
This is a defect in DB2 10.5 FP1
The defect does not exist in DB2 9.7 FP6
This problem affects TSM 7.1.0.0 customers with billions of extents (over 30TB deduplicatedmay release late enough to include DB2 10.5 FP3a,

In TSM Server 7.1.0.0 on AIX (unk if limited to AIX),
when RUNSTATS parses BF_AGGREGATED_BITFILES,
and there are more than maxint unique values for BFID,
then COLCARD may become negative.

A negative column cardinality will the index for queries against it,
which will lead to slowdowns and lock escalations within TSM.
This will present as a growing dedupdelete queue, slow expire, slow BACKUP STGPOOL, and slow client backups.

This is not exactly maxint related, as maxint - colcard was higher than the number of columns by about 20%.

You can check for this by logging in to your instance user, and running:

db2 connect to tsmdb1
db2 set schema tsmdb1
db2 'select TABNAME,COLNAME,COLCARD from SYSSTAT.COLUMNS where COLCARD<-1'


The output should say "0 record(s) selected."
If it lists any negative values for tables, then that table's index will becompromised.

There is no fix for TSM Server 7.1, as no patches are available.
TSM 7.1.1 will release with DB2 10.5 FP3, which will not include a fix for this problem.
As of 2014-08-01, the problem has not been isolated yet.

The workaround is to update column cardinality to a reasonable value.
It doesn't need to be exact. An example command might be:

db2 connect to tsmdb1
db2 set schema tsmdb1
db2 "UPDATE SYSSTAT.COLUMNS SET COLCARD=3300000000 WHERE COLNAME='BFID' AND TABNAME='BF_AGGREGATED_BITFILES' AND TABSCHEMA='TSMDB1'"


There is no APAR for this, and no hits on Google for "DB2 'negative column cardinality'".
This seems slightly related to: http://www-01.ibm.com/support/docview.wss?uid=swg1IC99408

NOTE: DO NOT INSTALL DB2 FIXPACK SEPARATELY. The TSM bundled DB2 is very slightly different. Standard DB2 fixpacks are not supported. If you decide to do this, you may find command or schema problems. If it works, then you may not be able to upgrade TSM afterward without a BACKUP DB, uninstall, reinstall, RESTORE DB -- at best.

If you have a large dedupe database, your options include:
* Stay at TSM 6.x
* Monitor for negative column cardinality
* Wait for an APAR and efix from IBM.
* Wait for TSM 7.1.1.1 or TSM 7.2.0 in 2015 (or whatever versions will contain fixes).

http://omnitech.net/reference/2014/08/04/db2-10-5-0-1-negative-colcard/

?

Log in

No account? Create an account