Authorities and privileges that are required for running single-query advisors on SQL statements that run on DB2 for z/OS

These authorities and privileges are required to run the single-query advisors.
Table 1. Authorities and privileges required to run the query-based advisors
Advisor Authorities or privileges required
Statistics advisor
  • To run the advisor, 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 the recommended RUNSTATS job, EXECUTE privilege on the SYSPROC.DSNUTILU stored procedure
  • To save recommended RUNSTATS jobs or retrieve previously saved jobs from the profile tables, EXECUTE privilege on the statistics advisor package (AOC5OSA)
Query advisor 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
and AOC5OQA
Access path advisor 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
Index advisor 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
    • AOC5OQIA
    • AOC5OIAn, where n={1-9, A-M}
  • SELECT, INSERT, UPDATE, and DELETE privileges on the DSOE.DSN_WIA_* tables
  • To create the recommended indexes, one of the following authorities or privileges:
    • INDEX privilege on the table
    • Ownership of the table
    • DBADM authority for the database that contains the table
    • SYSADM or SYSCTRL authority
  • To test candidate indexes that you or the Index Advisor proposes, one of the following authorities or privileges:
    • SYSADM privilege
    • You can also ensure that DB2OE is the secondary authorization ID of the logon user by creating a DB2OE RACF® group and linking the user to the group.

Feedback