Additional database monitor examples

The following are additional ideas or examples on how to extract information from the performance monitor statistics. All the examples assume that data has been collected in LIB/PERFDATA and the documented views have been created.

How many queries are performing dynamic replans?

 SELECT COUNT(*)
   FROM   LIB.QQQ1000
   WHERE  Dynamic_Replan_Reason_Code <> 'NA'

What is the statement text and the reason for the dynamic replans?
```
 SELECT Dynamic_Replan_Reason_Code, Statement_Text_Long
   FROM   LIB.QQQ1000
   WHERE  Dynamic_Replan_Reason_Code <> 'NA'
```
Note: You need to refer to the description of column Dynamic_Replan_Reason_Code for definitions of the dynamic replan reason codes.

How many indexes have been created over LIB1/TBL1?

 SELECT COUNT(*)
   FROM   LIB.QQQ3002
   WHERE  System_Table_Schema = 'LIB1'
     AND  System_Table_Name = 'TBL1'

What key columns are used for all indexes created over LIB1/TBL1 and what is the associated SQL statement text?

SELECT A.System_Table_Schema, A.System_Table_Name,
     A.Index_Advised_Columns, B.Statement_Text_Long 
   FROM LIB.QQQ3002 A, LIB.QQQ1000 B
   WHERE A.Join_Column = B.Join_Column
     AND A.System_Table_Schema = 'LIB1'
     AND A.System_Table_Name = 'TBL1'

Note: This query shows key columns only from queries executed using SQL.

What key columns are used for all indexes created over LIB1/TBL1 and what was the associated SQL statement text or query open ID?

SELECT A.System_Table_Schema, A.System_Table_Name, A.Index_Advised_Columns,
     B.Open_Id, C.Statement_Text_Long 
   FROM LIB.QQQ3002 A INNER JOIN LIB.QQQ3014 B
     ON (A.Join_Column = B.Join_Column)
   LEFT OUTER JOIN LIB.QQQ1000 C
     ON (A.Join_Column = C.Join_Column)
   WHERE A.System_Table_Schema LIKE '%'
     AND A.System_Table_Name = '%'

Note: This query shows key columns from all queries on the system.

What types of SQL statements are being performed? Which are performed most frequently?

SELECT CASE Statement_Function
     WHEN 'O' THEN 'Other'
     WHEN 'S' THEN 'Select'
     WHEN 'L' THEN 'DDL'
     WHEN 'I' THEN 'Insert'
     WHEN 'U' THEN 'Update'
   ELSE 'Unknown'
   END, COUNT(*)
   FROM LIB.QQQ1000
   GROUP BY Statement_Function
   ORDER BY 2 DESC

Which SQL queries are the most time consuming? Which user is running these queries?

SELECT (End_Timestamp - Start_Timestamp), Job_User, 
     Current_User_Profile, Statement_Text_Long
   FROM LIB.QQQ1000
   ORDER BY 1 DESC

Which queries are the most time consuming?

SELECT (A.Open_Time + B.Clock_Time_to_Return_All_Rows), 
     A.Open_Id, C.Statement_Text_Long
   FROM LIB.QQQ3014 A LEFT OUTER JOIN LIB.QQQ3019 B
     ON (A.Join_Column = B.Join_Column)
   LEFT OUTER JOIN LIB.QQQ1000 C
     ON (A.Join_Column = C.Join_Column)
   ORDER BY 1 DESC

Note: This example assumes that detail data was collected (STRDBMON TYPE(*DETAIL)).

Show the data for all SQL queries with the data for each SQL query logically grouped.
```
SELECT A.*
   FROM LIB.PERFDATA A, LIB.QQQ1000 B
   WHERE A.QQJFLD = B.Join_Column
```
Note: This might be used within a report that will format the interesting data into a more readable format. For example, all reason code columns can be expanded by the report to print the definition of the reason code. Physical column QQRCOD = 'T1' means that a table scan was performed because no indexes exist over the queried table.
How many queries are implemented with temporary tables because a key length greater than 2000 bytes, or more than 120 key columns was specified for ordering?
```
SELECT COUNT(*)
   FROM LIB.QQQ3004
   WHERE Reason_Code = 'F6'
```

Which SQL queries were implemented with nonreusable ODPs?

SELECT B.Statement_Text_Long 
   FROM LIB.QQQ3010 A, LIB.QQQ1000 B
   WHERE A.Join_Column = B.Join_Column
     AND A.ODP_Implementation = 'N';

What is the estimated time for all queries stopped by the query governor?
```
SELECT Estimated_Processing_Time, Open_Id
   FROM LIB.QQQ3014
   WHERE Stopped_By_Query_Governor = 'Y' 
```
Note: This example assumes that detail data was collected (STRDBMON TYPE(*DETAIL)).

Which queries estimated time exceeds actual time?

SELECT A.Estimated_Processing_Time,  
     (A.Open_Time + B.Clock_Time_to_Return_All_Rows), 
     A.Open_Id, C.Statement_Text_Long
   FROM LIB.QQQ3014 A LEFT OUTER JOIN LIB.QQQ3019 B
     ON (A.Join_Column = B.Join_Column)
   LEFT OUTER JOIN LIB.QQQ1000 C
     ON (A.Join_Column = C.Join_Column)
   WHERE A.Estimated_Processing_Time/1000 > 
     (A.Open_Time + B.Clock_Time_to_Return_All_Rows)

Note: This example assumes that detail data was collected (STRDBMON TYPE(*DETAIL)).

Should you apply a PTF for queries containing UNIONs? Yes, if any queries are performing UNIONs. Do any of the queries perform this function?
```
 SELECT COUNT(*)
   FROM QQQ3014
   WHERE Has_Union = 'Y'
```
Note: If the result is greater than 0, apply the PTF.
You are a system administrator and an upgrade to the next release is planned. You want to compare data from the two releases.
- Collect data from your application on the current release and save this data in LIB/CUR_DATA
- Move to the next release
- Collect data from your application on the new release and save this data in a different table: LIB/NEW_DATA
- Write a program to compare the results. You need to compare the statement text between the rows in the two tables to correlate the data.