Locations from which you can capture SQL statements to create query workloads that run on DB2 for z/OS

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.
Data Source Explorer
You can right-click an SQL stored procedure and select Start Tuning.To capture from this source, you must have the SELECT privilege on the system catalog.
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 Creating query workloads that run on DB2 for z/OS 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

    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.


Feedback