Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2009
    Posts
    272

    Unanswered: SQL1042 error while querying sysibmadm view

    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
    Last edited by blazer789; 07-12-11 at 11:03.

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    Fenced stuff, possibly similar to
    http://www.dbforums.com/db2/1663553-...42c-error.html ?

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

  3. #3
    Join Date
    Jun 2009
    Posts
    272
    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.

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    Quote Originally Posted by blazer789 View Post
    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.

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    Quote Originally Posted by BELLO4KA View Post
    tables functions have routines associated with them
    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?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought table functions shipped with db2 are built-in functions.
    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
    User-defined functions are registered to a database in SYSCAT.ROUTINES (using the
    CREATE FUNCTION statement). User-defined functions are never part of the
    SYSIBM schema. One such set of functions is provided with the database manager
    in a schema called SYSFUN, and another in a schema called SYSPROC.
    Last edited by tonkuma; 07-13-11 at 11:19.

  7. #7
    Join Date
    Jun 2009
    Posts
    272
    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.

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    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.

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by blazer789 View Post
    Are SYSIBMADM views derived from routines ?? I mean would they call stored procs at the backend ??
    They don't call Stored procedures, but Functions as Bella has explained.
    Functions, Procedures ,methods and Triggers all fall under routines.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    Quote Originally Posted by sathyaram_s View Post
    Functions, Procedures ,methods and Triggers all fall under routines.
    Triggers fall under routines as well?

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by BELLO4KA View Post
    Triggers fall under routines as well?
    Thanks for pointing out the error. Triggers do not fall under routines.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    Jun 2009
    Posts
    272
    Thanks Bella, Sathya and tonkuma for your explainations !!

  13. #13
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    Quote Originally Posted by sathyaram_s View Post
    Triggers do not fall under routines.
    There are already enough of them... enough to get me confused

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •