[DB2] Alter table limitations, DB21034E

หลายคนอาจจะงง ว่าทำไมผมถึงได้เขียน Blog ตอนนี้นะครับ  ลองมาดู Script ตัวอย่างของผมนะครับ

[sql]
ALTER TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL ALTER COLUMN ACCOUNTNAME SET DATA TYPE VARCHAR(300); --จุดที่ (1)
ALTER TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL ALTER COLUMN REMARK SET DATA TYPE VARCHAR(300); --จุดที่ (2)
ALTER TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL ADD COMPANYACCOUNT_ID INTEGER;
ALTER TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL ADD CONTACT_COMPANY_ID INTEGER;
--....
ALTER TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL ADD CUSTODIAN_ID INTEGER;
ALTER TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL ADD BRANCHNAME CHARACTER(100);
ALTER TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL DROP BANK_ID; --จุดที่ (3)
ALTER TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL DROP BANKBRANCH_ID; --จุดที่ (4)
CALL SYSPROC.ADMIN_CMD( 'REORG TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL' );
[/sql]

ถ้าหากนำ Script ด้านบนนี้ไปรันบน DB2 ที่มี Version ต่ำกว่า 9.7 พบว่า Script ตัวนี้สามารถรันได้ โดยไม่มีปัญหาครับ แต่ถ้านำ Script นี้ไปรันบน DB2 ที่มี Version สูงกว่านี้ เช่น 10.5 พบว่ามี Error Message ในจุดที่ (4) ดังนี้ครับ

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned: SQL20054N  The table "INVEST.COMPANYACCOUNT_SECURITY_DETAIL" is in an invalid state for the operation. Reason code="23".  SQLSTATE=55019

ตอนนี้ลองวกกลับมาหาที่สาเหตุบ้าง ถ้าเราลองไปอ่านใน Document ของทาง DB2 นะครับ พบว่ามีข้อกำหนดในการใช้คำสั่ง ALTER หรือ DROP สามารถทำได้สูงสุด เพียง 3 ครั้ง(3 Unit of work) ต่อการทำงานกับ Table ในแต่ละครั้งครับ และตัว DBMS ปรับ Flag ของ Table เพื่อบังคับให้ต้องทำการ REORG TABLE อีกครั้งครับ ส่วนตัวผมเข้าใจว่าระหว่างการเปลี่ยนแปลง ALTER Table เพื่อแก้ Data Type หรือ ขนาดของ Field รวมถึงการ DROP COLUMN ตัว DBMS ต้อง Allocate พื้นที่เข้ามาเป็น Temp ในการจัดการครับ (ถ้าสังเกตุใน SQL Server DBMS ทำการย้ายข้อมูลไว้ใน TMP >> DROP TABLE เดิม >> สร้าง Table ใหม่ >> เอาข้อมูลที่สำรองไว้ใน TMP ย้ายมาใส่ครับ) ด้านล่างนี้เป็นข้อความบางส่วนที่ผมยกมาจาก Document ของทาง IBM ครับ โดยเจ้า unit of work มัน คือ คำสั่งที่เราเข้าไปจัดการสิ่งที่ต่างๆใน Table ครับ

Multiple ALTER TABLE operations within a single unit of work
   Certain ALTER TABLE operations, like dropping a column, altering a column type, or altering the nullability property of a column may put the table into a reorg pending state. In this state, many types of queries cannot be run; you must perform a table reorganization before the table becomes available for some types of queries. However, even with the table in a reorg pending state, you can still perform multiple ALTER TABLE operations before doing a reorg. 

   Beginning with DB2® Version 9.7, you can perform an unlimited number of ALTER TABLE statements within a single unit of work. However, after three units of work have been performed that include such operations, a REORG TABLE command must be run.

หากผมต้องการใช้ Script นี้สามารถใช้กับ DB2 ได้ตั้งแต่ Version 8.xx ไปจนถึงปัจจุบัน ผมต้องแทรกคำสั่ง REORG TABLE แทรกเข้าไปทุกครั้ง เมื่อการแก้ ALTER หรือ DROP Column ครบ 3 ครั้ง(3 Unit of work) ดังนี้ครับ

[sql]
ALTER TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL ALTER COLUMN ACCOUNTNAME SET DATA TYPE VARCHAR(300); --จุดที่ (1)
ALTER TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL ALTER COLUMN REMARK SET DATA TYPE VARCHAR(300); --จุดที่ (2)
ALTER TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL ADD COMPANYACCOUNT_ID INTEGER;
ALTER TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL ADD CONTACT_COMPANY_ID INTEGER;
--....
ALTER TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL ADD CUSTODIAN_ID INTEGER;
ALTER TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL ADD BRANCHNAME CHARACTER(100);
ALTER TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL DROP BANK_ID; --จุดที่ (3)
--เพิ่มคำสั่ง เพื่อ REORG TABLE หลังจากมีการแก้ไข Table ไป 3 ครั้ง
CALL SYSPROC.ADMIN_CMD( 'REORG TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL' );
--เพิ่มคำสั่ง เพื่อ REORG TABLE หลังจากมีการแก้ไข Table ไป 3 ครั้ง
ALTER TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL DROP BANKBRANCH_ID; --จุดที่ (4)
CALL SYSPROC.ADMIN_CMD( 'REORG TABLE INVEST.COMPANYACCOUNT_SECURITY_DETAIL' );
[/sql]

Reference:

• Multiple ALTER TABLE operations within a single unit of work
• [DB2] CALL SYSPROC.ADMIN_CMD( ‘REORG TABLE xxxx’ ) ?