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

These authorities and privileges are required to use query analysis tools.
Table 1. Authorities and privileges that are required to use various query analysis tools
Query analysis tool Authorities or privileges required
All tools Depending on the situation, you might need:
  • EXECUTE privileges on the SYSPROC.DSNAEXP, to collect EXPLAIN information about SQL statements by using a stored procedure.
  • SYSPROC.DSNAHVPM, to collect EXPLAIN information about static SQL statements that contain host variables.
  • SET CURRENT SQLID to the SQLID that runs the query.
Access path reports 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)
Access plan graph All of the following authorities and privileges:
  • SELECT privilege for all EXPLAIN tables
  • 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)
To run EXPLAIN, one of the following authorities or privileges:
  • Own a plan table that is named PLAN_TABLE
  • Have an alias on a plan table that is named owner.PLAN_TABLE and have SELECT and INSERT privileges on the table
Capture query environment All of the following authorities and privileges:
  • 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 privilege on catalog tables
  • SELECT privilege on all EXPLAIN tables
For stored procedures, one or more of the following privileges:
  • EXECUTE privilege on the SYSPROC.DSNWZP stored procedure
  • EXECUTE privilege on the SYSPROC.DSNACCMD stored procedure.
Query annotation 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) and query annotation packages (DSN5OFMJ (DB2 for z/OS Versions 8 and 9), DSN5OFMM (DB2 for z/OS Version 10))
Summary reports All of the authorities and privileges that are described here: Authorities and privileges required for running single-query advisors on SQL statements that run on DB2 for z/OS
Visual plan hint All of the following authorities and privileges:
  • SELECT, INSERT, and DELETE privileges on PLAN_TABLE
  • EXECUTE on the basic packages (DSN5OADM, DSN5OEPJ (DB2 for z/OS Version 8), DSN5OEPK (DB2 for z/OS Versions 9 and 10), DSN5ONPT, DSN5OPKG)
  • The OPTIMIZATION HINT subsystem parameter on the DSNTIP8 installation panel must be set to YES.

Feedback