Certain authorities and privileges are required for capturing
and tuning SQL statements that run on DB2® for Linux, UNIX, and Windows.
To capture SQL statements from an InfoSphere® Optim™ Performance
Manager repository, a user ID must be authorized to access the performance
metrics that are stored in the repository database. To be authorized,
the user ID must be a member of the user group that was authorized
for accessing the repository database. This authorization takes place
when InfoSphere Optim Performance Manager is installed.
A
user ID must have the following privileges on the listed system objects.
Table 1. Privileges that are required on system objects that are in
the SYSPROC schema| Privileges |
Object |
| EXECUTE |
ADMIN_CMD |
| AUTH_LIST_AUTHORITIES_FOR_AUTHID |
| DESIGN_ADVISOR
(V9.7.5 or later) |
| EXPLAIN_FROM_ACTIVITY |
| EXPLAIN_FROM_CATALOG |
| EXPLAIN_FROM_SECTION |
| EXPLAIN_GET_MSG2 |
| MON_GET_PKG_CACHE_STMT |
| SET_PLAN_HINT |
| SNAP_GET_DYN_SQL_V95 |
| SYSINSTALLOBJECTS |
| SYSPROC.COMPILATION_ENV |
Table 2. Privileges that are required on system objects
that are in the SYSCAT schema| Privileges |
Object |
| SELECT |
BUFFERPOOLS |
| COLDIST |
| COLGROUPCOLS |
| COLGROUPS |
| COLUMNS |
| DATAPARTITIONEXPRESSION |
| DATAPARTITIONS |
| DATATYPE |
| DBPARTITIONGROUPDEF |
| EVENTMONITORS |
| EVENTS |
| EVENTTABLES |
| FUNCTIONS |
| INDEXCOLUSE |
| INDEXES |
| INDEXPARTITIONS |
| KEYCOLUSE |
| PACKAGEDEP |
| PACKAGES |
| PROCEDURES |
| REFERENCES |
| ROUTINEDEP |
| ROUTINES |
| SEQUENCEAUTH |
| STATEMENTS |
| TABAUTH |
| TABCONST |
| TABDEP |
| TABLES |
| TABLESPACES |
| TRIGDEP |
| VIEWDEP |
| VIEWS |
Table 3. Other privileges that are required on system
objects| Privileges |
Schemas |
Object |
| SELECT |
SYSIBM |
SYSCOLUMNS |
| SYSDUMMY1 |
| SYSJAROBJECTS |
| SYSSEQUENCES |
| SYSTABLES |
| SYSIBMADM |
DBCFG |
| SYSSTAT |
COLDIST |
| TABLES |
A user ID must have the following privileges on the objects
that InfoSphere Optim Query Workload Tuner creates
when a database is configured for tuning.
Table 4. Privileges
on tables that are created by InfoSphere Optim Query Workload Tuner in the
SYSTOOLS schema| Privileges |
Objects |
Comments |
| INSERT, UPDATE, DELETE, SELECT |
OPT_PROFILE |
When a database is configured for tuning, InfoSphere Optim Query Workload Tuner grants the INSERT,
UPDATE, DELETE, and SELECT privileges on this object to PUBLIC. |
| QT_WAPC_SESSION |
It is recommended that these
privileges be granted by the same person who configures the database
for tuning. |
| QT_WAPC_SESSION_EXCEPTIONS |
| QT_WAPC_SESSION_RESULT |
| QT_WCC_ERROR_MESSAGE |
| QT_WCC_STMT_DEPENDENT_OBJECT |
| QT_WCC_STMT_EXPLAIN_INFO |
| QT_WCC_STMT_INSTANCE |
| QT_WCC_STMT_RUNTIME_METRICS |
| QT_WCC_STMT_TEXT |
| QT_WCC_TEMP_CAPTURE |
| QT_WCC_TEMP_EXPLAIN_HANDLE |
| QT_WCC_WORKLOAD |
| QT_WCC_WORKLOAD_ADVISOR_INFO |
| QT_WCC_WORKLOAD_NAME |
| QT_WCC_WORKLOAD_SOURCE |
| QT_WCC_WORKLOAD_SOURCE_FILTER |
| QT_WCC_WORKLOAD_TASK |
| EXPLAIN tables created by InfoSphere Optim Query Workload Tuner |
Table 5. Privileges on other objects that are created
by InfoSphere Optim Query Workload Tuner in the
SYSTOOLS schema| Privileges |
Objects |
Comments |
| EXECUTE |
QT_LIC function |
When a database is configured
for tuning, InfoSphere Optim Query Workload Tuner grants
the EXECUTE privilege on these objects to PUBLIC. |
| CALLDB2ADVIS
stored procedure (DB2 for Linux, UNIX, or Windows,
Version 9.7.4 or older) |
| WCC_EXPLAIN_SP
stored procedure |
| EXPLAIN_GET_MSGS
function |
| USAGE |
QT_WCC_TUNING_BATCH_ID
sequence |
It is recommended that this privilege be
granted by the same person who configures the database for tuning. |