DB2 Reorg-recommended commands

Question

Is there some full list of commands that can cause table to become reorg-pending?

I tried google it for a while, but can't find full list. My exact problem for now - can ADD FOREIGN KEY statement cause table to become reorg-pending?

Answer 1

A table is placed in the reorg pending mode if you alter its physical structure, such as add or drop a column or change the column data type or nullability.

Some operations do not place the table in reorg pending, but reorganization is required for the changes to take effect, for example, changing the table COMPRESS attribute or the inline length of a LOB column.

Adding or dropping a foreign key does not place the table in reorg pending, nor does it require reorganization. However, changing an existing foreign key from not enforced to enforced will put the table in the check pending mode, which, like reorg pending, prevents applications from accessing that table.

I don't know of a single good source to read more about reorg, apart from the DB2 manual, particularly the ALTER TABLE statement and this article:

DB2 10.5 for Linux, UNIX, and Windows: Altering tables

Answer 2

Yes the the full list of column alterations that can put your table into reorg pending (read only and can only be table scanned once one of these commits, so pretty drastic) is now listed clearly in the ALTER TABLE command, excerpted below.

The following is the full list of REORG-recommended ALTER statements that cause a version change and place the table into a REORG-pending state:

• DROP COLUMN
• ALTER COLUMN SET NOT NULL
• ALTER COLUMN DROP NOT NULL
• ALTER COLUMN SET DATA TYPE, except in the following situations:
  • Increasing the length of a VARCHAR or VARGRAPHIC column
  • Decreasing the length of a VARCHAR or VARGRAPHIC column without truncating trailing blanks from existing data, when no indexes exist on the column

Although each of these can create a new version, and after 3 versions you must reorg, in a single UOW you can modify as many columns as you like. That can also include as many of these in a single ALTER TABLE command as you like. The sum of all the changes will count as 1 new version in these cases.