Granting authorities for monitoring and tuning SQL statements

You can enable the monitoring and tuning of SQL statements and SQL workloads, without providing additional privileges, such as access to data in the tables that are accessed by the statements.

Before you begin

Your authorization ID or role must have one of the following authorities or privileges:

About this task

You might want to enable a production database administrator, performance analyst, or application developer to complete monitoring and tuning tasks for SQL statements, without giving them access to data in the tables referenced by the statements. The EXPLAIN privilege enables certain EXPLAIN tasks without giving any privileges to access or modify the data. SQLADM authority enables additional monitoring and tuning capabilities, including those enabled by the EXPLAIN privileges, again without providing access to the data in the tables.

Procedure

To enable a user to monitor and tune SQL statements, without access to the data:

  • Enable the EXPLAIN privilege, by taking one of the following actions:
    • Issue a GRANT statement:
      GRANT EXPLAIN TO authorization-ID
    • Use RACF® to permit access to the EXPLAIN system resource.
    The EXPLAIN privilege enables you to:
    • Issue EXPLAIN statements, including PLAN and ALL, without privileges to execute the SQL statements.
    • Run EXPLAIN for dynamic statements that execute under the CURRENT EXPLAIN MODE = EXPLAIN special register
    • Issue PREPARE statements and DESCRIBE TABLE statements, without privileges for the objects.
    • Issue BIND and REBIND commands, and specify the EXPLAIN(ONLY) and SQLERROR(CHECK) options.
  • Enable SQLADM authority by issuing a GRANT statement:
    SQLADM authority enables you to:
    • Issue EXPLAIN statements, including PLAN, ALL, STMTCACHE ALL, STMTID, STMTTOKEN, and MONITORED STMTS, without privileges to execute the SQL statements.
    • Run EXPLAIN for dynamic statements that execute under the CURRENT EXPLAIN MODE = EXPLAIN special register
    • Issue PREPARE statements and DESCRIBE TABLE statements, without privileges for the objects.
    • Issue BIND and REBIND commands, and specify the EXPLAIN(ONLY) and SQLERROR(CHECK) options.
    • Issue START PROFILE, STOP PROFILE, and DISPLAY PROFILE commands.
    • Run the following utilities:
      • DIAGNOSE
      • LISTDEF
      • MODIFY STATISTICS
      • RUNSTATS
      • DSN1SDMP
    • Execute system-defined routines, including stored procedures and functions, and any packages that are defined within the routines.
    • Select data from all catalog tables, and modify data in updatable catalog tables (except for the SYSIBM.SYSAUDITPOLICIES table).