Suppose you have an application program with SQL statements and you want to analyze and performance tune these queries. The first step in analyzing the performance is collection of data. The following examples show how you might collect and analyze data using STRDBMON and ENDDBMON.
Performance data is collected in LIB/PERFDATA for an application running in your current job. The following sequence collects performance data and prepares to analyze it.
You are now ready to analyze the data. The following examples give you a few ideas on how to use this data. You should closely study the physical and logical file DDS to understand all the data being collected so you can create queries that give the best information for your applications.
Determine which queries in your SQL application are implemented with table scans. The complete information can be obtained by joining two logical files: QQQ1000, which contains information about the SQL statements, and QQQ3000, which contains data about queries performing table scans. The following SQL query could be used:
SELECT A.QQTLN, A.QQTFN, A.QQTOTR, A.QQIDXA, B.QQROWR,
(B.QQETIM - B.QQSTIM) AS TOT_TIME, B.QQSTTX
FROM LIB/QQQ3000 A, LIB/QQQ1000 B
WHERE A.QQJFLD = B.QQJFLD
AND A.QQUCNT = B.QQUCNT
Sample output of this query is shown in Table 3. Key to this example are the join criteria:
WHERE A.QQJFLD = B.QQJFLD AND A.QQUCNT = B.QQUCNT
A lot of data about many queries is contained in multiple rows in table
LIB/PERFDATA. It is not uncommon for data about a single query to be
contained in 10 or more rows within the table. The combination of
defining the logical files and then joining the tables together allows you to
piece together all the data for a query or set of queries. Column
QQJFLD uniquely identifies all data common to a job; column QQUCNT is
unique at the query level. The combination of the two, when referenced
in the context of the logical files, connects the query implementation to the
query statement information.
Table 3. Output for SQL Queries that Performed Table Scans
| Lib Name | Table Name | Total Rows | Index Advised | Rows Returned | TOT_TIME | Statement Text |
|---|---|---|---|---|---|---|
| LIB1 | TBL1 | 20000 | Y | 10 | 6.2 |
SELECT * FROM LIB1/TBL1 WHERE FLD1 = 'A' |
| LIB1 | TBL2 | 100 | N | 100 | 0.9 |
SELECT * FROM LIB1/TBL2 |
| LIB1 | TBL1 | 20000 | Y | 32 | 7.1 |
SELECT * FROM LIB1/TBL1 WHERE FLD1 = 'B' AND FLD2 > 9000 |
If the query does not use SQL, the SQL information row (QQQ1000) is not created. This makes it more difficult to determine which rows in LIB/PERFDATA pertain to which query. When using SQL, row QQQ1000 contains the actual SQL statement text that matches the performance rows to the corresponding query. Only through SQL is the statement text captured. For queries executed using the OPNQRYF command, the OPNID parameter is captured and can be used to tie the rows to the query. The OPNID is contained in column QQOPID of row QQQ3014.
Similar to the preceding example that showed which SQL applications were implemented with table scans, the following example shows all queries that are implemented with table scans.
SELECT A.QQTLN, A.QQTFN, A.QQTOTR, A.QQIDXA,
B.QQOPID, B.QQTTIM, C.QQCLKT, C.QQRCDR, D.QQROWR,
(D.QQETIM - D.QQSTIM) AS TOT_TIME, D.QQSTTX
FROM LIB/QQQ3000 A INNER JOIN LIB/QQQ3014 B
ON (A.QQJFLD = B.QQJFLD AND
A.QQUCNT = B.QQUCNT)
LEFT OUTER JOIN LIB/QQQ3019 C
ON (A.QQJFLD = C.QQJFLD AND
A.QQUCNT = C.QQUCNT)
LEFT OUTER JOIN LIB/QQQ1000 D
ON (A.QQJFLD = D.QQJFLD AND
A.QQUCNT = D.QQUCNT)
In this example, the output for all queries that performed table scans are shown in Table 4.
| Note: | The columns selected from table QQQ1000 do return NULL default values if the query was not executed using SQL. For this example assume the default value for character data is blanks and the default value for numeric data is an asterisk (*). |
Table 4. Output for All Queries that Performed Table Scans
If the SQL statement text is not needed, joining to table QQQ1000 is not necessary. You can determine the total time and rows selected from data in the QQQ3014 and QQQ3019 rows.
Your next step may include further analysis of the table scan data. The previous examples contained a column titled Index Advised. A Y (yes) in this column is a hint from the query optimizer that the query may perform better with an index to access the data. For the queries where an index is advised, notice that the rows selected by the query are low in comparison to the total number of rows in the table. This is another indication that a table scan may not be optimal. Finally, a long execution time may highlight queries that may be improved by performance tuning.
The next logical step is to look into the index advised optimizer hint. The following query could be used for this:
SELECT A.QQTLN, A.QQTFN, A.QQIDXA, A.QQIDXD,
A.QQIDXK, B.QQOPID, C.QQSTTX
FROM LIB/QQQ3000 A INNER JOIN LIB/QQQ3014 B
ON (A.QQJFLD = B.QQJFLD AND
A.QQUCNT = B.QQUCNT)
LEFT OUTER JOIN LIB/QQQ1000 C
ON (A.QQJFLD = C.QQJFLD AND
A.QQUCNT = C.QQUCNT)
WHERE A.QQIDXA = 'Y'
There are two slight modifications from the first example. First,
the selected columns have been changed. Most important is the selection
of column QQIDXD that contains a list of possible key columns to use when
creating the index suggested by the query optimizer. Second, the query
selection limits the output to those table scan queries where the optimizer
advises that an index be created (A.QQIDXA = 'Y'). Table 5 shows what the results might look like.
Table 5. Output with Recommended Key Columns
| Lib Name | Table Name | Index Advised | Advised Key columns | Advised Primary Key | Query OPNID | Statement Text |
|---|---|---|---|---|---|---|
| LIB1 | TBL1 | Y | FLD1 | 1 |
|
SELECT * FROM LIB1/TBL1 WHERE FLD1 = 'A' |
| LIB1 | TBL1 | Y | FLD1, FLD2 | 1 |
|
SELECT * FROM LIB1/TBL1 WHERE FLD1 = 'B' AND FLD2 > 9000 |
| LIB1 | TBL4 | Y | FLD1, FLD4 | 1 | QRY04 |
|
At this point you should determine whether it makes sense to create a permanent index as advised by the optimizer. In this example, creating one index over LIB1/TBL1 would satisfy all three queries since each use a primary or left-most key column of FLD1. By creating one index over LIB1/TBL1 with key columns FLD1, FLD2, there is potential to improve the performance of the second query even more. The frequency these queries are run and the overhead of maintaining an additional index over the table should be considered when deciding whether or not to create the suggested index.
If you create a permanent index over FLD1, FLD2 the next sequence of steps would be to:
It is likely that the three index-advised queries are no longer performing table scans.
The following are additional ideas or examples on how to extract information from the performance monitor statistics. All of the examples assume data has been collected in LIB/PERFDATA and the documented logical files have been created.
SELECT COUNT(*) FROM LIB/QQQ1000 WHERE QQDYNR <> 'NA'
SELECT QQDYNR, QQSTTX FROM LIB/QQQ1000 WHERE QQDYNR <> 'NA'
| Note: | You have to refer to the description of column QQDYNR for definitions of the dynamic replan reason codes. |
SELECT COUNT(*)
FROM LIB/QQQ3002
WHERE QQTLN = 'LIB1'
AND QQTFN = 'TBL1'
SELECT A.QQTLN, A.QQTFN, A.QQIDXD, B.QQSTTX
FROM LIB/QQQ3002 A, LIB/QQQ1000 B
WHERE A.QQJFLD = B.QQJFLD
AND A.QQUCNT = B.QQUCNT
AND A.QQTLN = 'LIB1'
AND A.QQTFN = 'TBL1'
| Note: | This query shows key columns only from queries executed using SQL. |
SELECT A.QQTLN, A.QQTFN, A.QQIDXD,
B.QQOPID,C.QQSTTX
FROM LIB/QQQ3002 A INNER JOIN LIB/QQQ3014 B
ON (A.QQJFLD = B.QQJFLD AND
A.QQUCNT = B.QQUCNT)
LEFT OUTER JOIN LIB/QQQ1000 C
ON (A.QQJFLD = C.QQJFLD AND
A.QQUCNT = C.QQUCNT)
WHERE A.QQTLN = 'LIB1'
AND A.QQTFN = 'TBL1'
| Note: | This query shows key columns from all queries on the system. |
SELECT QQSTOP, COUNT(*) FROM LIB/QQQ1000 GROUP BY QQSTOP ORDER BY 2 DESC
SELECT (QQETIM - QQSTIM), QQUSER, QQSTTX FROM LIB/QQQ1000 ORDER BY 1 DESC
SELECT (A.QQTTIM + B.QQCLKT), A.QQOPID, C.QQSTTX
FROM LIB/QQQ3014 A LEFT OUTER JOIN LIB/QQQ3019 B
ON (A.QQJFLD = B.QQJFLD AND
A.QQUCNT = B.QQUCNT)
LEFT OUTER JOIN LIB/QQQ1000 C
ON (A.QQJFLD = C.QQJFLD AND
A.QQUCNT = C.QQUCNT)
ORDER BY 1 DESC
| Note: | This example assumes detail data has been collected into row QQQ3019. |
SELECT A.*
FROM LIB/PERFDATA A, LIB/QQQ1000 B
WHERE A.QQJFLD = B.QQJFLD
AND A.QQUCNT = B.QQUCNT
| Note: | This might be used within a report that will format the the interesting data into a more readable format. For example, all reason code columns could be expanded by the report to print the definition of the reason code (that is, physical column QQRCOD = 'T1' means a table scan was performed because no indexes exist over the queried table). |
SELECT COUNT(*) FROM LIB/QQQ3004 WHERE QQRCOD = 'F6'
SELECT B.QQSTTX
FROM LIB/QQQ3010 A, LIB/QQQ1000 B
WHERE A.QQJFLD = B.QQJFLD
AND A.QQUCNT = B.QQUCNT
AND A.QQODPI = 'N'
SELECT QQEPT, QQOPID FROM LIB/QQQ3014 WHERE QQGVNS = 'Y'
| Note: | This example assumes detail data has been collected into row QQQ3019. |
SELECT A.QQEPT, (A.QQTTIM + B.QQCLKT), A.QQOPID,
C.QQTTIM, C.QQSTTX
FROM LIB/QQQ3014 A LEFT OUTER JOIN LIB/QQQ3019 B
ON (A.QQJFLD = B.QQJFLD AND
A.QQUCNT = B.QQUCNT)
LEFT OUTER JOIN LIB/QQQ1000 C
ON (A.QQJFLD = C.QQJFLD AND
A.QQUCNT = C.QQUCNT)
WHERE A.QQEPT/1000 > (A.QQTTIM + B.QQCLKT)
| Note: | This example assumes detail data has been collected into row QQQ3019. |
SELECT COUNT(*) FROM QQQ3014 WHERE QQUNIN = 'Y'
| Note: | If result is greater than 0, the PTF should be applied. |