Capturing SQL statements from user-defined SQL repositories

Use the Capture SQL from User-defined SQL Repositories page to find and select an SQL statement from DB2® for z/OS® tables that a non-IBM query-monitoring application created. The tables must be 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 capturing SQL statements from a user-defined SQL repository.

Procedure

To capture an SQL statement from a user-defined SQL repository:

  1. In the Data Source Explorer, right-click the DB2 for z/OS subsystem that contains the SQL repository.
  2. Select Query Tuner > Start Tuning. The workflow assistant for query tuning opens to the Capture section.
  3. On the left side of the workflow assistant, select User-defined SQL Repository under DB2 for z/OS Sources.
  4. 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.
  5. Click Capture. After you capture SQL statements that match the criteria, the Captured Statements section opens. The captured SQL statements appear in a table in this section. You can sort the statements by clicking the column headers in the table.
  6. Right-click the statement that you want to analyze and tune, and select Invoke Advisors and Tools.
    The workflow assistant for query tuning opens to the Run Single-Query Advisors and Analysis Tools page of the Invoke section. The statement appears in the Query Text field.

What to do next

Run advisors and tools on the statement. For help on the Run Single-Query Advisors and Analysis Tools page, click the Help button at the top of the page or in the two sections. You can also click at the top of the page or in a section and then press F1.


Feedback