Authorities and privileges that are required for capturing SQL statements from DB2 for z/OS

Capturing SQL statements from locations in DB2 for z/OS requires authorization IDs to have the following authorities and privileges.
Catalog plan or packages
Your authorization ID must meet the following prerequisites to capture from this location:
  • SELECT privilege on the following tables:
    • SYSIBM.SYSPACKAGE
    • SYSIBM.SYSPACKLIST
    • SYSIBM.SYSPLAN
    • SYSIBM.SYSSTMT
    • SYSIBM.SYSPACKSTMT
    • SYSIBM.SYSPACKDEP
    • SYSIBM.SYSPLANDEP
  • EXECUTE privilege on the following packages
    • AOC5OADM
    • AOC5OEPK (DB2 for z/OS Versions 9 and 10)
    • AOC5ONPT
    • AOC5OPKG
    • AOC5OFMJ (DB2® for z/OS® Version 9)
    • AOC5OFMM (DB2 for z/OS Version 10)
    For query-workload tuning, EXECUTE privilege on these additional packages:
    • AOC5OWCJ (DB2 for z/OS Versions 10 CM8)
    • AOC5OWCK (DB2 for z/OS Version 10 CM9)
    • AOC5OWCM (DB2 for z/OS Version 10 NFM)
DB2 Query Monitor for z/OS
Ensure that the SQL statements that you want to capture are offloaded from DB2 Query Monitor for z/OS into DB2 tables in the connected DB2 subsystem. For information about offloading data from DB2 Query Monitor for z/OS, see Loading Query Monitor data to DB2.

Ensure that the DB2 Query Monitor for z/OS tables have their default names.

Ensure that your authorization ID has the SELECT privilege on the SYSIBM.SYSPACKSTMT table and the DB2 Query Monitor for z/OS tables.

Ensure that you have the EXECUTE privileges on these packages:
  • AOC5OADM
  • AOC5OEPK (DB2 for z/OS Versions 9 and 10)
  • AOC5ONPT
  • AOC5OPKG
  • AOC5OFMJ (DB2 for z/OS Version 9)
  • AOC5OFMM (DB2 for z/OS Version 10)
DSN_FUNCTION_TABLE table
Your authorization ID must meet the following prerequisites to capture from this location:
  • SELECT privilege on the following tables:
    • DSN_FUNCTION_TABLE
    • SYSIBM.SYSPACKAGE
    • SYSIBM.SYSPACKLIST
    • SYSIBM.SYSPLAN
    • SYSIBM.SYSSTMT
    • SYSIBM.SYSPACKSTMT
  • EXECUTE privilege on the following packages
    • AOC5OADM
    • AOC5OEPK (DB2 for z/OS Versions 9 and 10)
    • AOC5ONPT
    • AOC5OPKG
    • AOC5OFMJ (DB2 for z/OS Version 9)
    • AOC5OFMM (DB2 for z/OS Version 10)
    For query-workload tuning, EXECUTE privilege on these additional packages:
    • AOC5OWCJ (DB2 for z/OS Versions 10 CM8)
    • AOC5OWCK (DB2 for z/OS Version 10 CM9)
    • AOC5OWCM (DB2 for z/OS Version 10 NFM)
DSN_STATEMNT_TABLE table
Your authorization ID must meet the following prerequisites to capture from this location:
  • SELECT privilege on the following tables:
    • DSN_STATEMNT_TABLE
    • SYSIBM.SYSPACKAGE
    • SYSIBM.SYSPACKLIST
    • SYSIBM.SYSPLAN
    • SYSIBM.SYSSTMT
    • SYSIBM.SYSPACKSTMT
  • EXECUTE privilege on the following packages
    • AOC5OADM
    • AOC5OEPK (DB2 for z/OS Versions 9 and 10)
    • AOC5ONPT
    • AOC5OPKG
    • AOC5OFMJ (DB2 for z/OS Version 9)
    • AOC5OFMM (DB2 for z/OS Version 10)
    For query-workload tuning, EXECUTE privilege on these additional packages:
    • AOC5OWCJ (DB2 for z/OS Versions 10 CM8)
    • AOC5OWCK (DB2 for z/OS Version 10 CM9)
    • AOC5OWCM (DB2 for z/OS Version 10 NFM)
PLAN_TABLE table
Your authorization ID must meet the following prerequisites to capture from this location:
  • SELECT privilege on the following tables:
    • PLAN_TABLE
    • SYSIBM.SYSPACKAGE
    • SYSIBM.SYSPACKLIST
    • SYSIBM.SYSPLAN
    • SYSIBM.SYSSTMT
    • SYSIBM.SYSPACKSTMT
  • EXECUTE privilege on the following packages
    • AOC5OADM
    • AOC5OEPK (DB2 for z/OS Versions 9 and 10)
    • AOC5ONPT
    • AOC5OPKG
    • AOC5OFMJ (DB2 for z/OS Version 9)
    • AOC5OFMM (DB2 for z/OS Version 10)
    For query-workload tuning, EXECUTE privilege on these additional packages:
    • AOC5OWCJ (DB2 for z/OS Versions 10 CM8)
    • AOC5OWCK (DB2 for z/OS Version 10 CM9)
    • AOC5OWCM (DB2 for z/OS Version 10 NFM)
QMF
Your authorization ID must meet the following prerequisites to capture from this location:
  • SELECT privilege on the following tables:
    • Q.OBJECT_DIRECTORY
    • Q.OBJECT_DATA
    • Q.OBJECT_REMARKS
  • EXECUTE privilege on the following packages
    • AOC5OADM
    • AOC5OEPK (DB2 for z/OS Versions 9 and 10)
    • AOC5ONPT
    • AOC5OPKG
    • AOC5OFMJ (DB2 for z/OS Version 9)
    • AOC5OFMM (DB2 for z/OS Version 10)
    For query-workload tuning, EXECUTE privilege on these additional packages:
    • AOC5OWCJ (DB2 for z/OS Versions 10 CM8)
    • AOC5OWCK (DB2 for z/OS Version 10 CM9)
    • AOC5OWCM (DB2 for z/OS Version 10 NFM)
  • Select privileges on the Q.DSEQ_QMFOBJSL view (not required for SYSADM)
QMF HPO
Your authorization ID must meet the following prerequisites to capture from this location:
  • SELECT privilege on these tables:
    • Q.OBJ_ACTIVITY_DTL
    • Q.OBJ_ACTIVITY_SUMM
  • EXECUTE privilege on the following packages
    • AOC5OADM
    • AOC5OEPK (DB2 for z/OS Versions 9 and 10)
    • AOC5ONPT
    • AOC5OPKG
    • AOC5OFMJ (DB2 for z/OS Version 9)
    • AOC5OFMM (DB2 for z/OS Version 10)
    For query-workload tuning, EXECUTE privilege on these additional packages:
    • AOC5OWCJ (DB2 for z/OS Versions 10 CM8)
    • AOC5OWCK (DB2 for z/OS Version 10 CM9)
    • AOC5OWCM (DB2 for z/OS Version 10 NFM)
SQL stored procedure
To capture from this source, you must have the SELECT privilege on the system catalog.
Statement cache
Your authorization ID must meet the following prerequisites to capture from this location:
  • EXECUTE privilege on the EXPLAIN STMTCACHE ALL statement, either dynamically or through the SYSPROC.OPT_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.OPT_RUNSQL stored procedure, which explains all statements.
  • EXECUTE privilege on the following packages
    • AOC5OADM
    • AOC5OEPK (DB2 for z/OS Versions 9 and 10)
    • AOC5ONPT
    • AOC5OPKG
    • AOC5OFMJ (DB2 for z/OS Version 9)
    • AOC5OFMM (DB2 for z/OS Version 10)
    For query-workload tuning, EXECUTE privilege on these additional packages:
    • AOC5OWCJ (DB2 for z/OS Versions 10 CM8)
    • AOC5OWCK (DB2 for z/OS Version 10 CM9)
    • AOC5OWCM (DB2 for z/OS Version 10 NFM)
  • 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 SYSPROC.DSNWZP
  • EXECUTE privilege on ADMIN_COMMAND_DB2 or SYSPROC.DSNACCMD
  • The authorization ID is associated with the RACF® group DB2OSCA.
User-defined SQL repository
There are applications that can gather runtime metrics about the performance of SQL statements within specified intervals of time. These applications can offload the runtime metrics and the SQL statements into DB2 for z/OS tables. The applications also gather and can offload information about the database objects that were referenced by the SQL statements that ran during an interval.

Capturing SQL statements from such a repository requires that you first specify the columns that contain the required information. To learn what information is required and to see an example of capturing such statements, see Example of capturing SQL statements from a user-defined SQL repository.

You can find and select an SQL statement from DB2 for z/OS tables that a non-IBM query-monitoring application created.

The tables must be populated with the text of SQL statements, runtime metrics, and information about objects that the SQL statements reference.

Ensure that you have SELECT privileges on the tables that contain the text of SQL statements, runtime metrics, and information about objects that the SQL statements reference. If there is a view to join tables that contain the runtime metrics and the SQL text, ensure that you have the SELECT privilege on that view.

Ensure that you have the EXECUTE privileges on these packages:
  • AOC5OADM
  • AOC5OEPK (DB2 for z/OS Versions 9 and 10)
  • AOC5ONPT
  • AOC5OPKG
  • AOC5OFMJ (DB2 for z/OS Version 9)
  • AOC5OFMM (DB2 for z/OS Version 10)

Feedback