DB2 - Problem description
Problem IT39319 | Status: Closed |
SQL0818N CAN OCCUR AFTER A ROLLED BACK REVALIDATION | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
ERROR SQL0818 appear after rolled back revalidation of an SQL routine or trigger Consider the following example. 1) Update trigger TRIG1 becomes invalid due to transfer owner select trigname, valid from syscat.triggers where trigname='TRIG1' TRIGNAME VALID ---------- ---- TRIG1 Y select pkgname, valid from syscat.packages where pkgname in (select bname from syscat.trigdep where trigname='TRIG1') PKGNAME VALID --------- ---- P441013123 Y TRANSFER OWNERSHIP OF TABLE DUMMYSCH.TEST to user TESTUSR PRESERVE PRIVILEGES select trigname, valid from syscat.triggers where trigname='TRIG1' TRIGNAME VALID ---------- ---- TRIG1 N select pkgname, valid from syscat.packages where pkgname in (select bname from syscat.trigdep where trigname='TRIG1') PKGNAME VALID --------- ---- P441013123 Y 2) An update statement is issued that triggers the trigger. This should cause revalidation, but the statement itself fails due to some expected error. E.g. SQL0438N. The revalidation is rolled back. db2 "update DUMMYSCH.TEST set f1=0" SQL0438N Application raised error or warning with diagnostic text: "Trying to update: forbidden". SQLSTATE=75002 select trigname, valid from syscat.triggers where trigname='TRIG1' TRIGNAME VALID ---------- ---- TRIG1 N select pkgname, valid from syscat.packages where pkgname in (select bname from syscat.trigdep where trigname='TRIG1') PKGNAME VALID --------- ---- P441013123 Y 3) The trigger is revalidated again on a new statement, but the update statement returns a -818 sqlcode error db2 "update DUMMYSCH.TEST1 set f1=0" SQL0723N An error occurred in a triggered SQL statement in trigger "DUMMYSCH.TEST". Information returned for the error includes SQLCODE "-818", SQLSTATE "51003" and message tokens "". SQLSTATE=09000 | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * All * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 v11.1 mod 4 Fixpack 7 * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * All * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 v11.1 mod 4 Fixpack 7 * **************************************************************** | |
Comment | |
Fixed in Db2 v11.1 mod 4 Fixpack 7 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 06.12.2021 17.04.2022 17.04.2022 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |