Authorities and privileges required for capturing SQL statements for workloads

These authorities and privileges are required for capturing SQL statements from different sources to create query workloads.
Table 1. Authorities and privileges required to capture SQL statements to workloads from various sources
Workload source Authorities and privileges required
All sources 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), query annotation packages (DSN5OFMJ (DB2 for z/OS Versions 8 and 9), DSN5OFMM (DB2 for z/OS Version 10)), and Workload Control Center packages (AOC5OWCJ (DB2 for z/OS Versions 8 NFM and 10 CM8), AOC5OWCK (DB2 for z/OS Version 10 CM9), AOC5OWCM (DB2 for z/OS Version 10 NFM))
Statement cache All of the following authorities and privileges:
  • EXECUTE privilege on SYSPROC.DSNWZP
  • EXECUTE privilege on ADMIN_COMMAND_DB2 or SYSPROC.DSNACCMD
  • For stored procedures, EXECUTE privilege on SYSPROC.OPT_RUN_SQL and SYSPROC.OPT_EXECUTE_TASK
  • The authorization ID is associated with the RACF® group DB2OSCA.
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)
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)

Feedback