IBM Support

SQL1131N error when calling a fenced routine in DB2 due to a missing fenced userid

Troubleshooting


Problem

When calling a fenced routine (stored procedure or user defined function) in DB2, a SQL1131N error may occur if the fenced user was removed from the system

Symptom

An error such as the following may be seen in the db2diag.log:

2014-05-15-11.08.02.575567-240 E625667A1294 LEVEL: Severe
PID : 11862018 TID : 3540 PROC : db2sysc 0
INSTANCE: instance NODE : 000 DB : DB
APPHDL : 0-18191 APPID: *LOCAL.instance.140515150807
AUTHID : authid
EDUID : 3540 EDUNAME: db2agent (DB) 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] 0x09000000121AE8D4 pdLog + 0x88
[1] 0x09000000135CCE30 sqlerSendFmpStart__FP13sqlerFmpParmsP14sqlerFmpHandleP5sqlca + 0xC20
[2] 0x09000000135D6BB8 sqlerInvokeFencedRoutine__FP13sqlerFmpParms + 0x5F8
[3] 0x0900000011B43F08 sqlriInvokeInvoker__FP10sqlri_ufob + 0x888
[4] 0x09000000122FBEE8 sqlricall__FP8sqlrr_cb + 0x100
[5] 0x09000000122A784C sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm + 0xFFFFFFFFFFFFF818
[6] 0x09000000122CEA50 sqlrr_process_execute_request__FP8sqlrr_cbi + 0x358
[7] 0x09000000122E41F0 sqlrr_execute__FP14db2UCinterfaceP9UCstpInfo + 0x240
[8] 0x09000000122E88C0 sqljs_ddm_excsqlstt__FP14db2UCinterfaceP13sqljDDMObject + 0x514
[9] 0x09000000122E9120 sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UCinterface + 0xFFFFFFFFFFFFFFBC

Diagnosing The Problem

Take a DB2 trace locally on the database server.
db2trc on -f trace.dmp
<Call the routine to reproduce the -1131 error>
db2trc off
db2trc flw trace.dmp trace.flw

In the formatted FLW file the following may be seen stating a BAD USER was found trying to switch to the fenced userid:

99500 | sqloSwitchToFencedUserid data [probe 20]
99501 | | sqloGetEnvInternal entry
99502 | | | EnvRegGetProfile entry
99503 | | | EnvRegGetProfile exit
99504 | | | EnvPrfGetValueByEnumIndex entry
99505 | | | EnvPrfGetValueByEnumIndex exit [rc = 0x870F0104 = -2029059836 = RC_ENV_NOT_FOUND]
99506 | | | EnvRegGetProfile entry
99507 | | | EnvRegGetProfile exit
99508 | | | EnvPrfGetValueByEnumIndex entry
99509 | | | EnvPrfGetValueByEnumIndex exit [rc = 0x870F0104 = -2029059836 = RC_ENV_NOT_FOUND]
99510 | | | EnvRegGetProfile entry
99511 | | | EnvRegGetProfile exit
99512 | | | EnvPrfGetValueByEnumIndex entry
99513 | | | EnvPrfGetValueByEnumIndex exit [rc = 0x870F0104 = -2029059836 = RC_ENV_NOT_FOUND]
99514 | | sqloGetEnvInternal exit [rc = 0x870F0104 = -2029059836 = RC_ENV_NOT_FOUND]
99515 | | sqloGetUserAttribById entry
99516 | | | sqloxltc_app entry
99517 | | | sqloxltc_app exit
99518 | | sqloGetUserAttribById data [probe 62]
99519 | | | sqloMapSecuritySystemError entry
99520 | | | sqloMapSecuritySystemError exit [rc = 0x800F006A = -2146500502 = SQLO_BAD_USER]
99521 | | sqloGetUserAttribById error [probe 40]

Look under the $INSTHOME/sqllib/ctrl directory

drwxrwsr-t 2 inst_name inst_group 4096 May 15 11:26 .
drwxrwsr-t 21 inst_name inst_group 4096 Jun 20 2012 ..
-rw-r--r-- 1 inst_name1 inst_group 0 May 15 11:26 .0
-rw-rw-r-- 1 20578 db2fgrp1 9 Jul 31 2009 .fencedID

The file named .fencedID may be owned by a number (in this example: 20578) rather than a userid.

Resolving The Problem

Re-create the missing fenced id on the system. To find out the name of who the fenced userid was supposed to be, view the contents of the .fencedID file. The string that is found in this file is the name of the fenced account missing on the machine and should be created

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"DB2 Tools - Troubleshooting","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"}],"Version":"10.1;10.5;9.5;9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Product Synonym

Document Information

Modified date:
16 June 2018

UID

swg21673450