RESOLVED : DB2 9.7 setdbtype createdb issues

Issues Faced during the database creation while executing the setdbtype.bat

When we checked the “reorgdb2.err.log”, we found the few of the packaged failed during the reorg operations and we can find errors similar to below in the log file:

SQL0551N: The user “XXXXXX” does not sufficient priviliges to rebind the package 'SYSIBMADM.P201372405' of database 'WCS7DB'. UniqueId=213123…..

Problem (Abstract)

In DB2 Version 9.7, a user who holds SYSADM authority no longer has implicit DBADM authority and therefore has limited capabilities compared to those available in Version 9.5.  

Symptom

Example authorization errors are SQL1092N, SQL0551N and SQL0552N:

SQL1092N

authorization-ID does not have the authority to perform the requested command or operation.

SQL0551N

authorization-ID does not have the required authorization or privilege to perform operation operation on object object-name.

SQL0552N

authorization-ID does not have the privilege to perform operation operation.
 

Cause

Prior to DB2 Version 9.7 a user with SYSADM authority had implicit DBADM authority. Common configurations have the instance owner account as a member of the SYSADM group. Applications that are run under the instance owner account had unlimited authority on the database.

In DB2 V9.7 a user with SYSADM authority no longer has implicit DBADM authority. As a result any applications that are run under the instance owner account might experience authorization errors when trying to perform operations that are no longer within the scope of a SYSADM.
 

Environment

The UPGRADE DATABASE command and the RESTORE DATABASE command (for a downlevel database) grant DBADM authority to the SYSADM group, however this is not the case for any new V9.7 database.

Resolving the problem

The resolution to the errors is to verify the authorities for the userid.

To get the list of authorities held by the instance owner authorization ID perform these steps:

1.       Use the SYSPROC.AUTH_GET_INSTANCE_AUTHID() scalar function to determine the instance owner authorization ID.
For example:

VALUES SYSPROC.AUTH_GET_INSTANCE_AUTHID()

1
-----------------------------------------------------------------
XXXXXX

1 record(s) selected.

2.       Get the list of authorities for this authorization ID.
For example:
SELECT * FROM
TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID (XXXXXX, 'U'))AS T
ORDER BY AUTHORITY

3.       Grant any missing authorities.
For example:
GRANT DBADM ON DATABASE TO USER XXXXXX

Connect to database using db2admin/password and then execute the above grant query.

4.       Execute a reorg for the entire database.

1.       Run the db2cmd

2.       Navigate to the WCInstall_Dir\bin

3.       Execute the following command

reorg.db2.bat databasename username password schemaname

Example:

reorg.db2.bat WCS7DB xxxxxx passw0rd XXXXXX

4.       Monitor the “reorgdb2.err.log” to ensure there are no errors.

Reference Link:

http://www-01.ibm.com/support/docview.wss?uid=swg21385801