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