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 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 that run on DB2 for z/OS 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.


Feedback