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...... |
