DB2 Client failing with SQLCODE -3015 and server is showing -572 in db2diag.log

Troubleshooting

Problem

DB2 Client Application is failing returning SQLCODE -3015 and unable to access Db2 Server database.

Symptom

Db2 Client Application is failing with SQLCODE -3015 which is SQL3025N.

"SQL3015N An SQL error "<sqlcode>" occurred during processing"

It means the <sqlcode> will show the real problem code.
However, there is no such code which was logged in the client db2diag.log file

Cause

At Db2 Server side some packages became inoperative

Environment

Db2LUW Database and customer application is accessing the same database.

Diagnosing The Problem

When looking into db2diag.log from Db2 Client setup -3015 messages are found.

But, there is no successive SQL code after that.

The messages were faced by non-Db2 process' which are using Db2 interface functions.

Example of Db2 client db2diag.log messages,

2022-03-14-13.55.04.212915-420 I104409E476 LEVEL: Error

PID : 3468 TID : 140343171790592 PROC : myProc

INSTANCE: db2inst1 NODE : 000 DB : MYDB

APPID : <>

HOSTNAME: myhost

FUNCTION: DB2 UDB, database utilities, sqluLoadClient, probe:22

MESSAGE : ZRC=0xFFFFF439=-3015

SQL3015N An SQL error "" occurred during processing.

DATA #1 : String, 29 bytes

Error setting isolation level

On Db2 server side db2diag.log logged -572 messages sometimes.

Those were logged primarily when the DIAGLEVEL is set to 4.

022-03-14-13.58.23.015893-420 E535217A588          LEVEL: Info
PID     : 22676140             TID : 61173          PROC : db2sysc 0
INSTANCE: db2inst1              NODE : 000           DB   : MYDB
APPHDL : 0-116                APPID: <>
UOWID   : 4                    ACTID: 3
AUTHID : MYAUTH             HOSTNAME: myhost
EDUID   : 61173                EDUNAME: db2agent (MYDB) 0
FUNCTION: DB2 UDB, runtime interpreter, sqlriUFOBCacheRelease, probe:1100
DATA #1 : <preformatted>
UFOBResultCache Statistics
SYSIBM.SQLSTATISTICS: undef

2022-03-14-13.58.23.025686-420 I535806A874          LEVEL: Info
PID     : 22676140             TID : 61173          PROC : db2sysc 0
INSTANCE: db2inst1              NODE : 000           DB   : MYDB
APPHDL : 0-116                APPID: <>
AUTHID : MYAUTH             HOSTNAME: myhost
EDUID   : 61173                EDUNAME: db2agent (MYDB) 0
FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -572   sqlerrml: 15
sqlerrmc: NULLID.SQLUJO0H
sqlerrp : SQLRA145

Details of -572 is following,

SQL0572N Package "<pkgname>" is inoperative.

Explanation:

The package "<pkgname>" is marked as inoperative and must be
explicitly rebound (without specifying RESOLVE CONSERVATIVE) before it
can be used. This package cannot be used because one or more
user-defined functions that it depends upon have been dropped.

User response:

Explicitly rebind the named package using either the REBIND (without
specifying RESOLVE CONSERVATIVE) or the BIND command.

Resolving The Problem

Based on the diagnosis of the problem the very first suggestion which was provided is to rebind the packages. But, that did not resolve the issue.

Following action resolved the issue in multiple similar customer cases,

In database config following should be set when this problem is faced,

(AUTO_REVAL) = DISABLED

Changing that to following should resolve the issue,
(AUTO_REVAL) = DEFERRED

AUTO_REVAL configuration parameter controls the re-validation and invalidation semantics.

Related Information

This configuration parameter controls the revalidation and invalidation semanti…

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSAUS7","label":"DB2 Universal Database Enterprise Edition"},"ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Tips