Database monitor view 3003 - Query Sort

Edit online

Displays the SQL logical view format for database monitor QQQ3003.


Create View QQQ3003 as 
  (SELECT QQRID as Row_ID, 
          QQTIME as Time_Created, 
          QQJFLD as Join_Column, 
          QQRDBN as Relational_Database_Name, 
          QQSYS as System_Name, 
          QQJOB as Job_Name, 
          QQUSER as Job_User, 
          QQJNUM as Job_Number, 
          QQI9 as Thread_ID, 
          QQUCNT as Unique_Count, 
          QQUDEF as User_Defined,  
          QQQDTN as Unique_SubSelect_Number, 
          QQQDTL as SubSelect_Nested_Level, 
          QQMATN as Materialized_View_Subselect_Number, 
          QQMATL as Materialized_View_Nested_Level, 
          QVP15E as Materialized_View_Union_Level, 
          QVP15A as Decomposed_Subselect_Number, 
          QVP15B as Total_Number_Decomposed_SubSelects, 
          QVP15C as Decomposed_SubSelect_Reason_Code, 
          QVP15D as Starting_Decomposed_SubSelect, 
          QQSTIM as Start_Timestamp, 
          QQETIM as End_Timestamp, 
          QQRSS as Sorted_Rows, 
          QQI1 as Sort_Space_Size, 
          QQI2 as Pool_Size, 
          QQI3 as Pool_Id, 
          QQI4 as Internal_Sort_Buffer_Length, 
          QQI5 as External_Sort_Buffer_Length, 
          QQRCOD as Reason_Code, 
          QQI7 as Union_Reason_Subcode, 
          QVBNDY as Bound, 
          QVRCNT as Unique_Refresh_Counter, 
          QVPARPF as Parallel_Prefetch, 
          QVPARPL as Parallel_PreLoad, 
          QVPARD as Parallel_Degree_Requested, 
          QVPARU as Parallel_Degree_Used, 
          QVPARRC as Parallel_Degree_Reason_Code, 
          QQEPT as Estimated_Processing_Time, 
          QVCTIM as Estimated_Cumulative_Time, 
          QQAJN as Estimated_Join_Rows, 
          QQJNP as Join_Position, 
          QQI6 as DataSpace_Number, 
          QQC21 as Join_Method, 
          QQC22 as Join_Type, 
          QQC23 as Join_Operator, 
          QVJFANO as Join_Fanout, 
          QVFILES as Join_Table_Count, 
          QQINT03 as Estimated_Storage, 
          QQSMINTF as Plan_Iteration_Number, 
          QQF1 as Average_Read_Time
   FROM   UserLib/DBMONTable 
   WHERE  QQRID=3003)
Table 1. QQQ3003 - Query Sort
View Column Name Table Column Name Description
Row_ID QQRID Row identification
Time_Created QQTIME Time row was created
Join_Column QQJFLD Join column (unique per job)
Relational_Database_Name QQRDBN Relational database name
System_Name QQSYS System name
Job_Name QQJOB Job name
Job_User QQUSER Job user
Job_Number QQJNUM Job number
Thread_ID QQI9 Thread identifier
Unique_Count QQUCNT Unique count (unique per query)
User_Defined QQUDEF User defined column
Unique_SubSelect_Number QQQDTN Unique subselect number
SubSelect_Nested_Level QQQDTL Subselect nested level
Materialized_View_Subselect_Number QQMATN Materialized view subselect number
Materialized_View_Nested_Level QQMATL Materialized view nested level
Materialized_View_Union_Level QVP15E Materialized view union level
Decomposed_Subselect_Number QVP15A Decomposed query subselect number, unique across all decomposed subselects
Total_Number_Decomposed_SubSelects QVP15B Total number of decomposed subselects
Decomposed_SubSelect_Reason_Code QVP15C Decomposed query subselect reason code
Starting_Decomposed_SubSelect QVP15D Decomposed query subselect number for the first decomposed subselect
Start_Timestamp QQSTIM Start timestamp, when available
End_Timestamp QQETIM End timestamp, when available
Sorted_Rows QQRSS Estimated number of rows selected or sorted.
Sort_Space_Size QQI1 Estimated size of sort space.
Pool_Size QQI2 Pool size
Pool_Id QQI3 Pool id
Internal_Sort_Buffer_Length QQI4 Internal sort buffer length
External_Sort_Buffer_Length QQI5 External sort buffer length
Reason_Code QQRCOD Reason code
  • F1 - Query contains grouping columns (GROUP BY) from more that one table, or contains grouping columns from a secondary table of a join query that cannot be reordered.
  • F2 - Query contains ordering columns (ORDER BY) from more that one table, or contains ordering columns from a secondary table of a join query that cannot be reordered.
  • F3 - The grouping and ordering columns are not compatible.
  • F4 - DISTINCT was specified for the query.
  • F5 - UNION was specified for the query.
  • F6 - Query had to be implemented using a sort. Key length of more than 2000 bytes or more than 120 key columns specified for ordering.
Reason_Code (continued)  
  • F7 - Query optimizer chose to use a sort rather than an index to order the results of the query.
  • F8 - Perform specified row selection to minimize I/O wait time.
  • FC - The query contains grouping fields and there is a read trigger on at least one of the physical files in the query.
Union_Reason_Subcode QQI7 Reason subcode for Union:
  • 51 - Query contains UNION and ORDER BY
  • 52 - Query contains UNION ALL
Bound QVBNDY I/O or CPU bound. Possible values are:
  • I - I/O bound
  • C - CPU bound
Unique_Refresh_Counter QVRCNT Unique refresh counter
Parallel_Prefetch QVPARPF Parallel Prefetch (Y/N)
Parallel_PreLoad QVPARPL Parallel Preload (index used)
Parallel_Degree_Requested QVPARD Parallel degree requested (index used)
Parallel_Degree_Used QVPARU Parallel degree used (index used)
Parallel_Degree_Reason_Code QVPARRC Reason parallel processing was limited (index used)
Estimated_Processing_Time QQEPT Estimated processing time, in seconds
Estimated_Cumulative_Time QVCTIM Estimated cumulative time, in seconds
Estimated_Join_Rows QQAJN Estimated number of joined rows
Join_Position QQJNP Join position - when available
DataSpace_Number QQI6 Dataspace number
Join_Method QQC21 Join method - when available
  • NL - Nested loop
  • MF - Nested loop with selection
  • HJ - Hash join
Join_Type QQC22 Join type - when available
  • IN - Inner join
  • PO - Left partial outer join
  • EX - Exception join
Join_Operator QQC23 Join operator - when available
  • EQ - Equal
  • NE - Not equal
  • GT - Greater than
  • GE - Greater than or equal
  • LT - Less than
  • LE - Less than or equal
  • CP - Cartesian product
Join_Fanout QVJFANO Join fan out. Possible values are:
  • N - Normal join situation where fanout is allowed and each matching row of the join fanout is returned.
  • D - Distinct fanout. Join fanout is allowed however none of the join fanout rows are returned.
  • U - Unique fanout. Join fanout is not allowed. Error situation if join fanout occurs.
Join_Table_Count QVFILES Number of tables joined
Estimated_Storage QQINT03 Estimated amount of temporary storage used, in megabytes, to create the temporary index.
Plan_Iteration_Number QQSMINTF AQP Plan iteration number, original optimization = 1
Average_Read_Time QQF1 Average disk I/O time for this object