Database monitor view 3030 - Materialized query tables

 

Create View QQQ3030 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, 
          QQ1000 as Materialized_Query_Tables,   
          QQC301 as MQT_Reason_Codes 
   FROM   UserLib/DBMONTable    WHERE  QQRID=3030)

Table 1. QQQ3030 - Materialized query tables
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
Materialized_Query_Tables QQ1000 Materialized query tables examined and reason why used or not used:

  • 0 - The materialized query table was used

  • 1 - The cost to use the materialized query table, as determined by the optimizer, was higher than the cost associated with the chosen implementation.

  • 2 - The join specified in the materialized query was not compatible with the query.

  • 3 - The materialized query table had predicates that were not matched in the query.

  • 4 - The grouping specified in the materialized query table is not compatible with the grouping specified in the query.
Materialized_Query_Tables (continued)  

  • 5 - The query specified columns that were not in the select-list of the materialized query table.

  • 6 - The materialized query table query contains functionality that is not supported by the query optimizer.

  • 7 - The materialized query table specified the DISABLE QUERY OPTIMIZATION clause.

  • 8 - The ordering specified in the materialized query table is not compatible with the ordering specified in the query.

  • 9 - The query contains functionality that is not supported by the materialized query table matching algorithm.

  • 10 - Materialized query tables may not be used for this query.

  • 11 - The refresh age of this materialized query table exceeds the duration specified by the MATERIALIZED_QUERY_TABLE_REFRESH_AGE QAQQINI option.

  • 12 - The commit level of the materialized query table is lower than the commit level specified for the query.

  • 13 - The distinct specified in the materialized query table is not compatible with the distinct specified in the query.

  • 14 - The FETCH FOR FIRST n ROWS clause of the materialized query table is not compatible with the query.

  • 15 - The QAQQINI options used to create the materialized query table are not compatible with the QAQQINI options used to run this query.

  • 16 - The materialized query table is not usable.

  • 17 - The union specified in the materialized query table is not compatible with the query.

  • 18 - The constants specified in the materialized query table are not compatible with host variable values specified in the query.
MQT_Reason_Codes QQC301 List of unique reason codes used by the materialized query tables (each materialized query table has a corresponding reason code associated with it)
QVRCNT QVRCNT Unique refresh counter

 

Parent topic:

Optional database monitor SQL view format