DB2 - Problembeschreibung
Problem IC74981 | Status: Geschlossen |
INSERT/UPDATE/DELETE statement fails to regenerate an invalidated trigger and returns an unexpected SQL0727N (SQL601N) | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
A data change statement may fail with SQL0727N, action type "3", SQLCODE "-601". This can happen under the following conditions: 1. The target table of the data change statement has 2 or more triggers that are in the invalid state 2. One of the triggers invokes a routine that is in the invalid state 3. The routine from #2 includes a data change statement with the same target table as the main data change statement Here is an example scenario: CREATE TABLE table1 (sender VARCHAR(30)); CREATE TABLE table2 (sender VARCHAR(30)); CREATE OR REPLACE PROCEDURE sample_proc DYNAMIC RESULT SETS 9999 MODIFIES SQL DATA NO EXTERNAL ACTION LANGUAGE SQL BEGIN ATOMIC UPDATE table1 SET sender = (SELECT sender FROM table2); -- RETURN 0; -- END; CREATE TRIGGER trig1 AFTER INSERT ON table1 REFERENCING NEW_TABLE AS INSERTED FOR EACH STATEMENT MODE DB2SQL BEGIN ATOMIC INSERT INTO table2 SELECT * FROM INSERTED; -- CALL sample_proc(); -- END; CREATE TRIGGER trig2 AFTER UPDATE ON table1 REFERENCING NEW_TABLE AS INSERTED FOR EACH STATEMENT MODE DB2SQL BEGIN ATOMIC INSERT INTO table2 SELECT * FROM INSERTED; -- END; -- Drop TABLE2 to invalidate trig1, trig2 and sample_proc DROP TABLE TABLE2; CREATE TABLE table2 (sender VARCHAR(30)); -- INSERT statement results in a chain of object regeneration and SQLCODE -727 INSERT INTO TABLE1 VALUES ('bb'); DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0727N An error occurred during implicit system action type "3". Information returned for the error includes SQLCODE "-601", SQLSTATE "42710" and message tokens "TRIG2|TRIGGER". LINE NUMBER=0. SQLSTATE=56098 | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * N/A * **************************************************************** * PROBLEM DESCRIPTION: * * A data change statement may fail with SQL0727N, action type * * "3", * * SQLCODE "-601". This can happen under the following * * conditions: * * * * 1. The target table of the data change statement has 2 or * * more * * triggers that are in the invalid state * * 2. One of the triggers invokes a routine that is in the * * invalid * * state * * 3. The routine from #2 includes a data change statement with * * the * * same target table as the main data change statement * * * * Here is an example scenario: * * * * CREATE TABLE table1 (sender VARCHAR(30)); * * CREATE TABLE table2 (sender VARCHAR(30)); * * * * CREATE OR REPLACE PROCEDURE sample_proc * * DYNAMIC RESULT SETS 9999 * * MODIFIES SQL DATA * * NO EXTERNAL ACTION * * LANGUAGE SQL * * BEGIN ATOMIC * * UPDATE table1 SET sender = (SELECT sender FROM table2); * * -- * * RETURN 0; -- * * END; * * * * CREATE TRIGGER trig1 AFTER INSERT ON table1 * * REFERENCING NEW_TABLE AS INSERTED * * FOR EACH STATEMENT * * MODE DB2SQL * * BEGIN ATOMIC * * INSERT INTO table2 SELECT * FROM INSERTED; -- * * CALL sample_proc(); -- * * END; * * * * CREATE TRIGGER trig2 AFTER UPDATE ON table1 * * REFERENCING NEW_TABLE AS INSERTED * * FOR EACH STATEMENT * * MODE DB2SQL * * BEGIN ATOMIC * * INSERT INTO table2 SELECT * FROM INSERTED; -- * * END; * * * * -- Drop TABLE2 to invalidate trig1, trig2 and sample_proc * * DROP TABLE TABLE2; * * * * CREATE TABLE table2 (sender VARCHAR(30)); * * * * -- INSERT statement results in a chain of object * * regeneration * * and SQLCODE -727 * * INSERT INTO TABLE1 VALUES ('bb'); * * DB21034E The command was processed as an SQL statement * * because * * it was not a * * valid Command Line Processor command. During SQL processing * * it * * returned: * * SQL0727N An error occurred during implicit system action * * type * * "3". * * Information returned for the error includes SQLCODE "-601", * * SQLSTATE "42710" * * and message tokens "TRIG2|TRIGGER". LINE NUMBER=0. * * SQLSTATE=56098 * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 9.7 Fix Pack 5. * **************************************************************** | |
Local-Fix: | |
Revalidate the objects individually using the ADMIN_REVALIDATE_DB_OBJECTS routine: CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS( 'TRIGGER', 'SCHEMA', 'TRIG2' ); CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS( 'PROCEDURE', 'SCHEMA', 'SAMPLE_PROC' ); CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS( 'TRIGGER', 'SCHEMA', 'TRIG1' ); | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows | |
Lösung | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 11.03.2011 23.12.2011 23.12.2011 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP5 | |
Problem behoben lt. FixList in der Version | |
9.7.0.5 |