Authority Options for SQL Analysis and Tuning
This topic describes some authority options for SQL analysis and tuning.
Db2® for i has a rich set of commands, stored procedures, APIs, and tools for analysis and tuning of the performance aspects of database applications.
These tools are available to users with *JOBCTL special authority. In addition, there are function usage identifiers that provide an alternative authorization mechanism. These function usage identifiers are:
- QIBM_DB_SQLADM (Database Administrator tasks)
- QIBM_DB_SYSMON (Database Information tasks)
The Database Administrator (QIBM_DB_SQLADM) function usage ID allows analysis and viewing of SQL performance data. Some of the more common functions are displaying statements from the SQL Plan Cache, analyzing SQL Performance Monitors and SQL Plan Cache Snapshots, and displaying the SQL details of a job other than your own.
The Database Information (QIBM_DB_SYSMON) function usage ID allows the examination of high-level database properties. For example, a user without *JOBCTL special authority or authorization to the QIBM_DB_SQLADM function usage ID can view the SQL Plan Cache properties when authorized to the QIBM_DB_SYSMON function usage ID.
The following table describes some of the authorization rules related to SQL analysis and tuning.
| User Action | *JOBCTL | QIBM_DB_SQLADM | QIBM_DB_SYSMON | No Authority |
|---|---|---|---|---|
| SET CURRENT DEGREE (SQL statement) | Allowed | Allowed | Not allowed | Not allowed |
| CHGQRYA command targeting a different user's job | Allowed | Allowed | Not allowed | Not allowed |
| STRDBMON or ENDDBMON commands targeting a different user's job | Allowed | Allowed | Not allowed | Not allowed |
| STRDBMON or ENDDBMON commands targeting a job that matches the current user | Allowed | Allowed | Allowed | Allowed |
| QUSRJOBI() API format 900 | Allowed | Allowed | Allowed | Not allowed |
| Visual Explain within Run SQL Scripts | Allowed | Allowed | Allowed | Allowed |
| Visual Explain outside of Run SQL Scripts | Allowed | Allowed | Not allowed | Not allowed |
| DUMP_PLAN_CACHE procedure | Allowed | Allowed | Not allowed | Not allowed |
| DUMP_PLAN_CACHE_TOPN procedure | Allowed | Allowed | Not allowed | Not allowed |
|
DUMP_PLAN_CACHE_
PROPERTIES procedure |
Allowed | Allowed | Allowed | Not allowed |
| CHANGE_PLAN_CACHE_SIZE procedure | Allowed | Allowed | Not allowed | Not allowed |
|
START_PLAN_CACHE_EVENT_
MONITOR procedure |
Allowed | Allowed | Not allowed | Not allowed |
|
END_PLAN_CACHE_EVENT_
MONITOR procedure |
Allowed | Allowed | Not allowed | Not allowed |
|
END_ALL_PLAN_CACHE_EVENT_
MONITORS procedure |
Allowed | Allowed | Not allowed | Not allowed |