IBM Support

SQL0443N with -727 when calling catalog functions such as SYSIBM.SQLTABLES

Troubleshooting


Problem

The following errors are examples of what you might encounter when you attempt to connect to a database from a client or application.
SQL0443N error, Routine "SYSIBM.SQLTABLES" (Specific Name "TABLES") has returned an error SQLSTATE with diagnostic text "SYSIBM:CLI:-727". SQLSTATE=38553)

Cause

The general cause is that db2schema.bnd file needs to be rebound to the database server.

Another possible cause is that the SELECT privilege was revoked from a set of system catalog tables. When a user tries to execute one of the schema procedures such as SYSIBM.SQLTABLES or SYSIBM.SQLCOLUMNS, it fails with a -551 error.

Resolving The Problem

To resolve the error, first perform the following steps on your server machine:
  1. Log in to the database server as instance owner.
  2. Run the following commands. 
    cd $HOME/sqllib/bnd directory
db2 connect to <database> user <dbadm>
db2 bind db2schema.bnd blocking all grant public sqlerror continue
db2 terminate

If the error persists, you need to determine whether a -551 error is being returned. To determine this increase the DIAGLEVEL to 4 on the database server and execute the application again. You see a similar entry in your db2diag.log;
2019-05-29-09.57.44.538000+060 I12009979F699 LEVEL: Info
PID     : 27734      TID : 183039802560      PROC : db2agent (SAMPLE)
INSTANCE: db2inst1   NODE: 000
APPHDL  : 0-31       APPID: *LOCAL.db2.190511111803
AUTHID  : TESTID
HOSTNAME: db2server
FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA    sqlcabc: 136  sqlcode: -727  sqlerrml: 43
sqlerrmc: 2 -551 42501 TESTID|SELECT|SYSIBM.SQLTABLES
sqlerrp : SQLRA02C
sqlerrd : (1) 0x801A006D   (2) 0x00000000     (3) 0x00000000
(4) 0x00000000     (5) 0xFFFFFFC4      (6) 0x00000000
sqlwarn : (1)     (2)     (3)     (4)       (5)      (6)
(7)     (8)     (9)     (10)       (11)
sqlstate: 56098

In this example, the -727 error is returned because of a -551 error. The -551 error is saying that userid TESTID does not have SELECT privilege on the table SYSIBM.SQLTABLES. Granting the appropriate privilege will fix the problem.
It is recommended to grant SELECT privilege to users and groups that have access to the database for all of these tables.
Example: grant SELECT privilege for SYSIBM.SYSTABLES to user db2inst1
db2 "GRANT SELECT ON TABLE SYSIBM.SQLTABLES TO USER DB2INST1"
Here are the specific system catalog tables that the SELECT privilege is required for in order for Db2's schema API's to work:
SYSIBM.SQLTABLES
SYSIBM.SYSTABLES
SYSIBM.SQLTABLETYPES
SYSIBM.SQLTABLEPRIVILEGES
SYSIBM.SYSCOLUMNS
SYSIBM.SQLSPECIALCOLUMNS
SYSIBM.SQLCOLPRIVILEGES
SYSIBM.SYSDUMMY1
SYSIBM.SQLPRIMARYKEYS
SYSIBM.SQLSTATISTICS
SYSIBM.SYSFOREIGNKEYS
SYSIBM.SYSINDEXES
SYSIBM.SQLPROCEDURES
SYSIBM.SQLPROCEDURECOLS
SYSIBM.SYSROUTINES
SYSIBM.SYSROUTINEPARMS
SYSIBM.SQLTYPEINFO
SYSIBM.SQLUDTS
SYSIBM.SQLSCHEMAS
SYSIBM.SYSRELS
SYSIBM.SYSKEYCOLUSE
SYSIBM.SYSTABCONST
SYSIBM.SYSDATATYPES
The system catalog tables and views those are referred by db2schema procedures can vary depending on the Db2 versions and fix packs. 
So you may want to check system catalog tables and views that the SELECT privilege is required on your environment like as followings:
  1. Identify the package name on your db2schame procedures. 
    $ cd $HOME/sqllib/bnd
$ db2bfd -b db2schema.bnd | grep -e ^App App Name "SQLL9P1N"
  2. List the tables and views the db2schame package refers. 
    $ db2 "select char(bschema,30)schame,char(bname,30)name,btype from syscat.packagedep where pkgname='SQLL9P1N' and btype in ('T','V')"
For example, you have to grant SELECT privilege to SYSCAT.DATATYPES table in v11.5.6.0 or later to run SYSIBM.SQLCOLUMNS schema procedure.
Note: You have to be aware of the SELECT privileges on the system catalog tables in case the database is created with RESTRICTIVE option.

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m0z0000001gWCAAY","label":"Connectivity-\u003EODBC"}],"ARM Case Number":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.1.0;10.5.0;11.1.0;11.5.0"}]

Document Information

Modified date:
06 April 2023

UID

swg21267177

Page Feedback