Database monitor view 3028 - Grouping

 

Create View QQQ3028 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,
          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, 
          QQREST as Estimated_Rows_Selected, 
          QQAJN as Estimated_Join_Rows, 
          QQJNP as Join_Position, 
          QQI1 as DataSpace_Number,
          QQC21 as Join_Method, 
          QQC22 as Join_Type,
          QQC23 as Join_Operator,
          QVJFANO as Join_Fanout,  
          QVFILES as Join_Table_Count,
          QQC11 as GroupBy_Implementation, 
          QQC101 as GroupBy_Index_Name,     
          QQC102 as GroupBy_Index_Library,  
          QVINAM as GroupBy_Index_Long_Name,  
          QVILIB as GroupBy_Index_Long_Library,  
          QQC12 as Has_Having_Selection,  
          QQC13 as Having_to_Where_Selection_Conversion, 
          QQI2 as Estimated_Number_of_Groups,  
          QQI3 as Average_Number_Rows_per_Group,  
          QVC3001 as GroupBy_Columns, 
          QVC3002 as MIN_Columns,  
          QVC3003 as MAX_Columns,  
          QVC3004 as SUM_Columns,  
          QVC3005 as COUNT_Columns,  
          QVC3006 as AVG_Columns,  
          QVC3007 as STDDEV_Columns,  
          QVC3008 as VAR_Columns 
   FROM   UserLib/DBMONTable    WHERE  QQRID=3028)

Table 1. QQQ3028 - Grouping
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
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_Rows_Selected QQREST Estimated rows selected
Estimated_Join_Rows QQAJN Estimated number of joined rows
Join_Position QQJNP Join position
DataSpace_Number QQI1 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

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
GroupBy_Implementation QQC11 Group by implementation

  • ' ' - No grouping

  • I - Index

  • H - Hash
GroupBy_Index_Name QQC101 Index, or constraint, used for grouping
GroupBy_Index_Library QQC102 Library of index used for grouping
GroupBy_Index_Long_Name QVINAM Long name of index, or constraint, used for grouping
GroupBy_Index_Long_Library QVILIB Long name of index, or constraint, library used for grouping
Has_Having_Selection QQC12 Having selection exists (Y/N)
Having_to_Where_Selection_Conversion QQC13 Having to Where conversion (Y/N)
Estimated_Number_of_Groups QQI2 Estimated number of groups
Average_Number_Rows_per_Group QQI3 Average number of rows in each group
GroupBy_Columns QVC3001 Grouping columns
MIN_Columns QVC3002 MIN columns
MAX_Columns QVC3003 MAX columns
SUM_Columns QVC3004 SUM columns
COUNT_Columns QVC3005 COUNT columns
AVG_Columns QVC3006 AVG columns
STDDEV_Columns QVC3007 STDDEV columns
VAR_Columns QVC3008 VAR columns

 

Parent topic:

Optional database monitor SQL view format