You can capture SQL statements from
the following locations.
Common prerequisites
for capturing
For each location
that you capture from, you must meet these prerequisites. Some locations
have additional prerequisites.
- In the Data Source Explorer, connect to
the DB2 database or subsystem, if the connection is not already open.
- Ensure that the DB2 database or
subsystem that the SQL statements run against is configured for tuning
single SQL statements.
Locations outside the workflow assistant
You can capture SQL statements from these locations
without opening the workflow assistant first.
- SQL editor or routine editor
- Right-click anywhere in the editor and select Start
Tuning.
Note: If a statement that you capture from a routine
editor contains local variables, those variables are converted to
typed parameter markers during the capture process.
- Data Source Explorer
- Right-click any of these objects
and select Start Tuning.
Restriction: To capture from these sources on DB2 for z/OS,
you must have the SELECT privilege on the system catalog.
- Package
- SQL stored procedure
- Trigger that uses compiled SQL statements; in other words, a trigger
that is associated with a package
- User-defined function with compiled SQL statements; in other words,
a UDF that is associated with a package
- View
Locations inside the workflow assistant
To capture SQL statements from the following sources,
you must first follow these steps to open the workflow assistant:
- In the Data Source Explorer, right-click a connection and select Connect,
expand the connection, right-click the data server, and select . The workflow assistant opens to the Capture section.
- On the left side of the Capture section,
select the location of the SQL statement that you want to tune. Follow
the instructions in the page that opens. You can click the ? icon
to the right of the title for information about the page.
You can capture from the following locations:- Type or paste in a statement
- Select Input Text on the left side of the Capture section.
- File
- Select File on the left side of the Capture section.
- SQL category
Important: The SQL Category view is deprecated in
IBM Data Studio full client and administration client, Version 3.1.1.
By
using SQL categories, you can organize and save SQL statements that
you want to return to later for tuning. SQL categories do not behave
like Query Tuner projects, however. SQL categories do not save any
of the work that you do in the workflow assistant.
- To open the SQL Category view, select . In the Show View window, expand Query
Tuner and select SQL Category.
- XML file that defines a query workload
- Even if the database or subsystem that you are connected to does
not have an active license for tuning query workloads, you can tune
an SQL statement that is within the definition of a query workload.
- Optim™ Performance Manager
repository
- For prerequisites and instructions for capturing from this source,
see Capturing SQL statements from Optim Performance Manager.
- Sources in DB2 for z/OS
- 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 basic packages:
- AOC5OADM
- AOC5OEPJ (DB2 for z/OS Version 8)
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- 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
- AOC5OEPJ (DB2 for z/OS Version 8)
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- 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 basic packages:
- AOC5OADM
- AOC5OEPJ (DB2 for z/OS Version 8)
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- 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 basic packages:
- AOC5OADM
- AOC5OEPJ (DB2 for z/OS Version 8)
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- 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 basic packages:
- AOC5OADM
- AOC5OEPJ (DB2 for z/OS Version 8)
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- 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 basic packages:
- AOC5OADM
- AOC5OEPJ (DB2 for z/OS Version 8)
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- 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 basic packages:
- AOC5OADM
- AOC5OEPJ (DB2 for z/OS Version 8)
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- 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.OSC_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.OSC_RUNSQL
stored procedure, which explains all statements.
- EXECUTE privilege on the basic packages:
- AOC5OADM
- AOC5OEPJ (DB2 for z/OS Version 8)
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- 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.
- 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
- AOC5OEPJ (DB2 for z/OS Version 8)
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- Sources in DB2 for Linux,
UNIX, and Windows
- Package cache
To capture from this source, you must have the
EXECUTE privilege on the user-defined function SYSPROC.MON_GET_PKG_CACHE_STMT.
If
you are using DB2 for Linux, UNIX, and Windows, Version 9.5, you must
have SYSMON authority. You must also have EXECUTE privilege on the
stored procedure SYSPROC.SNAP_GET_DYN_SQL_V95.
If you are using
DB2 for Linux, UNIX, and Windows, Version 9.7 Fix Pack 1 or later,
you must have the EXECUTE privilege on the stored procedure SYSPROC.EXPLAIN_FROM_SECTION.
- Packages
- SQL stored procedures