Authorities and privileges 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 basic packages (DSN5OADM, DSN5OEPJ (DB2® for z/OS® Version 8), DSN5OEPK (DB2 for z/OS Versions 9 and 10), DSN5ONPT, DSN5OPKG)
  • 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 basic packages (DSN5OADM, DSN5OEPJ (DB2 for z/OS Version 8), DSN5OEPK (DB2 for z/OS Versions 9 and 10), DSN5ONPT, DSN5OPKG) and Query Advisor package (AOC5OQA)
Access path advisor 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)
Index advisor 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), Index Advisor package (AOC5OQIA ), and Workload Index Advisor packages (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 use the what-if function, 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