|
Problem IC74981
|
Status: Closed |
INSERT/UPDATE/DELETE statement fails to regenerate an invalidated trigger and returns an unexpected SQL0727N (SQL601N) |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| 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 |
| Problem Summary: |
****************************************************************
* 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' ); |
| available fix packs: |
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
|
| Solution |
|
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 11.03.2011
23.12.2011
23.12.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
