Example of creating a query workload from a user-defined SQL repository

You can create query workloads from SQL statements that are in 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. This topic also shows an example of the requirements for capturing SQL statements from such data.

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. (Data type: 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 creating a query workload from SQL statements in data that was offloaded into DB2 for z/OS tables by a query-monitoring application

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 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.

You can filter on values in this data, so that the workflow assistant displays only the subset of the SQL statements that meet your filtering criteria. This capability makes it easy to locate SQL statements that require tuning.

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 you use the workflow assistant to capture SQL statements, you 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, you would follow the steps that are listed in Example of creating a query workload from a user-defined SQL repository.


Feedback