Db2 REORGCHK returns the SQL2310N (Error “-668”) message

Question

I have a Db2 database on my Linux laptop. The database is not heavily loaded. I access it from time to time for the development purposes.

I removed a column:

db2 "alter table SCHEMA.TABLE drop column COLUMN"

Then, I run REORGCHK:

db2 "reorgchk update statistics on table all"

and got the following message:

Doing RUNSTATS ....

SQL2310N  The utility could not generate statistics.  Error "-668" was 
returned.

However, if I do not ask REORGCHK to update statistics, the command succeeds:

db2 "reorgchk current statistics on table all"

Further investigation shows that RUNSTATS on the altered table:

db2 "runstats on table SCHEMA.TABLE for indexes all"

fails with the same error as above (i.e., SQL2310N, -668).

I tried to increse values of same parameters (LOGFILSIZ, LOGPRIMARY, LOGSECOND, and STAT_HEAP_SZ), but that did not help.

What could be the reason for the error? How to debug it? How could I fix it?

Answer 1

I'm not sure REORGCHK UPDATE STATISTICS is the way to go after a destructive operation (drop column) against a table. I suspect that the drop statement put a Z-lock on some catalog tables which prevents STATISTICS from being updated. If you are only interested in whether you need to reorg a table due to the drop you can:

SELECT NUM_REORG_REC_ALTERS, REORG_PENDING 
FROM SYSIBMADM.ADMINTABINFO
WHERE (TABSCHEMA, TABNAME) = (..., ...)

For some reason, it's really slow (don't ask for a table that does not exist), but it's faster than reorgchk.

Unless the table is really big, I usually add a REORG command to migration scripts just in case:

REORG TABLE ...;

Do note that REORG will commit any ongoing transactions, so you should end the current uow, before REORG's and put them in a separate transaction.

Answer 2

As a side note, REORGCHK is not the right way to determine if the table needs reorganization post-ALTER. It is intended to analyze object statistics to determine if the table or its indexes can be optimized in some way. Subsequently, an attempt to scan its indexes to update statistics will necessarily fail.

REORGCHK calculates several metrics, such as the amount of unused space in the table, the number of overflow rows, similar efficiency metrics for indexes, the cluster ratio of the table etc., which you can use to decide whether reorganization might improve query performance. What it doesn't do is look at the "reorg pending" status of the table.