Environment: DB2 V9.7 FP 3a, Multipartitioned environment with 17 partitions spanning across 3 physical nodes
Operating System: Linux

Getting SQL1042 error while querying the sysibmadm view. This error comes out for only specific SYSIBMADM views. It doesn't come out if I query sysibmadm.admintabinfo view.

Tried bouncing the instance and cleanly started the instance by cleaning ipcs .. . It didn't resolve the issue. After a while, may be after an hour, the query started working fine.

db2 -x "select distinct AGENT_ID,AGENT_ID_HOLDING_LK From SYSIBMADM.SNAPLOCKWAIT"
SQL1042C An unexpected system error occurred. SQLSTATE=58004

Whenever the this error got encountered, I see the following messages in db2diag.log

PID : 22582 TID : 46912803694912PROC : db2sysc 2
INSTANCE: lrmp NODE : 002 DB : LRMDB
APPHDL : 0-122 APPID: *N0.lrmp.110712022310
AUTHID : LRMP
EDUID : 582 EDUNAME: db2agntp (LRMDB) 2
FUNCTION: DB2 UDB, routine_infrastructure, sqlerMasterThreadReq, probe:10
RETCODE : ZRC=0xFFFFFBEE=-1042

2011-07-11-22.42.45.304460-240 E9014E3354 LEVEL: Severe
PID : 22582 TID : 46912803694912PROC : db2sysc 2
2011-07-11-22.42.45.259574-240 E6154E600 LEVEL: Error
PID : 2740 TID : 46915467077952PROC : db2sysc 0
INSTANCE: lrmp NODE : 000 DB : LRMDB
APPHDL : 0-122 APPID: *N0.lrmp.110712022310
AUTHID : LRMP
EDUID : 112 EDUNAME: db2agntp 0
FUNCTION: DB2 UDB, routine_infrastructure, sqlerRemoveAllIPCforRow, probe:10
DATA #1 : String, 32 bytes
Freeing IPC resource explicitly:
DATA #2 : Process ID, 4 bytes
30649
DATA #3 : Hexdump, 8 bytes
0x000000020063FEE0 : 0000 0000 0000 0000 ........

2011-07-11-22.42.45.259956-240 E6755E500 LEVEL: Error
PID : 2740 TID : 46915467077952PROC : db2sysc 0
INSTANCE: lrmp NODE : 000 DB : LRMDB
APPHDL : 0-122 APPID: *N0.lrmp.110712022310
AUTHID : LRMP
EDUID : 112 EDUNAME: db2agntp 0
FUNCTION: DB2 UDB, routine_infrastructure, sqlerRemoveAllIPCforRow, probe:20
DATA #1 : String, 22 bytes
IPC resources Address:
DATA #2 : Pointer, 8 bytes
0x0000000210030080

2011-07-11-22.42.45.260329-240 E7256E1315 LEVEL: Error
PID : 2740 TID : 46915467077952PROC : db2sysc 0
INSTANCE: lrmp NODE : 000 DB : LRMDB
APPHDL : 0-122 APPID: *N0.lrmp.110712022310
AUTHID : LRMP
EDUID : 112 EDUNAME: db2agntp 0
FUNCTION: DB2 UDB, routine_infrastructure, sqlerRemoveAllIPCforRow, probe:30
DATA #1 : String, 29 bytes
Number of IPC resource found:
DATA #2 : signed integer, 4 bytes


lrmp02a-N0:/db2home/lrmp/sqllib/db2dump> db2diag db2diag.0.log_2011-07-11-22.42.56 -l Error | more
2011-07-11-22.42.45.259574-240 E6154E600 LEVEL: Error
PID : 2740 TID : 46915467077952PROC : db2sysc 0
INSTANCE: lrmp NODE : 000 DB : LRMDB
APPHDL : 0-122 APPID: *N0.lrmp.110712022310
AUTHID : LRMP
EDUID : 112 EDUNAME: db2agntp 0
FUNCTION: DB2 UDB, routine_infrastructure, sqlerRemoveAllIPCforRow, probe:10
DATA #1 : String, 32 bytes
Freeing IPC resource explicitly:
DATA #2 : Process ID, 4 bytes
30649
DATA #3 : Hexdump, 8 bytes
0x000000020063FEE0 : 0000 0000 0000 0000 ........

2011-07-11-22.42.45.259956-240 E6755E500 LEVEL: Error
PID : 2740 TID : 46915467077952PROC : db2sysc 0
INSTANCE: lrmp NODE : 000 DB : LRMDB
APPHDL : 0-122 APPID: *N0.lrmp.110712022310
AUTHID : LRMP
EDUID : 112 EDUNAME: db2agntp 0
FUNCTION: DB2 UDB, routine_infrastructure, sqlerRemoveAllIPCforRow, probe:20
DATA #1 : String, 22 bytes
IPC resources Address:
DATA #2 : Pointer, 8 bytes
0x0000000210030080

2011-07-11-22.42.45.260329-240 E7256E1315 LEVEL: Error
PID : 2740 TID : 46915467077952PROC : db2sysc 0
INSTANCE: lrmp NODE : 000 DB : LRMDB
APPHDL : 0-122 APPID: *N0.lrmp.110712022310
AUTHID : LRMP
EDUID : 112 EDUNAME: db2agntp 0
FUNCTION: DB2 UDB, routine_infrastructure, sqlerRemoveAllIPCforRow, probe:30
DATA #1 : String, 29 bytes
Number of IPC resource found:
DATA #2 : signed integer, 4 bytes
1
DATA #3 : String, 29 bytes
Number of IPC resource freed:
DATA #4 : signed integer, 4 bytes
1
CALLSTCK:
[0] 0x00002AAAAB937A6A pdLog + 0x398
[1] 0x00002AAAAC80D1C8 _Z23sqlerRemoveAllIPCforRowP11sqlerFmpRowb + 0x44A
[2] 0x00002AAAAC80DC23 /db2home/lrmp/sqllib/lib64/libdb2e.so.1 + 0x1D54C23
[3] 0x00002AAAAC80DCA4 _Z23sqlerRemoveFmpFromTableP11sqlerFmpRowb + 0x2A
[4] 0x00002AAAAC80E80E /db2home/lrmp/sqllib/lib64/libdb2e.so.1 + 0x1D5580E
[5] 0x00002AAAABAACB4E _Z19sqlerGetFmpFromPoolP14sqlerFmpHandleP13sqlerFm pParms + 0x480
[6] 0x00002AAAABAA92B7 _Z24sqlerInvokeFencedRoutineP13sqlerFmpParms + 0x3DB
[7] 0x00002AAAABFEE53B _Z18sqlriInvokeInvokerP10sqlri_ufobb + 0x739
[8] 0x00002AAAAD7B2472 _Z8sqlriutfP8sqlrr_cb + 0x2EA
[9] 0x00002AAAABFBC829 _Z15sqlriExecThreadP8sqlrr_cbP12sqlri_opparm + 0x79

2011-07-11-22.42.45.355983-240 E12808E607 LEVEL: Error
PID : 22582 TID : 46912803694912PROC : db2sysc 2
INSTANCE: lrmp NODE : 002 DB : LRMDB
APPHDL : 0-122 APPID: *N0.lrmp.110712022310
AUTHID : LRMP
EDUID : 582 EDUNAME: db2agntp (LRMDB) 2
FUNCTION: DB2 UDB, routine_infrastructure, sqlerRemoveAllIPCforRow, probe:10
DATA #1 : String, 32 bytes
Freeing IPC resource explicitly:
DATA #2 : Process ID, 4 bytes
6281
DATA #3 : Hexdump, 8 bytes
0x000000020030FFC0 : 0000 0000 0000 0000

Fenced stuff, possibly similar to
Getting SQL1042C error ?

But I'm not sure why some admin views work and some don't.

Thanks !! You were exactly right. Thats the issue with fenced ID. We had a VAS upgrade and VAS flush wasn't done after that and the fenced user ID was unknown to the instance. After couple of hours, VAS flush was done and the issue got resolved.

Even I am not sure why some admin views worked and some didn't. I am not sure how sysibmadm views depend on fenced user ID.

I think I know why some worked and some didn't. I recall working on a problem where snapshot_cntrfs routine (used by get_dbsize_info) got changed from fenced to non-fenced and this change caused some problems. Anyway, I think tables functions have routines associated with them. Some are defined as fenced and some as non-fenced. Example:


db2 "select substr(ROUTINENAME,1,30), FENCED from syscat.routines" | grep -i admin_get_tab_info
ADMIN_GET_TAB_INFO_V97 N
ADMIN_GET_TAB_INFO_V95 N
ADMIN_GET_TAB_INFO N


db2 "select substr(ROUTINENAME,1,30), FENCED from syscat.routines where ROUTINESCHEMA = 'SYSPROC'" | grep -i snap_get_lockwait
SNAP_GET_LOCKWAIT Y
SNAP_GET_LOCKWAIT Y


This is from v9.7 FP4.

I think this is not correct. Table functions don't have routines associated with them. A table function is a routine. Can someone please correct me?



From the manual:
SYSCAT.ROUTINES catalog view
Each row represents a user-defined routine (scalar function, table function, sourced function, method, or procedure). Does not include built-in functions.


$ db2 "select substr(ROUTINENAME,1,30), FENCED, ORIGIN, FUNCTIONTYPE from syscat.routines" | grep -i snap_get_lockwait
SNAP_GET_LOCKWAIT Y E T
SNAP_GET_LOCKWAIT Y E T


$ db2 "select substr(ROUTINENAME,1,30), FENCED, ORIGIN, FUNCTIONTYPE from syscat.routines" | grep -i admin_get_tab_info
ADMIN_GET_TAB_INFO_V97 N E T
ADMIN_GET_TAB_INFO_V95 N E T
ADMIN_GET_TAB_INFO N E T


I thought table functions shipped with db2 are built-in functions. But when I query syscat.routines, origin = E (user-defined, external). And the description of syscat.routines mentions that it doesn't include built-in functions. Could someone please clarify this for me?

Why?

SYSPROC routnes are user-defined functions provided with the database manager.

IBM DB2 9.7 for LUW SQL Reference, Volume 1 ---> Chapter 3. Functions ---> Functions overview

Are SYSIBMADM views derived from routines ?? I mean would they call stored procs at the backend ?? If so, the respective stored proc which is being called when this view is queried might either be fenced or not-fenced. The fenced one's are the one's which don't work when we have issue with fenced ID. This is my prediction. Correct me if I am wrong.

In your original post, you gave an example of two SYSIBMADM views: ADMINTABINFO and SNAPLOCKWAIT

ADMINTABINFO - based on ADMIN_GET_TAB_INFO_V97 table function
SNAPLOCKWAIT - based on SNAP_GET_LOCKWAIT table function


So, I queried syscat.routines to find if they're fenced or not:

ADMIN_GET_TAB_INFO_V97 is NOT FENCED
SNAP_GET_LOCKWAIT is FENCED


So, when fenced ID had a problem, I believe FENCED ones returned sql1042c and NOT FENCED worked.

They don't call Stored procedures, but Functions as Bella has explained.
Functions, Procedures ,methods and Triggers all fall under routines.

Triggers fall under routines as well?

Thanks for pointing out the error. Triggers do not fall under routines.

Thanks Bella, Sathya and tonkuma for your explainations !!

There are already enough of them... enough to get me confused