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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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:
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.