Authorities and privileges required for capturing SQL statements that run on DB2 for z/OS

The following table lists the authorities and privileges required for capturing and viewing SQL SQL statements from different sources.
Table 1. Authorities and privileges required to view individual SQL statements in from different sources
Query source Authorities or privileges required
Catalog plan, or package All of the following authorities and privileges:
  • SELECT privilege on the following tables:
    • SYSIBM.SYSPACKAGE
    • SYSIBM.SYSPACKLIST
    • SYSIBM.SYSPLAN
    • SYSIBM.SYSSTMT
    • SYSIBM.SYSPACKSTMT
    • SYSIBM.SYSPACKDEP
    • SYSIBM.SYSPLANDEP
  • EXECUTE privilege on the basic packages (DSN5OADM, DSN5OEPJ (DB2® for z/OS® Version 8), DSN5OEPK (DB2 for z/OS Versions 9 and 10), DSN5ONPT, DSN5OPKG)
SQL category None
File None
Function table All of the following authorities and privileges:
  • SELECT privilege on the following tables:
    • DSN_FUNCTION_TABLE
    • SYSIBM.SYSPACKAGE
    • SYSIBM.SYSPACKLIST
    • SYSIBM.SYSPLAN
    • SYSIBM.SYSSTMT
    • SYSIBM.SYSPACKSTMT
  • EXECUTE privilege on the basic packages (DSN5OADM, DSN5OEPJ (DB2 for z/OS Version 8), DSN5OEPK (DB2 for z/OS Versions 9 and 10), DSN5ONPT, DSN5OPKG)
Plan table All of the following authorities and privileges:
  • SELECT privilege on the following tables:
    • PLAN_TABLE
    • SYSIBM.SYSPACKAGE
    • SYSIBM.SYSPACKLIST
    • SYSIBM.SYSPLAN
    • SYSIBM.SYSSTMT
    • SYSIBM.SYSPACKSTMT
  • EXECUTE privilege on the basic packages (DSN5OADM, DSN5OEPJ (DB2 for z/OS Version 8), DSN5OEPK (DB2 for z/OS Versions 9 and 10), DSN5ONPT, DSN5OPKG)
QMF™ All of the following authorities and privileges:
  • SELECT privilege on the following tables:
    • Q.OBJECT_DIRECTORY
    • Q.OBJECT_DATA
    • Q.OBJECT_REMARKS
  • EXECUTE privilege on the basic packages (DSN5OADM, DSN5OEPJ (DB2 for z/OS Version 8), DSN5OEPK (DB2 for z/OS Versions 9 and 10), DSN5ONPT, DSN5OPKG)
  • Select privileges on the Q.DSEQ_QMFOBJSL view (not required for SYSADM)
QMF HPO All of the following authorities and privileges:
  • SELECT privilege on the Q.OBJ_ACTIVITY_SUMM and Q.OBJ_ACTIVITY_DTL tables
  • EXECUTE privileges on the basic packages (DSN5OADM, DSN5OEPJ (DB2 for z/OS Version 8), DSN5OEPK (DB2 for z/OS Versions 9 and 10), DSN5ONPT, DSN5OPKG)
SQL procedure SELECT privilege on the system catalog.
SQL user-defined function SELECT privilege on the system catalog.
Statement cache All of the following authorities and privileges:
  • EXECUTE privilege on the EXPLAIN STMTCACHE ALL statement, either dynamically or through the SYSPROC.OSC_RUNSQL stored procedure:
    • Dynamically: SYSADM authority is needed to run EXPLAIN for all statements, otherwise only statements with the same authorization ID can be explained.
    • By stored procedure: EXECUTE privilege is required for the SYSPROC.OSC_RUNSQL stored procedure, which explains all statements.
  • Ownership of DSN_STATEMENT_CACHE_TABLE, or ownership of an alias on owner.DSN_STATEMENT_CACHE_TABLE, and SELECT, INSERT and DELETE privileges on the table.
  • EXECUTE privilege on the basic packages (DSN5OADM, DSN5OEPJ (DB2 for z/OS Version 8), DSN5OEPK (DB2 for z/OS Versions 9 and 10), DSN5ONPT, DSN5OPKG)
  • Optional: To get statistics information (such as CPU time and elapsed time) EXECUTE privilege on the SYSPROC.ADMIN_COMMAND_DB2 stored procedure is needed to start and stop the trace.
Statement table All of the following authorities and privileges:
  • SELECT privilege on the following tables:
    • DSN_STATEMNT_TABLE
    • SYSIBM.SYSPACKAGE
    • SYSIBM.SYSPACKLIST
    • SYSIBM.SYSPLAN
    • SYSIBM.SYSSTMT
    • SYSIBM.SYSPACKSTMT
  • EXECUTE on the basic packages (DSN5OADM, DSN5OEPJ (DB2 for z/OS Version 8), DSN5OEPK (DB2 for z/OS Versions 9 and 10), DSN5ONPT, DSN5OPKG)
Text None.
Trigger SELECT privilege on the system catalog.
View SELECT privilege on the system catalog.

Feedback