ChannelDB2

How to resolve SQL0552N error for NNSTAT?

Problem

db2 => connect to feddb

Database Connection Information

Database server = DB2/AIX64 9.5.5
SQL authorization ID = DEVDBA
Local database alias = SB1DM

db2 => CALL SYSPROC.NNSTAT('RDHCOVC', 'EDGEDM', 'IGS_OPP_DETAIL_HISTORY_MEAS_REVENUE', NULL, NULL, 0, NULL, ?)
SQL0552N "OMATIC" does not have the privilege to perform operation "CREATE
TABLESPACE". SQLSTATE=42502


Use OMTIC , got the same error

db2 => connect to feddb

Database Connection Information

Database server = DB2/AIX64 9.5.5
SQL authorization ID = OMATIC
Local database alias = SB1DM

db2 => CALL SYSPROC.NNSTAT('RDHCOVC', 'EDGEDM', 'IGS_OPP_DETAIL_HISTORY_MEAS_REVENUE', NULL, NULL, 0, NULL, ?)
SQL0552N "OMATIC" does not have the privilege to perform operation "CREATE
TABLESPACE". SQLSTATE=42502


DEVDBA has SYSADM authority

db2 => get authorizations

Administrative Authorizations for Current User

...

Indirect SYSADM authority = YES
Indirect SYSCTRL authority = NO
....

omatic has DBADM but not SYSADM and SYSCROL

db2 => get authorizations

Administrative Authorizations for Current User

Direct SYSADM authority = NO
Direct SYSCTRL authority = NO
Direct SYSMAINT authority = NO
Direct DBADM authority = YES
Direct CREATETAB authority = YES
Direct BINDADD authority = YES
Direct CONNECT authority = YES
Direct CREATE_NOT_FENC authority = YES
Direct IMPLICIT_SCHEMA authority = YES
Direct LOAD authority = YES
Direct QUIESCE_CONNECT authority = YES
Direct CREATE_EXTERNAL_ROUTINE authority = YES
Direct SYSMON authority = NO

Indirect SYSADM authority = NO
Indirect SYSCTRL authority = NO
Indirect SYSMAINT authority = NO
Indirect DBADM authority = NO
Indirect CREATETAB authority = YES
Indirect BINDADD authority = YES
Indirect CONNECT authority = YES
Indirect CREATE_NOT_FENC authority = NO
Indirect IMPLICIT_SCHEMA authority = YES
Indirect LOAD authority = YES
Indirect QUIESCE_CONNECT authority = NO
Indirect CREATE_EXTERNAL_ROUTINE authority = NO
Indirect SYSMON authority = YES




Answer:

In V9.1 and V9.5, NNSTAT will create a USER TEMPORARY TABLESPACE WRAPPER1 at the first time call.

But The privileges held by the authorization ID of "CREATE TABLESPACE" must include SYSCTRL or SYSADM authority.


And check the doc:
However, when a package is created and the OWNER bind option is specified, the owner of objects created by the static SQL statements in the package is the value of the OWNER bind option.

As a procedure, NNSTAT bind owner option can be checked by:
db2 "select owner from syscat.packages where PKGNAME='NNSTAT'"

Usually, the ID that created the Federated database is the bind owner.

So the bind owner should have SYSADM or SYSCROL authority to CREATE TABLESPACE.
In this case, give user OMATIC that authority should resolve the problem.

Another options is,
Before first time call NNSTAT, you can use a ID with SYSADM or SYSCROL authority to CREATE TABLESPACE manually by yourself.

CREATE USER TEMPORARY TABLESPACE WRAPPER1
MANAGED BY SYSTEM
USING ('wrapper1');

Views: 956

Tags: DB2, Federation, Information, Integration, Wrapper, collection, nickname, statistics

Comment

You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Try BLU Acceleration on Cloud

© 2014   Created by channeldb2.

Badges  |  Report an Issue  |  Terms of Service