DB2 LUW – Recommended Commands for DB2 Reorg

db2db2-luw

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?

Best Answer

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

Related Solutions

Db2 – creating PKs for existing tables – altering column constraints

My expertise is on DB2 for i, not really LUW. That said, I will still offer my thoughts.

I don't think you want your ID field to have a default of zero and say GENERATED BY DEFAULT.

Since this column is giving you so much trouble and is one you added, I might be tempted to drop it, and start from scratch, if practical.

I believe the commonly recommended definition for this field would be

ALTER TABLE tablename 
  ADD COLUMN orm_id   INTEGER   NOT NULL
                                GENERATED ALWAYS AS IDENTITY
                                CONSTRAINT tablename_pk PRIMARY KEY;

But someone wiser may adjust this advice.

DB2 – Reasons for Foreign Key Error (-667)

The DB2 optimizer relies on foreign key constraints, which by default are enabled for optimization even though they may not be enforced. The optimizer may choose not to read the rows that should not exist according to the constraint.

Try disabling query optimization on the RI constraint:

ALTER TABLE xyz ALTER FOREIGN KEY fkname DISABLE QUERY OPTIMIZATION

Best Answer

Related Solutions

Db2 – creating PKs for existing tables – altering column constraints

DB2 – Reasons for Foreign Key Error (-667)

Related Question