1

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?

2

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.

  • Thank you! The SELECT statement confirmed that my table needs to be REORG'ed. Then, I run db2 reorg table SCHEMA.TABLE. After that, the RUNSTATS succeeded. – Alexander Pozdneev Jul 24 at 9:09
  • 1
    Glad it helped, regarding REORGCHK it calculates a lot of metrics. I find the percent of overflows most useful. You can instead determine that, via - for example - SYSIBMADM.SNAPTAB. `db2 "SELECT TABSCHEMA, TABNAME FROM SYSIBMADMIN.SNAPTAB WHERE 100.0 * OVERFLOW_ACCESSES / ROWS_READ > 3". 3 is just an invented number of percent, another constant may fit your needs better. – Lennart Jul 24 at 9:29
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.