When performing a schema repair between two db2 databases I receive errors SQL 1092n permissions error and an "unable to read table space parts" error. The user is dbadmin. It does work for the sysadmin. This problem is only occurring on db2 v8.2.6 fix pack 13. It does not seem to have this problem on v8.1.5. The current user did not create the objects, but the user is dbadmin so it shouldn't matter.
Database A is on V8 FixPak 5
Database B is on V8 FixPak 5
Database C is on V8 FixPak 13
Schema compares between Database A and Database B succeed, even though the user running the compare has relatively few privileges on either database.
Comparing either database with Database C produces two permissions errors with the message "Unable to read tablespace parts" and a SQL1092N error, but the error does not specify which object was being accessed.
If I grant DBADM ON DATABASE to the particular user, the permissions errors still occur during a schema compare.
The only way I can get a schema compare to work with the V8.2 FixPak 13 database is to give Toad the instance owner's login to the database. So, on a V8.2 database, DBADM can't run a schema compare, but SYSADM can.
SNAP_GET_TBSP() UDF call failing due to insufficient privileges.
There is a failed attempt to execute SYSPROC.SNAP_GET_TBSP(). You must ensure that any user running a schema compare on V8.2 has SYSMON authority or higher. If SYSMON or other intermediate authority levels are not set up on the DB2 instance, they'll have to run the schema compare as a user SYSADM authority.
On V8.2 systems, the schema compare runs snapshot UDFs to grab the tablespace definitions. This requires minimum SYSMON authority, which needs to be explicitly set up on the instance. Granting DBADM to a user will not solve the problem because even users with DBADM cannot run the snapshot UDFs. Create a new OS group and tell DB2 that it was the group to use for SYSMON (via the SYSMON_GROUP)
This is why an instance owner (who has SYSADM) can preform the comparison, but a regular user with DBADM cannot.
Information about authorization is documented only for V9 http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.doc/doc/db2idxS.htm
To execute table functions it needs to have either of following privileges:
- SYSMON authority
- SELECT or CONTROL privilege on the SNAPTBSP administrative view and EXECUTE privilege on the SNAP_GET_TBSP_V91 table function.
For V8 it's not documented.
Before 9.1 we are using snap_get_tbsp and snap_get_tbsp_part.
For 9.1 and later, - snap_get_tbsp_v91 and snap_get_tbsp_part_v91