ponedeljek, 14. februar 2011

DB2 SQL440 or SQL901 on DELETE

If you get SQL440 or SQL901 when you try to delete from a table, you might encountered this problem. The solution is to recreate all foreign keys that point to this table (MY_TABLE is the SQL script below).
The SQL to generate that SQL script for you is this (statement delimiter is #):

SELECT 'ALTER TABLE EMGSYS.' || TABNAME || ' DROP CONSTRAINT ' || CONSTNAME || '#' || CHR(13) || CHR(10) ||
'ALTER TABLE EMGSYS.' || TABNAME || ' ADD CONSTRAINT ' || CONSTNAME || ' FOREIGN KEY (' || TRIM(FK_COLNAMES) || ') REFERENCES EMGSYS.' || REFTABNAME || '(' || TRIM(PK_COL
NAMES) || ') ON DELETE ' ||
(CASE WHEN DELETERULE = 'R' THEN 'RESTRICT' WHEN DELETERULE = 'C' THEN 'CASCADE' END) || ' ON UPDATE ' || (CASE WHEN UPDATERULE = 'R' THEN 'RESTRICT' WHEN UPDATERULE = 'A
' THEN 'NO ACTION' END) || '#'
FROM SYSCAT.REFERENCES WHERE REFTABNAME = 'MY_TABLE' ORDER BY TABNAME WITH UR

There are still some constands missing in DELETERULE and UPDATERULE. The various values can be seen here.
Objavite komentar