Starting in DB2 Version 9.7, when a function is being dropped,
its dependent functions will get invalidated. If the dependent
function has an index extension, dropping the parent function
will cause the index extension to be dropped from the system
catalogs during the invalidation. Thus, when the dependent
function gets dropped at a later time, SQL0901N gets returned as
the corresponding record for the user-defined predicate
specification cannot be found in the system catalogs.
You will find the following record in the db2diag.log:
2011-08-17-15.15.46.994781-300 E197133A1440 LEVEL: Info
(Origin)
PID : 3159 TID : 8 PROC : db2sysc
INSTANCE: regres5 NODE : 000 DB : XTNDDB
APPHDL : 0-277 APPID:
*LOCAL.regres5.110817201533
AUTHID : NEWTON
EDUID : 8 EDUNAME: db2agent (XTNDDB)
FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
MESSAGE : ZRC=0x801A006D=-2145779603=SQLZ_CA_BUILT
"SQLCA has already been built"
DATA #1 : String, 62 bytes
An unexpected error was detected during statement compilation.
DATA #2 : Boolean, 1 bytes
true
DATA #3 : Boolean, 1 bytes
false
DATA #4 : Boolean, 1 bytes
true
DATA #5 : Boolean, 1 bytes
false
DATA #6 : Hex integer, 4 bytes
0x00000000
DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 25
sqlerrmc: predicate entry not found
sqlerrp : SQLRL1E4
sqlerrd : (1) 0x801A006D (2) 0x00000000 (3)
0x00000000
(4) 0x00000000 (5) 0xFFFFFFEC (6)
0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
DATA #8 : Hex integer, 4 bytes
0x00000040
DATA #9 : String, 167 bytes
Compiler error stack for rc = -2145779603:
sqlnn_cmpl[300]
sqlnp_main[250]
sqlnp_parser[510]
sqlnp_smactn[100]
sqlnq_drop_db_object[700]
sqlnq_drop_db_object_end[290]
And failing with the below call stack, which can be found in the
FODC* directory:
(11) 0xc0000000177bc590 sqlrrSqlCode + 0x260 at
sqlrr_error.C:159
[/view/db2_v97fp5_hpipf64_s110814/vbs/INST/lib/libdb2e.so]
(12) 0xc000000018285870 _Z20sqlrl_delete_UDPspecP8sqlrr_cbii +
0x310 at sqlrl_index_extension.C:2647
[/view/db2_v97fp5_hpipf64_s110814/vbs/INST/lib/libdb2e.so]
(13) 0xc0000000184c3ae0
_Z11sqlrldrf_swP8sqlrr_cbPhiS1_iS1_ii18sqlrldrf_sw_callerbbb +
0x3430 at sqlrl_function.C:7959
[/view/db2_v97fp5_hpipf64_s110814/vbs/INST/lib/libdb2e.so]
(14) 0xc0000000162503d0
_Z24sqlnq_drop_db_object_end15sqlnq_ddlobjectP9sqlnq_qtbi19sqlnq
_statementType
+ 0x2290 at sqlnq_ddl_misc.C:7089
[/view/db2_v97fp5_hpipf64_s110814/vbs/INST/lib/libdb2e.so]
(15) 0xc000000016245310
_Z20sqlnq_drop_db_objectPP8stknode_i10actiontypePhP3loc + 0x1c10
at sqlnq_ddl_misc.C:4601
[/view/db2_v97fp5_hpipf64_s110814/vbs/INST/lib/libdb2e.so]
(16) 0xc0000000122a6410 _Z12sqlnp_smactnP8sqlnp_cbi + 0x2d0 at
sqlnp_prs.C:990
[/view/db2_v97fp5_hpipf64_s110814/vbs/INST/lib/libdb2e.so]
(17) 0xc0000000122a3ef0 _Z12sqlnp_parserP8sqlnp_cb + 0xe90 at
sqlnp_driver.C:1084
[/view/db2_v97fp5_hpipf64_s110814/vbs/INST/lib/libdb2e.so]
(18) 0xc00000001226ae90
_Z10sqlnp_mainP12sqlnq_stringbP3locPP9sqlnq_qur + 0x2f0 at
sqlnp_main.C:490
[/view/db2_v97fp5_hpipf64_s110814/vbs/INST/lib/libdb2e.so] |
****************************************************************
* USERS AFFECTED: *
* All users trying to drop a function associated with an index *
* extension using DB2 Version 9.7 on platforms Linux, Unix and *
* Windows. *
****************************************************************
* PROBLEM DESCRIPTION: *
* Starting in DB2 Version 9.7, when a function is being *
* dropped, its dependent functions will get invalidated. If *
* the dependent function has an index extension, dropping the *
* parent function will cause the index extension to be dropped *
* from the system catalogs, syscat.predicatespecs and *
* syscat.indexexploitrules, during the invalidation. Thus, *
* when the dependent function gets dropped at a later time, *
* SQL0901N gets returned as the corresponding record for the *
* user-defined predicate specification cannot be found in the *
* system catalogs. *
****************************************************************
* RECOMMENDATION: *
* Update to DB2 Version 9.7 Fix Pack 6 or follow the local *
* fix. *
**************************************************************** |
