home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

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
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 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 FixList