IBM Support

75 ways to demystify DB2: #21: Techtip: Getting SQL0444N with Reason code: "6"

Technical Blog Post


Abstract

75 ways to demystify DB2: #21: Techtip: Getting SQL0444N with Reason code: "6"

Body

Getting SQL0444N with Reason code: "6" ? 
 
For example, when you do a query like: db2 "select count(*) from SYSIBMADM.DB_HISTORY"
 
You can get the following error.
 
SQL0444N  Routine "*IST_HIST" (specific name "ADMIN_LIST_HIST") is implemented
with code in library or path "...e/db2adminListHist", function
"adminListHistTrusted" which cannot be accessed.  Reason code: "6".
SQLSTATE=42724
 
Why is that? How to troubleshoot?
 
1. Take a look at db2diag.log. You may find something similar to:
 
2015-03-11-12.31.12.617317-240 I2065E676             LEVEL: Severe
PID     : 2260                 TID : 139715957221120 PROC : db2sysc 0
INSTANCE: db2istg1             NODE : 000            DB   : DSSCONCT
APPHDL  : 0-59722              APPID: 159.247.105.136.50488.150311163108
AUTHID  : DB2ISTG1             HOSTNAME: DSSDB01STGC.ct.gov
EDUID   : 267                  EDUNAME: db2agent (DSSCONCT) 0
FUNCTION: DB2 UDB, routine_infrastructure, sqlerRoutineResolveAddress, probe:10
RETCODE : ZRC=0x870F0038=-2029060040=SQLO_PROC_NOT_FOUND "Stored Proc Not Found"
        DIA1002C Error accessing function "monGetPkgCacheStmtv101fp2" in
        library "db2dbrouttrusted".
 
The function sqlerRoutineResolveAddress is telling that there is an error accessing function monGetPkgCacheStmtv101fp2.
 
There is a reference to v101fp2 in the function name itself. Next step would be to check the 'db2level' of the instance you are on.
 
2. Check db2level
 
db2inst1@host1:~> db2level
DB21085I  Instance "db2inst1" uses "64" bits and DB2 code release "SQL10010"
with level identifier "0201010E".
Informational tokens are "DB2 v10.1.0.0", "s120403", "LINUXAMD64101", and Fix
Pack "0".
Product is installed at "/opt/ibm/db2/V10.1".
 
3. Check the level of database
 
db2inst1@host1:~> db2 connect to sample
 
   Database Connection Information
 
 Database server        = DB2/LINUXX8664 10.1.0
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE
 
 
db2inst1@host1:~> db2 "select * from sysibm.sysversions"
 
VERSIONNUMBER VERSION_TIMESTAMP          AUTHID                                                                                                                           VERSIONBUILDLEVEL       
------------- -------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
     10010000 2015-03-11-14.29.40.689138 DB2INST1                                                                                                                         s120403                 
     10010200 2015-03-12-15.39.04.769379 DB2INST1                                                                                                                         s121127                 
 
  2 record(s) selected.
  
 
So, we can see that the instance is on V10.1 fp1 but the database is on V10.1 fp2. Hence the error.
 
How can this happen? Well, there are two possibilities.
 
1. We could have taken a database that was created or upgraded to a V10.1 fp2 instance and restored it onto a V10.1 fp1 instance. DB will be functional, but the features that are enabled or requires fp2 will not be functional.
2. For what ever reason, a V10.1 fp2 instance could have been downgraded to V10.1 fp1.
 
 
Here is an example.
 
1. To start with, we are on V10.1fp1 and the database is also reflecting the same.
 
db2inst1@host1:~> db2level
DB21085I  Instance "db2inst1" uses "64" bits and DB2 code release "SQL10010"
with level identifier "0201010E".
Informational tokens are "DB2 v10.1.0.0", "s120403", "LINUXAMD64101", and Fix
Pack "0".
Product is installed at "/opt/ibm/db2/V10.1".
 
db2inst1@host1:~> db2 "select * from sysibm.sysversions"
 
VERSIONNUMBER VERSION_TIMESTAMP          AUTHID                                                                                                                           VERSIONBUILDLEVEL
------------- -------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
     10010000 2015-03-11-14.29.40.689138 DB2INST1                                                                                                                         s120403
 
  1 record(s) selected.
 
2. Now, we go ahead and update the instance to V10.1 fp2
 
 
host1:/opt/ibm/db2/V10.1fp2/instance # su - db2inst1
ddb2inst1@host1:~> db2level
DB21085I  This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL10012" with level
identifier "0203010E".
Informational tokens are "DB2 v10.1.0.2", "s121127", "IP23394", and Fix Pack
"2".
Product is installed at "/opt/ibm/db2/V10.1fp2".
 
db2inst1@host1:~> db2 connect to sample
 
   Database Connection Information
 
 Database server        = DB2/LINUXX8664 10.1.2
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE
 
db2inst1@host1:~> db2 "select * from sysibm.sysversions"
 
VERSIONNUMBER VERSION_TIMESTAMP          AUTHID                                                                                                                           VERSIONBUILDLEVEL       
------------- -------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
     10010000 2015-03-11-14.29.40.689138 DB2INST1                                                                                                                         s120403                 
 
  1 record(s) selected.
 
 
The db is still on V10.1 fp1. This is expected as we didn't update the database yet.
 
db2inst1@host1:~> db2 "select count(*) from SYSIBMADM.DB_HISTORY"
 
1
-----------
          2
 
  1 record(s) selected.
 
 
 
Now, doing db2updv10:
 
db2inst1@host1:~> db2updv10 -d sample
 
 
_________________________________________________________________________
 
                    _____   DB2 Service Tools   _____
 
                            I      B      M
 
                               db2updv10
 
   This tool is a service utility designed to update a DB2 Version 10.1
   database to the current fix pack level.
 
_________________________________________________________________________
 
 
DB2 Universal Database Version 10.1, 5622-044 (c) Copyright IBM Corp. 2011
Licensed Material - Program Property of IBM
IBM DATABASE 2 Database update to current fix pack tool
 
MESSAGE: SYSCAT.ROUTINEAUTH had 368 rows successfully updated.
MESSAGE: SYSCAT.MODULEAUTH had 7 rows successfully updated.
db2updv10 completed successfully for database 'sample'.
 
 
Now:
 
db2inst1@host1:~> db2 "select * from sysibm.sysversions"
 
VERSIONNUMBER VERSION_TIMESTAMP          AUTHID                                                                                                                           VERSIONBUILDLEVEL       
------------- -------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
     10010000 2015-03-11-14.29.40.689138 DB2INST1                                                                                                                         s120403                 
     10010200 2015-03-12-15.39.04.769379 DB2INST1                                                                                                                         s121127                 
 
  2 record(s) selected.
 
 
As you can see, my db is on v101fp2.
 
 
db2inst1@host1:~> db2 "select count(*) from SYSIBMADM.DB_HISTORY"
 
1
-----------
          2
 
  1 record(s) selected.
 
Now, bringing the instance level down again to v10.1 fp1
 
./db2iupdt -f level db2inst1
 
host1:/opt/ibm/db2/V10.1/instance # su - db2inst1
db2inst1@host1:~> db2level
DB21085I  Instance "db2inst1" uses "64" bits and DB2 code release "SQL10010"
with level identifier "0201010E".
Informational tokens are "DB2 v10.1.0.0", "s120403", "LINUXAMD64101", and Fix
Pack "0".
Product is installed at "/opt/ibm/db2/V10.1".
 
 
db2inst1@host1:~> db2 "select * from sysibm.sysversions"
 
VERSIONNUMBER VERSION_TIMESTAMP          AUTHID                                                                                                                           VERSIONBUILDLEVEL       
------------- -------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
     10010000 2015-03-11-14.29.40.689138 DB2INST1                                                                                                                         s120403                 
    -10010200 2015-03-12-15.39.04.769379 DB2INST1                                                                                                                         s121127                 
 
  2 record(s) selected.
 
 
db2inst1@host1:~> db2 "select count(*) from SYSIBMADM.DB_HISTORY"
 
1
-----------
SQL0444N  Routine "*IST_HIST" (specific name "ADMIN_LIST_HIST") is implemented
with code in library or path "...e/db2adminListHist", function
"adminListHistTrusted" which cannot be accessed.  Reason code: "6".
SQLSTATE=42724
 
This proves the theory that a instance could have been downgraded or a database that was on V10.1 FP2 is being used on a downlevel instance.

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11141168