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 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 Capturing SQL statements 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
  • User-defined SQL repository
  • 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.

Sources in DB2 for Linux, UNIX, and Windows
  • Package cache

    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.

    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

    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.

    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