Database monitor view 3003 - Query Sort
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 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
Parent topic:
Optional database monitor SQL view format