You can capture SQL statements from
a number of 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 subsystem
that the SQL statements run against is configured for tuning query
workloads.
Locations from which you can
capture SQL statements
- SQL editor or routine editor
- Highlight the statements that you want
to tune, right-click them, 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
- You can right-click an SQL stored
procedure and select Start Tuning.To capture from this source, you must have
the SELECT privilege on the system catalog.
- DB2® for z/OS®, and other locations
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.
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.
- 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 Creating query workloads that run on DB2 for z/OS from Optim Performance Manager.
- Sources in DB2 for z/OS
- Catalog plan or packages
f the package in which
a statement is located was bound with EXPLAIN set to YES, then it
is captured together with information about its actual access plan.
- DB2 Query Monitor for z/OS
- DSN_FUNCTION_TABLE table
- DSN_STATEMNT_TABLE table
- PLAN_TABLE table
- QMF
- QMF HPO
- SQL stored procedure
- Statement cache
Statements from this location are captured together
with their actual access plans.
- User-defined SQL repository
For the authorities and privileges that
are required for capturing from these sources, see Authorities and privileges that are
required for capturing SQL statements from DB2 for z/OS.