Search code examples
db2reorganize

db2 luw reorg table

My doubt concerns how to determine exactly when it is necessary to reorg a table.

My application executes the following select:

SELECT TABNAME FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA = 'DB2ADMIN' AND REORG_PENDING = 'Y'

Right now I have found the following article of IBM:

IBM documentation

They suggest to use:

SELECT TABSCHEMA, TABNAME, NUM_REORG_REC_ALTERS, REORG_PENDING FROM SYSIBMADM.ADMINTABINFO where tabname='TAB1'

So from 'SYSIBMADM.ADMINTABINFO' they consider also the column 'NUM_REORG_REC_ALTERS'

In the articale is reported:

Certain "ALTER TABLE" statements are considered "REORG-recommended" operations. After 3 of such operations your table will be forced into a reorg pending state. Limited access to a table that has had "REORG-recommended" operations is permitted, thus, you may need to know how many have been done so far. After three such operations no access to the table is allowed until a REORG has been performed.

I've found also the documentation of column NUM_REORG_REC_ALTERS

However, it is still not clear to me whether to have a list of the tables to be reorganised is sufficient:

SELECT TABNAME FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA = 'DB2ADMIN' AND REORG_PENDING = 'Y'

Or is preferable:

SELECT TABNAME FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA = 'DB2ADMIN' AND (REORG_PENDING = 'Y' OR NUM_REORG_REC_ALTERS > 0)

What is your opinion on this?

Thank you.

Solution

  • The necessity of a table reorg must be dictated by this table access pattern.
    REORG_PENDING = 'Y' AND NUM_REORG_REC_ALTERS < 3 means that your table is readonly accessible + a number of other statements allowed on this table which are mentioned in the doc.
    REORG_PENDING = 'Y' AND NUM_REORG_REC_ALTERS = 3 means that your table is readonly accessible, and no other alters are allowed on this table.

    You must decide on your own, if you need to take any action with such a particular table in its state depending on this table access pattern.