SQL1042C on Use of A System Table Function

This post is specific to DB2 for UNIX or Linux. SQL1042C can have a number of causes and be received as an error from a number of different commands. The IBM Support Portal does a good job of coming up with the issues and solutions. And I have it in the back of my head that when I get this error, I need a db2iupdt – which is non trivial since it requires the instance to be down and root access to run.

The Problem

Today, on a server I’m working on, I noticed that one of my maintenance checking scripts was returning incorrect restults. It was reporting that a database backup had never been taken when I knew I had verified a backup just yesterday. I had just started this maintenance script running in this new non-production environment.

When I dug into the detailed script output, I discovered this issue:

db2 "select last_backup from table(snap_get_db('SAMPLE', -1)) as tab_snap"
SQL1042C  An unexpected system error occurred.  SQLSTATE=58004

The Investigation

My script was unable to get a value for the timestamp of the last backup. Now, I knew that I probably needed a db2iupdt because that’s the most common solution I’ve seen for this error message. But I also wanted to have ammunition to back me up in case someone objected to the 15 minutes of down time that this would require. On searching the support portal, I found this Technote: http://www-01.ibm.com/support/docview.wss?uid=swg21399105, which describes receiving SQL1042C when running a fenced stored procedure. I figured that it is entirely possible that a system table function is using a fenced stored procedure at some point, so I look in the db2diag.log for the error that the document references. I find this:

2012-11-13-14.23.03.101637-360 E3064418E1437       LEVEL: Error
PID     : 9888                 TID  : 139713792960256PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000          DB   : SAMPLE
APPHDL  : 0-4498               APPID: *LOCAL.db2inst1.121113202302
AUTHID  : DB2INST1
EDUID   : 1250                 EDUNAME: db2agent (SAMPLE) 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: (Static functions may not be resolved correctly, as they are resolved to the nearest symbol)
  [0] 0x00007F135201C996 pdLog + 0x398
  [1] 0x00007F1352FC25C5 _Z23sqlerRemoveAllIPCforRowP11sqlerFmpRowb + 0x3FF
  [2] 0x00007F1352FC215C /db2home/db2inst1/sqllib/lib64/libdb2e.so.1 + 0x1D0715C
  [3] 0x00007F1352FC2662 _Z23sqlerRemoveFmpFromTableP11sqlerFmpRowb + 0x2A
  [4] 0x00007F1352FC2819 /db2home/db2inst1/sqllib/lib64/libdb2e.so.1 + 0x1D07819
  [5] 0x00007F13521A0948 _Z19sqlerGetFmpFromPoolP14sqlerFmpHandleP13sqlerFmpParms + 0x498
  [6] 0x00007F135219DF07 _Z24sqlerInvokeFencedRoutineP13sqlerFmpParms + 0x3D1
  [7] 0x00007F135272B39C _Z18sqlriInvokeInvokerP10sqlri_ufobb + 0x2CA
  [8] 0x00007F1353FE80B4 _Z8sqlriutfP8sqlrr_cb + 0x2EA
  [9] 0x00007F13526EDD0F _Z15sqlriExecThreadP8sqlrr_cbP12sqlri_opparm + 0x73

To me, that doesn’t look much like what’s in the Technote, but I decide to test out the permissions issue for the fenced user that the Technote describes anyway. When I look at the permissions on sqllib and sqllib/db2systm, they look correct. But when I actually su over to my fenced user and try to view the file as the Technote suggests, I get a permission denied error. That confirms for me that I need the db2iupdt. As the Technote says “If permission problems were found when accessing the db2systm file then use the db2iupdt command (as root userid) to reset the permissions for the instance.”

The Solution

I have to schedule an outage on this particular system, so when the scheduled time comes, here’s what I do:

  • force off all connections
  • stop DB2
  • su over to root
  • cd to /opt/IBM/db2/V9.7/instance (the first part of that is your install path, and may vary – I believe the default has a lower-case IBM in it)
  • execute:
    # ./db2iupdt -k db2inst1
    DBI1070I  Program db2iupdt completed successfully.
    

I then start my instance back up, and try the failing query again:

$ db2 "select last_backup from table(snap_get_db('WC036Q01', -1)) as tab_snap"

LAST_BACKUP               
--------------------------
2012-11-11-03.00.02.000000

  1 record(s) selected.

And my problem is resolved.

2 comments… add one

  • hi, this site has bee quite helpful for beginners like me…so here I am returning the favour..
    (So, that any poor guy does not have to bang his head for three consecutive days to find a solution)

    Problem:– On activating the database ,the system was throwing error
    SQL1042C An unexpected system error occurred. SQLSTATE=58004 and in the db2diag.log
    you will see error like “buffer pool logical error”,”failed to initialise/calculate the group table memory”

    Solution:-
    Check whether you have files SQLSGF.1 and SQLSGF.2 @ ../../NODE0000/SQL00001
    Strangely in my case these files had been deleted (dont know how)
    If you have these files ./db2iupdt -k can do the thing for you.

    But in case you files have also been deleted mysteriously (might be a rouge process)
    you need to follow the following steps.
    Copy the above two files from any existing environment having the same instance_name and db name..doesnt matter how much old they are….or you can restore an old copy of the database in some other environment ..under an instance of the same name as in the case….and extract these two SQLSGF files….and copy them in your environment where you are getting error…
    turned off the self tuning memory manager db cfg parameter

    change the permissions of these files to instance_owner:instance_owner group and restart the instance
    ./db2 terminate
    ./db2 db2stop
    ./db2 db2start

    and activate the database
    turn on the self tuning memory manager db cfg parameter

    and restart the database

    Knowledge for FREE, Knowledge for ALL

    Reply
    • Not a situation I’ve run into before. Thank you for sharing.

      Reply

Leave a Comment