|
Problem IC91858
|
Status: Closed |
DURING IMPLICIT REBIND OF AN INOPERATIVE PACKAGE SQL0572 MAY BE RETURNED WHEN AUTO_REVAL = DISABLED |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
When the database configuration parameter AUTO_REVAL = DISABLED,
if DDL or similar work is performed on a base object, triggers
associated with the base object are marked inoperative. This in
turn marks packages that reference the trigger inoperative as
well.
Even if the "DDL or similar work" is then later rolled back, the
cached apm packages will remain as inoperative in the cache.
When the inoperative package is loaded again, the inoperative
state will cause the DB2 server to drive an implicit rebind of
the package and may fail with -572.
Following is an example of the error you may see:
DB21034E The command was processed as an SQL statement because
it was not a valid Command Line Processor command. During SQL
processing it returned:
SQL0723N An error occurred in a triggered SQL statement in
trigger "TRIG.B1". Information returned for the error includes
SQLCODE "-572", SQLSTATE "51028" and message tokens
"TRIG.P680830983". SQLSTATE=09000
You may find similar entry from the db2diag.log:
Data1 (PD_DB2_TYPE_SQLCA,136) SQLCA:
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -572 sqlerrml: 24
sqlerrmc: TRIG.P680830983
sqlerrp : SQLRA141
sqlerrd : (1) 0x801A006D (2) 0x00000000 (3)
0x00000000
(4) 0x00000000 (5) 0xFFFFFF9C (6)
0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate: |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users running DB2 LUW Version 10.1 Fix Pack 2 with *
* AUTO_REVAL set to DISABLED. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Update to DB2 LUW Version 10.1 Fix Pack 3 or refer to "Local *
* Fix". *
**************************************************************** |
| Local Fix: |
Temporarily set AUTO_REVAL = DEFERRED and retry the statement. |
| available fix packs: |
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
The problem is first fixed in DB2 LUW Version 10.1 Fix Pack 3. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 24.04.2013
27.09.2013
27.09.2013 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.3
|
 |
| 10.1.0.3
|
|
