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.
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
and activate the database
turn on the self tuning memory manager db cfg parameter
and restart the database
Knowledge for FREE, Knowledge for ALL
Not a situation I’ve run into before. Thank you for sharing.
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?
Definitely one you’ll have to open a PMR on.
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
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…:(