Database monitor view 3020 - Index advised (SQE)
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 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
- I4 - Nested loop join
- I5 - Row selection using bitmap processing
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
Parent topic:
Optional database monitor SQL view format
Related reference
Query optimizer index advisor