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 full set of tuning features is not activated on the
database or subsystem that you are connected to, 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 plans or packages
If 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
If you
save captured statements from the statement cache as a query workload,
you can save runtime access path information for statements that were
already executed. When you first run workload advisors on the query
workload, those advisors can use that runtime information when generating
recommendations. If you choose to tune a single SQL statement from
the query workload, do not re-explain the statement if you want the
advisors to use the runtime information for it.
- 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.