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.