Capturing SQL statements from locations in DB2® for z/OS® requires
authorization IDs to have the following authorities and privileges.
- Catalog plan or packages
- Your authorization ID must meet the
following prerequisites to capture from this location:
- SELECT privilege on the following tables:
- SYSIBM.SYSPACKAGE
- SYSIBM.SYSPACKLIST
- SYSIBM.SYSPLAN
- SYSIBM.SYSSTMT
- SYSIBM.SYSPACKSTMT
- SYSIBM.SYSPACKDEP
- SYSIBM.SYSPLANDEP
- EXECUTE privilege on the following
packages
- AOC5OADM
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- AOC5OFMJ (DB2 for z/OS Version 9)
- AOC5OFMM (DB2 for z/OS Version 10)
For query-workload tuning, EXECUTE privilege on these additional
packages:- AOC5OWCJ (DB2 for z/OS Versions 10 CM8)
- AOC5OWCK (DB2 for z/OS Version 10 CM9)
- AOC5OWCM (DB2 for z/OS Version 10 NFM)
- DB2 Query Monitor for z/OS
- Ensure that the SQL statements that you want to capture are offloaded
from DB2 Query Monitor for z/OS into DB2 tables in the connected DB2 subsystem. For information about offloading
data from DB2 Query Monitor
for z/OS, see Loading Query Monitor data to DB2.
Ensure that the DB2 Query Monitor for z/OS tables have their default names.
Ensure
that your authorization ID has the SELECT privilege on the SYSIBM.SYSPACKSTMT
table and the DB2 Query Monitor
for z/OS tables.
Ensure that you have the EXECUTE privileges
on these packages:- AOC5OADM
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- AOC5OFMJ (DB2 for z/OS Version 9)
- AOC5OFMM (DB2 for z/OS Version 10)
- DSN_FUNCTION_TABLE table
- Your authorization ID must meet the
following prerequisites to capture from this location:
- SELECT privilege on the following tables:
- DSN_FUNCTION_TABLE
- SYSIBM.SYSPACKAGE
- SYSIBM.SYSPACKLIST
- SYSIBM.SYSPLAN
- SYSIBM.SYSSTMT
- SYSIBM.SYSPACKSTMT
- EXECUTE privilege on the following
packages
- AOC5OADM
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- AOC5OFMJ (DB2 for z/OS Version 9)
- AOC5OFMM (DB2 for z/OS Version 10)
For query-workload tuning, EXECUTE privilege on these additional
packages:- AOC5OWCJ (DB2 for z/OS Versions 10 CM8)
- AOC5OWCK (DB2 for z/OS Version 10 CM9)
- AOC5OWCM (DB2 for z/OS Version 10 NFM)
- DSN_STATEMNT_TABLE table
- Your authorization ID must meet the
following prerequisites to capture from this location:
- SELECT privilege on the following tables:
- DSN_STATEMNT_TABLE
- SYSIBM.SYSPACKAGE
- SYSIBM.SYSPACKLIST
- SYSIBM.SYSPLAN
- SYSIBM.SYSSTMT
- SYSIBM.SYSPACKSTMT
- EXECUTE privilege on the following
packages
- AOC5OADM
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- AOC5OFMJ (DB2 for z/OS Version 9)
- AOC5OFMM (DB2 for z/OS Version 10)
For query-workload tuning, EXECUTE privilege on these additional
packages:- AOC5OWCJ (DB2 for z/OS Versions 10 CM8)
- AOC5OWCK (DB2 for z/OS Version 10 CM9)
- AOC5OWCM (DB2 for z/OS Version 10 NFM)
- PLAN_TABLE table
- Your authorization ID must meet the
following prerequisites to capture from this location:
- SELECT privilege on the following tables:
- PLAN_TABLE
- SYSIBM.SYSPACKAGE
- SYSIBM.SYSPACKLIST
- SYSIBM.SYSPLAN
- SYSIBM.SYSSTMT
- SYSIBM.SYSPACKSTMT
- EXECUTE privilege on the following
packages
- AOC5OADM
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- AOC5OFMJ (DB2 for z/OS Version 9)
- AOC5OFMM (DB2 for z/OS Version 10)
For query-workload tuning, EXECUTE privilege on these additional
packages:- AOC5OWCJ (DB2 for z/OS Versions 10 CM8)
- AOC5OWCK (DB2 for z/OS Version 10 CM9)
- AOC5OWCM (DB2 for z/OS Version 10 NFM)
- QMF™
- Your authorization ID must meet the
following prerequisites to capture from this location:
- SELECT privilege on the following tables:
- Q.OBJECT_DIRECTORY
- Q.OBJECT_DATA
- Q.OBJECT_REMARKS
- EXECUTE privilege on the following
packages
- AOC5OADM
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- AOC5OFMJ (DB2 for z/OS Version 9)
- AOC5OFMM (DB2 for z/OS Version 10)
For query-workload tuning, EXECUTE privilege on these additional
packages:- AOC5OWCJ (DB2 for z/OS Versions 10 CM8)
- AOC5OWCK (DB2 for z/OS Version 10 CM9)
- AOC5OWCM (DB2 for z/OS Version 10 NFM)
- Select privileges on the Q.DSEQ_QMFOBJSL view (not required for
SYSADM)
- QMF HPO
- Your authorization ID must meet the
following prerequisites to capture from this location:
- SELECT privilege on these tables:
- Q.OBJ_ACTIVITY_DTL
- Q.OBJ_ACTIVITY_SUMM
- EXECUTE privilege on the following
packages
- AOC5OADM
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- AOC5OFMJ (DB2 for z/OS Version 9)
- AOC5OFMM (DB2 for z/OS Version 10)
For query-workload tuning, EXECUTE privilege on these additional
packages:- AOC5OWCJ (DB2 for z/OS Versions 10 CM8)
- AOC5OWCK (DB2 for z/OS Version 10 CM9)
- AOC5OWCM (DB2 for z/OS Version 10 NFM)
- SQL stored procedure
- To capture from this source, you must have the SELECT privilege
on the system catalog.
- Statement cache
- Your authorization ID must meet the
following prerequisites to capture from this location:
- EXECUTE privilege on the EXPLAIN STMTCACHE ALL statement, either
dynamically or through the SYSPROC.OPT_RUNSQL stored procedure:
- Dynamically: SYSADM authority is needed to run EXPLAIN for all
statements; otherwise, only statements with the same authorization
ID can be explained.
- By stored procedure: EXECUTE privilege is required for the SYSPROC.OPT_RUNSQL
stored procedure, which explains all statements.
- EXECUTE privilege on the following
packages
- AOC5OADM
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- AOC5OFMJ (DB2 for z/OS Version 9)
- AOC5OFMM (DB2 for z/OS Version 10)
For query-workload tuning, EXECUTE privilege on these additional
packages:- AOC5OWCJ (DB2 for z/OS Versions 10 CM8)
- AOC5OWCK (DB2 for z/OS Version 10 CM9)
- AOC5OWCM (DB2 for z/OS Version 10 NFM)
- Ownership of DSN_STATEMENT_CACHE_TABLE, or ownership of an alias
on owner.DSN_STATEMENT_CACHE_TABLE, and SELECT,
INSERT and DELETE privileges on the table.
- EXECUTE privilege on SYSPROC.DSNWZP
- EXECUTE privilege on ADMIN_COMMAND_DB2 or SYSPROC.DSNACCMD
- The authorization ID is associated with the RACF® group DB2OSCA.
- User-defined SQL repository
- There are applications that can gather runtime metrics about the
performance of SQL statements within specified intervals of time.
These applications can offload the runtime metrics and the SQL statements
into DB2 for z/OS tables. The applications also gather and
can offload information about the database objects that were referenced
by the SQL statements that ran during an interval.
Capturing SQL
statements from such a repository requires that you first specify
the columns that contain the required information. To learn what information
is required and to see an example of capturing such statements, see
Example of capturing SQL statements from a user-defined SQL repository.
You
can find and select an SQL statement from DB2 for z/OS tables
that a non-IBM query-monitoring application created.
The tables
must be populated with the text of SQL statements, runtime metrics,
and information about objects that the SQL statements reference.
Ensure
that you have SELECT privileges on the tables that contain the text
of SQL statements, runtime metrics, and information about objects
that the SQL statements reference. If there is a view to join tables
that contain the runtime metrics and the SQL text, ensure that you
have the SELECT privilege on that view.
Ensure that you have the EXECUTE privileges
on these packages:- AOC5OADM
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- AOC5OFMJ (DB2 for z/OS Version 9)
- AOC5OFMM (DB2 for z/OS Version 10)