DB2 - Problem description
Problem IT37424 | Status: Closed |
TRAP WHEN RUNNING ADMIN_REVALIDATE_DB_OBJECTS() | |
product: | |
DB2 FOR LUW / DB2FORLUW / B50 - DB2 | |
Problem description: | |
I have a database with a locktimeout of 20 seconds. I create a simple procedure, and in one CLP connection with autocommit off, I issue a CALL to that procedure. This leaves the V lock for the execution of the CALL statement held by that connection. In another connection I issue "call admin_revalidate_db_objects('procedure','SNOWBELL',NULL,'Y')" to revalidate all procedures in my schema (or instead of NULL; I also attempt passing in 'P1' (the name of the procedure) instead of the NULL argument. As expected, the revalidation times out and we see in the db2diag.log: 2021-06-18-02.46.24.600094-240 I407815E612 LEVEL: Error PID : 944215 TID : 140732908168960 PROC : db2sysc INSTANCE: snowbell NODE : 000 DB : V115M5 APPHDL : 0-61 APPID: *LOCAL.snowbell.210618064424 UOWID : 5 ACTID: 2 AUTHID : SNOWBELL HOSTNAME: hotellnx107 EDUID : 114 EDUNAME: db2agent (V115M5) FUNCTION: DB2 UDB, access plan manager, sqlra_inval_vars_hard, probe:100 RETCODE : ZRC=0x80100044=-2146435004=SQLP_LTIMEOUT "LockTimeOut - tran rollback Reason code 68" 2021-06-18-02.46.24.600989-240 I408428E1121 LEVEL: Warning PID : 944215 TID : 140732908168960 PROC : db2sysc INSTANCE: snowbell NODE : 000 DB : V115M5 APPHDL : 0-61 APPID: *LOCAL.snowbell.210618064424 UOWID : 5 ACTID: 2 AUTHID : SNOWBELL HOSTNAME: hotellnx107 EDUID : 114 EDUNAME: db2agent (V115M5) FUNCTION: DB2 UDB, database application extension for utilities, admin_revalidate_db_objects_new, probe:999 DATA #1 : String, 16 bytes Unexpected error DATA #2 : String, 11 bytes SNOWBELL.P1 DATA #3 : ASCII Char, 1 bytes DATA #4 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes sqlcaid : SQLCA sqlcabc: 136 sqlcode: 0 sqlerrml: 0 sqlerrmc: sqlerrp : SQL11055 sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000 (4) 0x00000000 (5) 0x00000000 (6) 0x00000000 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: 00000 DATA #5 : signed integer, 4 bytes -2146435004 2021-06-18-08.19.30.836441-240 I409550E1114 LEVEL: Warning PID : 944215 TID : 140732908168960 PROC : db2sysc INSTANCE: snowbell NODE : 000 DB : V115M5 APPHDL : 0-61 APPID: *LOCAL.snowbell.210618064424 UOWID : 7 ACTID: 2 AUTHID : SNOWBELL HOSTNAME: hotellnx107 EDUID : 114 EDUNAME: db2agent (V115M5) FUNCTION: DB2 UDB, database application extension for utilities, admin_revalidate_db_objects_new, probe:999 DATA #1 : String, 16 bytes Unexpected error DATA #2 : String, 7 bytes UNKNOWN DATA #3 : ASCII Char, 1 bytes DATA #4 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes sqlcaid : SQLCA sqlcabc: 136 sqlcode: 0 sqlerrml: 0 sqlerrmc: sqlerrp : SQL11055 sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000 (4) 0x00000000 (5) 0x00000000 (6) 0x00000000 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: 00000 DATA #5 : signed integer, 4 bytes -2146435004 As mentioned, there is no trap, but oddly the error is not returned to the caller. The SQLCA appears to be reset. This is the output: SQL20508N An error occurred during the revalidation of object "UNKNOWN". Operation "REVALIDATION" failed with SQLCODE "0", SQLSTATE "00000", and message tokens "". SQLSTATE=5UA03 sqlca dumped in the diag log for the -911: sqlca for EDUID 46463 0x00007FFEC77F6A20 : 0000 0000 0000 0000 0000 0000 71FC FFFF ............q... 0x00007FFEC77F6A30 : 0200 3638 0000 0000 0020 2020 0000 0000 ..68..... .... 0x00007FFEC77F6A40 : 2020 2020 2020 2020 3078 7FC7 FE7F 0000 0x...... 0x00007FFEC77F6A50 : C06A 7FC7 FE7F 0000 50D0 0DEF FF7F 0000 .j......P....... 0x00007FFEC77F6A60 : 3078 7FC7 FE7F 0000 48D7 537D FE7F 0000 0x......H.S}.... 0x00007FFEC77F6A70 : 0000 0000 0000 0000 5351 4C52 4130 4238 ........SQLRA0B8 0x00007FFEC77F6A80 : 4400 1080 4400 0000 0000 0000 0A00 0000 D...D........... 0x00007FFEC77F6A90 : 0000 0000 0000 0000 2020 2020 3E00 0000 ........ >... 0x00007FFEC77F6AA0 : 9C6A 7FC7 FE7F 0000 .j...... | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ADMIN_REVALIDATE_DB_OBJECTS() users * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description, Install this fix. * **************************************************************** * RECOMMENDATION: * * Upgrade to 11.5.7 or later * **************************************************************** | |
Local Fix: | |
Apply Special Build | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * ADMIN_REVALIDATE_DB_OBJECTS() users * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description, Install this fix. * **************************************************************** * RECOMMENDATION: * * Upgrade to 11.5.7 or later * **************************************************************** | |
Comment | |
Upgrade to 11.5.7 or later | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 25.06.2021 08.12.2021 08.12.2021 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |