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

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.

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.

For DB2 for Linux, UNIX, and Windows, Version 9.7.4 or later: When you run an SQL stored procedure in a routine editor, in the Run window you can collect actual runtime information about the access plans for the statements in the stored procedure. Select the option Gather performance information from the database, and then select the option Gather actual access plan information from the database. In the Profiling Data view, select the statements that you want to tune by holding the CTRL key and clicking those statements. Right-click the selection and click Start Tuning.

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
DB2® for Linux, UNIX, and Windows and other locations

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.
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.
InfoSphere® 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

    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.

    All occurrences of captured statements in the specified packages are saved when you create a query workload from this type of source. If you select the option Save EXPLAIN information for the captured statements, the EXPLAIN information for each occurrence of a statement is also saved.

  • 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.

    All occurrences of captured statements in the specified packages are saved when you create a query workload from this type of source. If you select the option Save EXPLAIN information for the captured statements, the EXPLAIN information for each occurrence of a statement is also saved.

  • EXPLAIN tables

    Immediately after capturing, you can view the actual access plans for a statement by right-clicking it and selecting Show Access Plan Graph.

    For static statements: If captured statements occur more than once in the specified packages, then only the EXPLAIN information for the latest occurrence of the statement is saved when you create a query workload from this type of source. Statements are considered duplicates when the package name, package schema, section number, statement number, statement text are identical. Even if you do not select this option, only the latest occurrence of each statement is saved into the query workload.

  • Event monitor tables

    Capturing from this type of source requires first creating and running an ACTIVITIES event monitor to collect runtime activity on a database. Immediately after capturing, you can view the actual access plans for a statement by right-clicking it and selecting Show Access Plan Graph.

    If captured statements occur more than once in the specified packages, then only the EXPLAIN information for the latest occurrence of the statement is saved when you create a query workload from this type of source. Statements are considered duplicates when the executable IDs are identical. Even if you do not select this option, only the latest occurrence of each statement is saved into the query workload.

  • SQL stored procedures

Feedback