Authorities and privileges that are required for tuning 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 following packages:
  • AOC5OADM
  • AOC5OEPJ (DB2® for z/OS® Version 8)
  • AOC5OEPK (DB2 for z/OS Versions 9 and 10)
  • AOC5ONPT
  • AOC5OPKG
Access plan graph All of the following authorities and privileges:
  • SELECT privilege for all EXPLAIN tables
  • EXECUTE privilege on the following packages:
    • AOC5OADM
    • AOC5OEPJ (DB2 for z/OS Version 8)
    • AOC5OEPK (DB2 for z/OS Versions 9 and 10)
    • AOC5ONPT
    • AOC5OPKG
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 following packages:
    • AOC5OADM
    • AOC5OEPJ (DB2 for z/OS Version 8)
    • AOC5OEPK (DB2 for z/OS Versions 9 and 10)
    • AOC5ONPT
    • AOC5OPKG
  • 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 following packages:
    • AOC5OADM
    • AOC5OEPJ (DB2 for z/OS Version 8)
    • AOC5OEPK (DB2 for z/OS Versions 9 and 10)
    • AOC5ONPT
    • AOC5OPKG
  • EXECUTE privileges on the following packages:
    • AOC5OFMJ (DB2 for z/OS Versions 8 and 9)
    • AOC5OFMM (DB2 for z/OS Version 10)
Summary reports All of the authorities and privileges that are described here: Authorities and privileges that are required for running the Statistics Advisor on SQL statements that run on DB2 for z/OS

Feedback