75 ways to demystify DB2: #54: Techtip: Error SQL0551N while calling SYSPROC.NNSTAT

Body

SSYPROC.NNSTAT is a system stored procedure which retrieves currently available statistics on one or more nicknames in a federated database. The statistics are saved in the system catalog on the federated database.

Recently had an issue where the call to this procedure failed with -551 error.

db2 "Call SYSPROC.NNSTAT('SERVER1', NULL, NULL, NULL, NULL, 0, NULL, ?)"
The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation.  Authorization ID: "AUTHID1".  Operation: "CREATE TABLE". Object: "SYSTOOLSTMPSPACE".. SQLCODE=-551, SQLSTATE=42501,

The above error means:

SQL0551N:
The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Authorization ID: authorization-ID. Operation: operation. Object: object-name.

Here the procedure is failing with -551 because the userID does not have authorization to create a table in temporary tablespace.

To troubleshoot that, check the syscat.packages for the owner of the NULLID.NNSTAT package.
SYSPROC.NNSTAT depends on the NULLID.NNSTAT package.

C:\Program Files\IBM\SQLLIB_03\BIN>db2 "select pkgschema, pkgname, owner,dynamic rules from syscat.packages where pkgname='NNSTAT'"

PKGSCHEMA    PKGNAME  OWNER    DYNAMICRULES
--------------------------------------------------------------------------------
NULLID                  NNSTAT      AUTHID1       R

  1 record(s) selected.

If the Owner of this package is NOT the DB2 instance owner or the user with necessary privileges, then the error is expected.
In order to change the ownership of the package, as DB2 instance owner drop the package.

db2 drop package NULLID.NNSTAT

If the package does not exist, the procedure SYSPROC.NNSTAT automatically binds the package next time it is invoked. The rebinding should fix the owner ID.
Once you have dropped the package, try to call the SYSPROC.NNSTAT again as the correct user, that should create the package with the owner as the user you ran the command as and should have authorities to create tables in the temporary tablespace.

C:\Program Files\IBM\SQLLIB_03\BIN>db2 drop package NULLID.NNSTAT
DB20000I  The SQL command completed successfully.

Connecting to the database as an admin user: "db2admin":

C:\Program Files\IBM\SQLLIB_03\BIN>db2 connect to sample user db2admin
Enter current password for db2admin:

   Database Connection Information

 Database server        = DB2/NT64 10.5.5
 SQL authorization ID   = DB2ADMIN
 Local database alias   = SAMPLE

Since the package is dropped, no results returned from the catalog tables:

C:\Program Files\IBM\SQLLIB_03\BIN>db2 "select pkgschema, pkgname, owner,dynamic rules from syscat.packages where pkgname='NNSTAT'"

PKGSCHEMA    PKGNAME  OWNER   DYNAMICRULES
--------------------------------------------------------------------------------
  0 record(s) selected.

Now call the procedure as DB2ADMIN:

C:\Program Files\IBM\SQLLIB_03\BIN>db2 "call SYSPROC.NNSTAT('SERVER1', NULL, NULL,  NULL, NULL, 0, NULL, ?)"

Now check the SYSCAT.PACKAGES for the owner of NULLID.NNSTAT:

It gets updated with the admin owner.

C:\Program Files\IBM\SQLLIB_03\BIN>db2 "select pkgschema, pkgname, owner,dynamic rules from syscat.packages where pkgname='NNSTAT'"

PKGSCHEMA   PKGNAME   OWNER  DYNAMICRULES
--------------------------------------------------------------------------------
NULLID                NNSTAT       DB2ADMIN          R

  1 record(s) selected.

The call to SYSPROC.NNSTAT will be successful after this.

Thanks for reading!

-Subbu Prabhu