IBM Support


How do I determine how many "REORG-recommended" operations have been done on a table?

Question & Answer


Question

I am considering an "ALTER TABLE" operation, and I am not sure if it will place the table in a pending status. Further I am not sure if previous "ALTER TABLE" statements have been run on this table, how can I determine how many "REORG-recommended" operations have been done, and if the table is in a reorg pending state?

Answer


There may be instances where you need to know how many "REORG-recommended" operations have been done against a table, so as to not perform an additional "REORG-recommended" operation that could unexpectedly force your table in a pending state.

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.

Further details on which operations can be run after a "REORG-recommended" operation and which "ALTER TABLE" statements are considered "REORG-recommended" can be found under the Using the ALTER TABLE statement to alter columns of a table page from the Information Center.

To find out how many "REORG-recommended" operations have been done against a table, and determine if its in a reorg pending state, you can use the "SYSIBMADM.ADMINTABINFO" table:

$ db2 "SELECT TABSCHEMA, TABNAME, NUM_REORG_REC_ALTERS, REORG_PENDING FROM SYSIBMADM.ADMINTABINFO where tabname='TAB1'"
 
TABSCHEMA  TABNAME   NUM_REORG_REC_ALTERS REORG_PENDING
---------  --------- -------------------- -------------
V95FP4     TAB1      1                    Y 
 
  1 record(s) selected.

For example, an "alter table...add column" command is not a "REORG-recommended" operation, so the "NUM_REORG_REC_ALTERS" will not increment and "REORG_PENDING" will show "N" assuming no previous "REORG-recommended" operation had been run. The following illustrates this:

$ db2 "ALTER TABLE tab1 ADD col3 int NOT NULL DEFAULT 0"
DB20000I  The SQL command completed successfully.

$ db2 "SELECT TABSCHEMA, TABNAME, NUM_REORG_REC_ALTERS, REORG_PENDING FROM SYSIBMADM.ADMINTABINFO where tabname='TAB1'"
 
TABSCHEMA  TABNAME   NUM_REORG_REC_ALTERS REORG_PENDING 
---------  --------- -------------------- -------------
V95FP4     TAB1      0                    N 

Internal Use Only

PMR 86883,122,000

[{"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Business Unit":{"code":"BU001","label":"Analytics Private Cloud"},"Component":"Database Objects\/Config - Tables","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;9.1","Edition":"Enterprise Server;Personal;Workgroup Server"}]

Document Information

Modified date:
16 June 2018

UID

swg21440178