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 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.
- 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.
- 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 from Optim Performance Manager.
- 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