Database monitor view 3020 - Index advised (SQE)

Displays the SQL logical view format for database monitor QQQ3020.


Create View QQQ3020 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, 
          QQTLN as System_Table_Schema, 
          QQTFN as System_Table_Name, 
          QQTMN as Member_Name, 
          QQPTLN as System_Base_Table_Schema, 
          QQPTFN as System_Base_Table_Name, 
          QQPTMN as Base_Member_Name, 
          QVPLIB as Base_Table_Schema, 
          QVPTBL as Base_Table_Name,
          QQTOTR as Table_Total_Rows, 
          QQEPT as Estimated_Processing_Time, 
          QQIDXA as Index_is_Advised, 
          QQIDXD as Index_Advised_Columns_Short_List, 
          QQ1000L as Index_Advised_Columns_Long_List, 
          QQI1 as Number_of_Advised_Columns, 
          QQI2 as Number_of_Advised_Primary_Columns, 
          QQRCOD as Reason_Code, 
          QVRCNT as Unique_Refresh_Counter, 
          QVC1F as Type_of_Index_Advised, 
          QQNTNM as NLSS_Table, 
          QQNLNM as NLSS_Library, 
          QQSMINTF as Plan_Iteration_Number, 
          QQ13 as DEPENDENT_ADVICE_ID
   FROM   UserLib/DBMONTable 
   WHERE  QQRID=3020)
Table 1. QQQ3020 - Index advised (SQE)
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
System_Table_Schema QQTLN Schema of table queried
System_Table_Name QQTFN Name of table queried
Member_Name QQTMN Member name of table queried
System_Base_Table_Schema QQPTLN Schema name of base table
System_Base_Table_Name QQPTFN Name of base table for table queried
Base_Member_Name QQPTMN Member of base table
Base_Table_Schema QVPLIB Schema of base table, long name
Base_Table_Name QVPTBL Base table, long name
Table_Total_Rows QQTOTR Number of rows in the table
Estimated_Processing_Time QQEPT Estimated processing time, in seconds
Index_is_Advised QQIDXA Index advised (Y/N)
Index_Advised_Columns_Short_List QQIDXD Columns for the index advised, first 1000 bytes
Index_Advised_Columns_Long_List QQ1000L Column for the index advised
Number_of_Advised_Columns QQI1 Number of indexes advised
Number_of_Advised_Primary_Columns QQI2 Number of advised columns that use index scan-key positioning
Reason_Code QQRCOD Reason code
  • I1 - Row selection
  • I2 - Ordering/Grouping
  • I3 - Row selection and Ordering/Grouping
  • I5 - Row selection using bitmap processing
  • I6 - Source of statistics
Unique_Refresh_Counter QVRCNT Unique refresh counter
Type_of_Index_Advised QVC1F Type of index advised. Possible values are:
  • B - Radix index
  • E - Encoded vector index
NLSS_Table QQNTNM Sort Sequence Table
NLSS_Library QQNLNM Sort Sequence Library
Plan_Iteration_Number QQSMINTF AQP Plan iteration number, original optimization = 1
Dependent_Advice_ID QQ13 Unique identifier used to link together OR predicate index advice