Creating query workloads from SQL statements that are in user-defined SQL repositories

You can create query workloads from SQL statements from DB2® for z/OS® tables that a non-IBM query-monitoring application created and populated with the text of SQL statements, runtime metrics, and information about objects that the SQL statements reference.

Before you begin

About this task

There are applications that can gather runtime metrics about the performance of SQL statements within specified intervals of time. These applications can offload the runtime metrics and the SQL statements into DB2 for z/OS tables. The applications also gather and can offload information about the database objects that were referenced by the SQL statements that ran during an interval.

Capturing SQL statements from such a repository requires that you first specify the columns that contain the required information. To learn what information is required and to see an example of capturing such statements, see Example of creating a query workload from a user-defined SQL repository.

Procedure

To create a query workload from SQL statements in a user-defined SQL repository:

  1. In the Capture section of the workflow assistant, select User-defined SQL repository under DB2 for z/OS Sources. The Capture SQL from User-defined SQL Repository page opens. If you need help with a section of this page, click the Help button for that section.
  2. Create a filter, or select an existing filter, that sets criteria for how to find the SQL statements that you want to tune.
  3. Create a filter, or select a saved filter, that specifies the columns that contain the required information and sets criteria for finding the SQL statement that you want.
  4. Click Capture.
  5. Right-click in the table in the Captured Statements section and select Save All Statements as a Workload.

Results

The workflow assistant for query tuning creates the query workload and lists it on the Manage and Tune Workloads page of the Manage section.

What to do next

Collect EXPLAIN information about the SQL statements that are in the query workload.

Feedback