Locations from which you can capture SQL statements to create query workloads

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.

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:

  1. In the Data Source Explorer, right-click a connection and select Connect, expand the connection, right-click the data server, and select Analyze and Tune > Start Tuning. The workflow assistant opens to the Capture section.
  2. 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

Feedback