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.
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.
You can also specify which columns contain information about the database objects that the SQL statements reference, if a table contains this information.
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:
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.