Additional database monitor examples

 

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 views have been created.

1. How many queries are performing dynamic replans?

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

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

   You need to refer to the description of column Dynamic_Replan_Reason_Code for definitions of the dynamic replan reason codes.

3. How many indexes have been created over LIB1/TBL1?

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

4. 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.Unique_Count = B.Unique_Count      AND A.System_Table_Schema = 'LIB1'
        AND A.System_Table_Name = 'TBL1' 

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

5. 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 AND
        A.Unique_Count = B.Unique_Count)
      LEFT OUTER JOIN LIB/QQQ1000 C      ON (A.Join_Column = C.Join_Column AND
        A.Unique_Count = C.Unique_Count)
      WHERE A.System_Table_Schema LIKE '%'
        AND A.System_Table_Name = '%' 

   This query shows key columns from all queries on the server.

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

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

8. 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 AND
        A.Unique_Count = B.Unique_Count)
      LEFT OUTER JOIN LIB/QQQ1000 C      ON (A.Join_Column = C.Join_Column AND
        A.Unique_Count = C.Unique_Count)
      ORDER BY 1 DESC 

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

9. Show the data for all SQL queries with the data for each SQL query logically grouped together.

   SELECT A.*
      FROM LIB/PERFDATA A, LIB/QQQ1000 B    WHERE A.QQJFLD = B.Join_Column      AND A.QQUCNT = B.Unique_Count

   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 (that is, physical column QQRCOD = 'T1' means a table scan was performed because no indexes exist over the queried table).

10. How many queries are being implemented with temporary tables because a key length of greater than 2000 bytes or more than 120 key columns was specified for ordering?

    SELECT COUNT(*)
       FROM LIB/QQQ3004
       WHERE Reason_Code = 'F6'

11. 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.Unique_Count = B.Unique_Count      AND A.ODP_Implementation = 'N';

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

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

13. 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 AND
         A.Unique_Count = B.Unique_Count)
       LEFT OUTER JOIN LIB/QQQ1000 C      ON (A.Join_Column = C.Join_Column AND
         A.Unique_Count = C.Unique_Count)
       WHERE A.Estimated_Processing_Time/1000 > 
         (A.Open_Time + B.Clock_Time_to_Return_All_Rows)

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

14. Should a PTF for queries that perform UNION exists be applied. It should be applied if any queries are performing UNION. Do any of the queries perform this function?

     SELECT COUNT(*)
       FROM   QQQ3014
       WHERE  Has_Union = 'Y'

    If result is greater than 0, the PTF should be applied.

15. 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 will need to compare the statement text between the rows in the two tables to correlate the data.

 

Parent topic:

Database monitor examples