Authorities and privileges that are required for capturing and tuning SQL statements and query workloads that run on DB2 for Linux, UNIX, and Windows

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
Privileges Schemas Object
EXECUTE SYSPROC 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
EXPLAIN_SQL
MON_GET_PKG_CACHE_STMT
SET_PLAN_HINT
SNAP_GET_DYN_SQL_V95
SYSINSTALLOBJECTS
SELECT SYSCAT 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
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 2. Privileges on objects that are created by InfoSphere Optim Query Workload Tuner
Privileges Schemas Objects Comments
EXECUTE DB2OE 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)
OQT WCC_EXPLAIN_SP stored procedure
SYSTOOLS EXPLAIN_GET_MSGS function
USAGE SYSTOOLS QT_WCC_TUNING_BATCH_ID sequence It is recommended that this privilege be granted by the same person who configures the database for tuning.
INSERT, UPDATE, DELETE, SELECT SYSTOOLS 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_WCC_ERROR_MESSAGE It is recommended that these privileges be granted by the same person who configures the database for tuning.
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

Feedback