Example of capturing SQL statements from a user-defined SQL repository

You can capture SQL statements from data that was offloaded to DB2® for z/OS® tables by non-IBM query-monitoring applications. To do this task, you must map categories of data to columns in the DB2 for z/OS tables that contain the offloaded data. The first section shows these categories of data and the data types to use for them. The second section shows an example of the requirements for capturing SQL statements from such data.

Categories of data to map to DB2 columns

Columns in the DB2 tables must contain the following information about the SQL statements, their runtime metrics, and the time intervals during which the statements ran.

DB2 subsystem
DB2 subsystem where the runtime metrics and SQL statements were collected (Data type: SQL CHAR or VARCHAR)
Plan name
Name of the DB2 plan (Data type: SQL CHAR or VARCHAR)
Collection
ID of the DB2 plan or package collection (Data type: SQL CHAR or VARCHAR)
Package name
Name of the DB2 package (Data type: SQL CHAR or VARCHAR)
Package consistency token
Consistency token for the DBRM or DB2 package (Data type: SQL CHAR or BINARY)
Primary authorization ID
Primary authorization ID that was used to run the statement (Data type: SQL CHAR or VARCHAR)
Statement text
Text of the SQL statement, either the whole statement or a fragment of it (Data type: SQL CHAR, VARCHAR, or BINARY)

This text can be broken into segments that are in other rows. The unique statement ID and sequence number ensure that the segments are concatenated correctly.

The query-monitoring application can offload static SQL statements to the STMT column of the SYSIBM.SYSPACKSTMT catalog table. The table stores the SQL text in BINARY format. If the application that you are using offloads static SQL statements to this table, you must use the HEX() function to convert the statements from BINARY format to hexidecimal strings. Do the conversion in the view that you define to combine this column with the table of runtime metrics and information about the intervals of time in which the metrics were collected.
Unique statement ID
ID that is unique to the statement; it is used to identify fragments of the statement text that are in different rows of a table (SQL CHAR, VARCHAR, or BINARY)
Statement sequence number
Number that describes the order in which a segment of a statement is to be concatenated together with the other segments of that statement (Data type: SQL INT or SMALLINT)
Start of interval
Start of the interval in which the runtime metrics were collected (Data type: SQL TIMESTAMP)
End of interval
End of the interval in which the runtime metrics were collected (Data type: SQL TIMESTAMP)
Section number
Number of the section in the DB2 package in which the SQL statement is located (Data type: SQL INT or SMALLINT)
Statement number
Number of the statement within the DB2 package (Data type: SQL INT or SMALLINT)
Execution count
Number of times that the SQL statement ran (Data type: SQL INT or SMALLINT)
Total CPU time
CPU time that was required to run the statement for the number of times that is specified for the execution count (Data type: SQL FLOAT or DOUBLE)
Total elapsed time
Amount of Class 2 time that was required to run the statement for the number of times that is specified for the execution count (Data type: SQL FLOAT or DOUBLE)
Number of getpages
Number of getpage requests that were issued (Data type: SQL INT or SMALLINT)
Unique metric ID
ID that is unique to the runtime metrics that were collected for an SQL statement; it is used to join the table of runtime metrics to the table that lists objects that the SQL statements in the repository reference (Data Type: SQL CHAR, VARCHAR, or BINARY)

You can also specify which columns contain information about the database objects that the SQL statements reference, if a table contains this information.

Dependent object qualifier
Qualifier or schema of the referenced object (Data type: SQL CHAR or VARCHAR)
Dependent object name
Name of the referenced object (Data type: SQL CHAR or VARCHAR)
Dependent object type
Type of object that is referenced (Data type: SQL CHAR or VARCHAR)
Database name
Name of the database in which the referenced object is stored (Data type: SQL CHAR or VARCHAR)
Table space name
Name of the table space in which the referenced object is stored (Data type: SQL CHAR or VARCHAR)
Unique metric ID
ID that is unique to the runtime metrics that were collected for an SQL statement; it is used to join the table of runtime metrics to the table that lists objects that the SQL statements in the repository reference (Data Type: SQL CHAR, VARCHAR, or BINARY)

Example of capturing SQL statements from DB2 for z/OS tables that were created by a query-monitoring application

This example uses IBM® DB2 Query Monitor for z/OS, which can offload data into DB2 for z/OS tables, just as non-IBM query-monitoring applications can. However, for best results, use the Capture SQL from DB2 Query Monitor for z/OS page in the Capture section of the workflow assistant to capture SQL statements from DB2 Query Monitor for z/OS.

Suppose that you use DB2 Query Monitor for z/OS and offload data from VSAM files into DB2 for z/OS tables. You then want to query these tables to find out information about the performance of SQL statements in various database applications. If any SQL statements fall below a threshold of performance, your objective is to tune those statements.

To capture and tune SQL statements from this repository of data, you need to offload runtime metrics, SQL text, and information about time intervals.

This data is required for query tuning and provides these benefits:

You need to offload data into these three tables:

CQMnnINTERVALS
This table defines the start and end times of each interval.
CQMnnSUMM_METRICS
This table contains the runtime metrics that DB2 Query Monitor for z/OS collects for every SQL statement.
CQMnnSUMM_TEXT
This table contains the text of the SQL statements.
Note: Your query-monitoring application can offload the text of static SQL statements to the STMT column of the SYSIBM.SYSPACKSTMT catalog table.

Before using the workflow assistant to capture SQL statements, you would need to create a view that joins these three tables. Here is an example:

CREATE VIEW QM_STMT_VIEW 
  (DB2_SUBSYSTEM, PLAN, COLLECTION, PROGRAM,
   CONSISTENCY_TOKEN, AUTHID, METRICS_TOKEN, TEXT_TOKEN, SQLTEXT, SEQNO,
   INTERVAL_START, INTERVAL_END, SECTION, STMT, SQL_CALLS, DB2_CPU,
   DB2_ELAP, GETPAGES) 
AS
SELECT A.DB2_SUBSYSTEM, A.PLAN, A.COLLECTION, A.PROGRAM, 
   A.CONSISTENCY_TOKEN, A.AUTHID, A.METRICS_TOKEN, A.TEXT_TOKEN, 
   B.SQLTEXT, 0, A.INTERVAL_START, CURRENT TIMESTAMP AS INTERVAL_END_TS, 
   A.SECTION, A.STMT, A.SQL_CALLS, A.DB2_CPU, A.DB2_ELAPSED, A.GETPAGES
FROM QMTOOLS.CQM23_SUMM_METRICS A,  
     QMTOOLS.CQM23_SUMM_TEXT B
WHERE 
   A.TEXT_TOKEN = B.TEXT_TOKEN
   AND A.SMFID = B.SMFID
   AND A.CQM_SUBSYSTEM = B.CQM_SUBSYSTEM    
   AND A.INTERVAL_NUMBER = B.INTERVAL_NUMBER
   AND A.INTERVAL_START = B.INTERVAL_START  
   AND A.INTERVAL_NUMBER IN (       
      SELECT DISTINCT A.INTERVAL_NUMBER
      FROM QMTOOLS.CQM23_INTERVALS A,           
           QMTOOLS.CQM23_SUMM_METRICS B        
      WHERE A.SMFID = B.SMFID
         AND A.CQM_SUBSYSTEM = B.CQM_SUBSYSTEM           
         AND A.INTERVAL_NUMBER = B.INTERVAL_NUMBER               
         AND A.INTERVAL_START = B.INTERVAL_START);

If your query-monitoring application offloads the text of static SQL statements to the STMT column of the SYSIBM.SYSPACKSTMT catalog table, the view must join this column with the CQMnnINTERVALS and CQMnnSUMM_METRICS tables. The view must also use the HEX() function to convert the text of each statement from BINARY format to a hexidecimal string.

You could also offload data about the database objects that the SQL statements reference. You would need to offload that data into the SUMM_OBJECTS table.

After your data is offloaded and your view is created, follow the steps that are listed in Capturing SQL statements from user-defined SQL repositories.


Feedback