db2修改表名 db2 在修改表名的時候，表上不能有外鍵約束，不能被視圖引用。。。。。。。。。 --例子 RENAME TABLE TPA_AVMACCOUNTTO TCASH_AVMACCOUNT ; RENAME TABLE TPA_BOMACCOUNT TO TCASH_BOMACCOUNT ; RENAME TABLE TPA_CSCTICKET TO TCASH_CSCTICKET ; RENAME TABLE TPA_HEADINFO TO TCASH_HEADINFO ; --db2 報了一大堆錯誤（剛開始有點怕怕的） 在發出 RENAME 語句之前，刪除依賴於此表的視圖、具體化查詢表、觸發器、SQL 函數、SQL 方法、檢查約束、引用約束或 XSR 對象。對於依賴於此表的視圖或具 體化查詢表，請查詢 SYSCAT.VIEWDEP 並使表與 BSCHEMA 和 BNAME 列匹配。對 於依賴於此表的觸發器，請查詢 SYSCAT.TRIGDEP 並使表與 BSCHEMA 和 BNAME 列匹配。對於 SQL 函數或 SQL 方法，請查詢 SYSCAT.FUNCDEP 並使表與 BSCHEMA 和 BNAME 列匹配。對於表的檢查約束，請查詢 SYSCAT.CHECKS 並使表與 TABSCHEMA 和 TABBNAME 列匹配。對於依賴於此表的引用約束，請查詢 SYSCAT.REFERENCES 並使表與 TABSCHEMA 和 TABNAME 列或者 REFTABSCHEMA 和 REFTABNAME 列匹配。對於為了進行將此表作為目標的分解而啟用的 XSR 對象， 請查詢 SYSCAT.XSROBJECTDEP 並使表與 BSCHEMA 和 BNAME 列匹配。 --看來限制條件真多，根據錯誤提示，查看對象的依賴性 select * from SYSCAT.VIEWDEP where bschema='L_SZ_V16' AND BNAME IN('TPA_AVMACCOUNT','TPA_BOMACCOUNT','TPA_CSCTICKET','TPA_HEADINFO'); select * from SYSCAT.TRIGDEP where bschema='L_SZ_V16' AND BNAME IN('TPA_AVMACCOUNT','TPA_BOMACCOUNT','TPA_CSCTICKET','TPA_HEADINFO'); select * from SYSCAT.FUNCDEP where bschema='L_SZ_V16' AND BNAME IN('TPA_AVMACCOUNT','TPA_BOMACCOUNT','TPA_CSCTICKET','TPA_HEADINFO'); SELECT * FROM SYSCAT.CHECKS WHERE TABSCHEMA='L_SZ_V16' AND TABNAME IN('TPA_AVMACCOUNT','TPA_BOMACCOUNT','TPA_CSCTICKET','TPA_HEADINFO'); SELECT * FROM SYSCAT.REFERENCES WHERE TABSCHEMA='L_SZ_V16' AND TABNAME IN('TPA_AVMACCOUNT','TPA_BOMACCOUNT','TPA_CSCTICKET','TPA_HEADINFO'); SELECT * FROM SYSCAT.REFERENCES WHERE REFTABSCHEMA='L_SZ_V16' AND REFTABNAME IN('TPA_AVMACCOUNT','TPA_BOMACCOUNT','TPA_CSCTICKET','TPA_HEADINFO'); SELECT * FROM SYSCAT.XSROBJECTDEP WHERE bschema='L_SZ_V16' AND BNAME IN('TPA_AVMACCOUNT','TPA_BOMACCOUNT','TPA_CSCTICKET','TPA_HEADINFO'); 查看後，發現表上有外鍵約束，其他的都沒有 --執行刪除外鍵的sql ALTER TABLE TPA_AVMACCOUNT DROP FOREIGN KEY FK57641F8FB2FBC862; ALTER TABLE TPA_CSCTICKET DROP FOREIGN KEY FK3505A84597A0F542; COMMIT; RENAME TABLE TPA_AVMACCOUNT TO TCASH_AVMACCOUNT ; RENAME TABLE TPA_BOMACCOUNT TO TCASH_BOMACCOUNT ; RENAME TABLE TPA_CSCTICKET TO TCASH_CSCTICKET ; RENAME TABLE TPA_HEADINFO TO TCASH_HEADINFO ; COMMIT; --執行創建外鍵的sql，表名TPA_開頭的都修改為 TCASH開頭，然後執行。 ALTER TABLE TCASH_CSCTICKET ADD CONSTRAINT FK3505A84597A0F542 FOREIGN KEY (BOMACCOUNT_RECORDID) REFERENCES TCASH_BOMACCOUNT(RECORDID) ON DELETE CASCADE ON UPDATE RESTRICT ENFORCED ENABLE QUERY OPTIMIZATION; ALTER TABLE TCASH_AVMACCOUNT ADD CONSTRAINT FK57641F8FB2FBC862 FOREIGN KEY (HEADINFO_RECORDID) REFERENCES TCASH_HEADINFO(RECORDID) ON DELETE CASCADE ON UPDATE RESTRICT ENFORCED ENABLE QUERY OPTIMIZATION; COMMIT;
