Locations from which you can capture an SQL statement

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

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.
Restriction: To capture from these sources on DB2 for z/OS, you must have the SELECT privilege on the system catalog.
  • 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, 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:

  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.
Optim™ Performance Manager repository
For prerequisites and instructions for capturing from this source, see Capturing SQL statements 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.

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