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.
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
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)  0x00007F135201C996 pdLog + 0x398  0x00007F1352FC25C5 _Z23sqlerRemoveAllIPCforRowP11sqlerFmpRowb + 0x3FF  0x00007F1352FC215C /db2home/db2inst1/sqllib/lib64/libdb2e.so.1 + 0x1D0715C  0x00007F1352FC2662 _Z23sqlerRemoveFmpFromTableP11sqlerFmpRowb + 0x2A  0x00007F1352FC2819 /db2home/db2inst1/sqllib/lib64/libdb2e.so.1 + 0x1D07819  0x00007F13521A0948 _Z19sqlerGetFmpFromPoolP14sqlerFmpHandleP13sqlerFmpParms + 0x498  0x00007F135219DF07 _Z24sqlerInvokeFencedRoutineP13sqlerFmpParms + 0x3D1  0x00007F135272B39C _Z18sqlriInvokeInvokerP10sqlri_ufobb + 0x2CA  0x00007F1353FE80B4 _Z8sqlriutfP8sqlrr_cb + 0x2EA  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.”
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)
# ./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.