Technical Blog Post
Abstract
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
UID
ibm11140976