DB2 主鍵建立失效排查全紀錄：從 NULL 約束到 REORG PENDING 狀態解除

在 DB2 中為現有資料表新增主鍵時，常遇到連續錯誤：SQL0542N（欄位允許 NULL）→ 修改為 NOT NULL → SQL0668N reason code 7（REORG PENDING 狀態）。本文完整記錄排查邏輯與解決路徑。

錯誤重現場景

-- 嘗試為 EMP 表的 EMPNO 欄位新增主鍵
db2 "alter table emp add primary key (empno)"

DB21034E  The command was processed as an SQL statement...
SQL0542N  "EMPNO" cannot be a column of a primary key or unique key 
          because it can contain null values. SQLSTATE=42831

第一層阻礙：主鍵欄位不得為 NULL

DB2 嚴格要求主鍵欄位為 NOT NULL，與 Oracle/MySQL 允許先建主鍵再補約束不同。需先修改欄位屬性：

db2 "alter table emp alter column empno set not null"
DB20000I  The SQL command completed successfully.

第二層阻礙：REORG PENDING 狀態

db2 "alter table emp add primary key (empno)"

DB21034E  The command was processed as an SQL statement...
SQL0668N  Operation not allowed for reason code "7" on table "DB2INST1.EMP".
SQLSTATE=57016

錯誤代碼深度解析

db2 "? SQL0668N"

SQL0668N  Operation not allowed for reason code "" on table "".
Reason code 7: The table is in the reorg pending state. This can occur after
an ALTER TABLE statement containing a REORG-recommended Operation.
User response: Reorganize the table using the REORG TABLE command.

驗證表狀態

db2 "load query table emp"

Tablestate:
Reorg Pending

根因分析：ALTER COLUMN SET NOT NULL 觸發 REORG PENDING

在 DB2 中，修改欄位從 NULLABLE 改為 NOT NULL 屬於 REORG-recommended operation。資料庫不會立即重寫所有資料頁，而是將表標記為 REORG PENDING，禁止結構變更（如建立索引、主鍵、外鍵）直到完成重組。

解決方案：執行 REORG TABLE

db2 "reorg table emp"
DB20000I  The REORG command completed successfully.

驗證恢復正常

db2 "load query table emp"
Tablestate:
Normal

db2 "alter table emp add primary key (empno)"
DB20000I  The SQL command completed successfully.

完整標準化流程

-- 1. 檢查欄位現狀
db2 "describe table emp show detail"

-- 2. 若允許 NULL，先設定 NOT NULL
db2 "alter table emp alter column empno set not null"

-- 3. 檢查表狀態
db2 "load query table emp"

-- 4. 若為 Reorg Pending，執行重組
db2 "reorg table emp"

-- 5. 再次確認狀態為 Normal
db2 "load query table emp"

-- 6. 建立主鍵
db2 "alter table emp add primary key (empno)"

-- 7. 驗證主鍵建立成功
db2 "select tabname, constname, colname from syscat.keycoluse where tabname='EMP'"

進階情境與注意事項

其他觸發 REORG PENDING 的操作

• ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE
• ALTER TABLE ... DROP COLUMN
• ALTER TABLE ... ADD COLUMN ... NOT NULL（無預設值）
• ALTER TABLE ... SET COMPRESS YES/NO
• REDISTRIBUTE DATABASE PARTITION GROUP

良好習慣：結構變更後例行檢查 load query table，若為 Reorg Pending 則排程重組。