IBM Support

SQL1042C running a fenced routine (stored procedure/UDF)

Troubleshooting


Problem

DB2 error message SQL1042C was received when running a fenced routine (stored procedure/UDF)

Symptom

When running a fenced routine (stored procedure/UDF), the following error may occur:

SQL1042C An unexpected system error occurred. SQLSTATE=58004

Cause

This problem may be caused by one of the following reasons:

1) The fenced user is missing from the system

2) The fenced user for the instance cannot access the following due to permission issues:
- $INSTHOME/sqllib
- $INSTHOME/sqllib/db2systm

Environment

This scenario as specific to Linux/UNIX environments as there is no fenced user on Windows.

Diagnosing The Problem

Messages such as the following may be seen in the db2diag.log file:

2009-08-27-08.41.35.829137-240 E97538795A1408 LEVEL: Severe
PID : 15412 TID : 72 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : DBNAME
APPHDL : 0-7980 APPID: *LOCAL.name.090827124139
AUTHID : db2inst1
EDUID : 72 EDUNAME: db2agent (DBNAME) 0
FUNCTION: DB2 UDB, routine_infrastructure, sqlerSendFmpStart, probe:10
MESSAGE : ZRC=0xFFFFFBEE=-1042
DATA #1 : SQC RC type., PD_TYPE_SQC_RC, 4 bytes
54
CALLSTCK:
[0] 0xFFFFFFFF7B35CBDC __1cRsqlerSendFmpStart6FpnNsqlerFmpParms_pnOsqlerFmpHandle_pnFsqlca__i_ + 0x224
[1] 0xFFFFFFFF7B355144 __1cYsqlerInvokeFencedRoutine6FpnNsqlerFmpParms__i_ + 0xAA4
[2] 0xFFFFFFFF7CD20D9C __1cSsqlriInvokeInvoker6FpnKsqlri_ufob__i_ + 0x10AC
[3] 0xFFFFFFFF7CD1B00C __1cJsqlricall6FpnIsqlrr_cb__i_ + 0x3EC
[4] 0xFFFFFFFF7CC0FD48 __1cPsqlriSectInvoke6FpnIsqlrr_cb_pnMsqlri_opparm__i_ + 0x388
[5] 0xFFFFFFFF7C8F3BBC __1cbDsqlrr_process_execute_request6FpnIsqlrr_cb_i_i_ + 0x1B9C
[6] 0xFFFFFFFF7C8D2214 __1cNsqlrr_execute6FpnOdb2UCinterface_pnJUCstpInfo__i_ + 0x254
[7] 0xFFFFFFFF7B52A3C4 __1cTsqljs_ddm_excsqlstt6FpnOdb2UCinterface_pnNsqljDDMObject__i_ + 0x694
[8] 0xFFFFFFFF7B505A0C __1cVsqljsParseRdbAccessed6FpnNsqljsDrdaAsCb_pnNsqljDDMObject_pnOdb2UCinterface__i_ + 0x26C
[9] 0xFFFFFFFF7B50688C __1cKsqljsParse6FpnNsqljsDrdaAsCb_pnOdb2UCinterface__i_ + 0x4BC

2009-08-27-08.41.35.833565-240 I97540204A447 LEVEL: Severe
PID : 15412 TID : 72 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : DBNAME
APPHDL : 0-7980 APPID: *LOCAL.name.090827124139
AUTHID : db2inst1
EDUID : 72 EDUNAME: db2agent (DBNAME) 0
FUNCTION: DB2 UDB, routine_infrastructure, sqlerSendFmpStart, probe:10
RETCODE : ZRC=0xFFFFFBEE=-1042

Resolving The Problem

1. Look at the following files
$INSTHOME/sqllib/adm:
-r--r--r-- 1 1234 db2iadm1 0 Apr 11 2014 .fenced

$INSTHOME/sqllib/ctrl:
-rw-rw-r-- 1 1234 db2iadm1 9 Aug 14 2012 .fencedID

2. If the files are owned by an number rather than a userid, then the fenced id is missing from the system. As the fenced user may have been accidentally been removed from the system, it needs to be recreated.

3. If the files above are owned by a userid, that userid is the fenced user. The fenced user can also be determined by reading the contents of the .fencedID file.

4. Log in using the fenced userid.

5. Test the db2systm file permissions with fenced userid by attempting to view the contents and see if permission errors occur. Note that the db2systm file is a binary file and the contents are not readable.
cat $INSTHOME/sqllib/db2systm

6. 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.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Routines (SP \u0026 UDF) - SQL","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"}],"Version":"9.7;9.5;10.1;10.5","Edition":"Enterprise Server;Express;Personal;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
06 December 2022

UID

swg21399105