Db2 – Fixing SQL2310N Error in REORGCHK

alter-tabledb2db2-luwerrorsstatistics

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?

Best Answer

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.

Related Question