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- E3064418E1437       LEVEL: Error
PID     : 9888                 TID  : 139713792960256PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000          DB   : SAMPLE
APPHDL  : 0-4498               APPID: *LOCAL.db2inst1.121113202302
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
DATA #3 : String, 29 bytes
Number of IPC resource freed:
DATA #4 : signed integer, 4 bytes
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"


  1 record(s) selected.

And my problem is resolved.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 556


  1. 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”

    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

  2. Hi Ember,

    I’ve got the same issue in a pre-installed instance and the instance update command did not solve the issue, I can not even not start the instance. I’ve dropped and recreated the instance and the same occurred.
    It is running into Ubuntu server.

    Any thought on this?

  3. Hi Ember ,

    I am a fan of your website , which always provides me lot of solutions where i never get from IBM repositories.

    Mine is a weired case.
    I am unable to stop the db2 instance :-

    12/18/2018 13:05:24 0 0 SQL1042C An unexpected system error occurred.
    SQL1042C An unexpected system error occurred. SQLSTATE=58004
    db2stop force
    12/18/2018 13:05:28 0 0 SQL1042C An unexpected system error occurred.
    SQL1042C An unexpected system error occurred. SQLSTATE=58004

    Since the issue itself is with stopping instance , I had to perform db2_kill which messed up my TSA completely , and I had to spent whole day to fix the same (ur website was very helpful for that )

    After killing db2 , completed db2iupdt but the error persists.

    Any other suggestion to solve the issue since we need to a DB bounce so that Apllications can connect to DB.

    • Can you replicate the problem? If so, I’d open a PMR with IBM. That’s not normal behavior, but sometimes instances just get in a state where they have to be killed. Yes, the repercussions for TSA are bad. If possible, next time, try disabling TSA first if you can (db2haicu -disable).

      • Sure….thanks a lot for your reply…..I tried all methods …..disabled tsa and stopped hadr for all databases and then did db2iupdt after killing instance ….no use….still problem persists…:(

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.