Database monitor view 3002

Database monitor view 3002 - Index Created
Create View QQQ3002 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, 
          QQILNM as System_Index_Schema, 
          QQIFNM as System_Index_Name, 
          QQIMNM as Index_Member_Name, 
          QQNTNM as NLSS_Table, 
          QQNLNM as NLSS_Library,
          QQSTIM as Start_Timestamp, 
          QQETIM as End_Timestamp, 
          QQTOTR as Table_Total_Rows, 
          QQRIDX as Created_Index_Entries, 
          QQREST as Estimated_Rows_Selected, 
          QQFKEY as Index_Probe_Keys, 
          QQKSEL as Index_Scan_Keys, 
          QQAJN as Estimated_Join_Rows, 
          QQEPT as Estimated_Processing_Time, 
          QQJNP as Join_Position, 
          QQI1 as DataSpace_Number, 
          QQC21 as Join_Method, 
          QQC22 as Join_Type, 
          QQC23 as Join_Operator, 
          QQI2 as Index_Advised_Probe_Count,
          QQKP as Index_Probe_Used, 
          QQI3 as Index_Probe_Column_Count, 
          QQKS as Index_Scan_Used, 
          QQDSS as DataSpace_Selection, 
          QQIDXA as Index_Advised, 
          QQRCOD as Reason_Code, 
          QQIDXD as Index_Advised_Columns,
          QQ1000 as Created_Index_Columns, 
          QVQTBL as Table_Name, 
          QVQLIB as Table_Schema, 
          QVPTBL as Base_Table_Name, 
          QVPLIB as Base_Table_Schema,
          QVINAM as Index_Name, 
          QVILIB as Index_Schema,  
          QVBNDY as Bound, 
          QVRCNT as Unique_Refresh_Counter, 
          QVJFANO as Join_Fanout, 
          QVFILES as Join_Table_Count, 
          QVPARPF as Parallel_Prefetch,
          QVPARPL as Parallel_Preload,
          QVPARD as Parallel_Degree_Requested, 
          QVPARU as Parallel_Degree_Used, 
          QVPARRC as Parallel_Degree_Reason_Code, 
          QVCTIM as Estimated_Cumulative_Time,
          QQC101 as Created_Index_Name, 
          QQC102 as Created_Index_Schema, 
          QQI4 as Created_Index_Page_Size, 
          QQI5 as Created_Index_Row_Size, 
          QQC14 as Created_Index_Used_ACS_Table, 
          QQC103 as Created_Index_ACS_Table, 
          QQC104 as Created_Index_ACS_Library, 
          QVC13 as Created_Index_Reusable, 
          QVC14 as Created_Index_Sparse, 
          QVC1F as Created_Index_Type, 
          QVP15F as Created_Index_Unique_EVI_Count, 
          QVC15 as Permanent_Index_Created, 
          QVC16 as Index_From_Index, 
          QVP151 as Created_Index_Parallel_Degree_Requested, 
          QVP152 as Created_Index_Parallel_Degree_Used, 
          QVP153 as Created_Index_Parallel_Degree_Reason_Code, 
          QVC17 as Index_Only_Access, 
          QVC18 as Index_Fits_In_Memory,
          QVC1B as Index_Type, 
          QQI6 as Index_Entries, 
          QQI7 as Unique_Keys, 
          QVP158 as Percent_Overflow, 
          QVP159 as Vector_Size, 
          QQI8 as Index_Size, 
          QVP156 as Index_Page_Size, 
          QVP154 as Pool_Size, 
          QVP155 as Pool_ID, 
          QVP157 as Table_Size, 
          QVC1C as Skip_Sequential_Table_Scan, 
          QVC3001 as DataSpace_Selection_Columns, 
          QVC1E as Derived_Column_Selection, 
          QVC3002 as Derived_Column_Selection_Columns, 
          QVC3003 as Table_Column_For_Index_Probe, 
          QVC3004 as Table_Column_For_Index_Scan, 
          QQC18 as Read_Trigger, 
          QQC13 as MQT_Replacement,
          QQC16 as Reused_Temporary_Index    FROM   UserLib/DBMONTable    WHERE  QQRID=3002)   
Table 1. QQQ3002 - Index Created
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 name of base table
System_Index_Schema QQILNM Schema name of index used for access
System_Index_Name QQIFNM Name of index used for access
Index_Member_Name QQIMNM Member name of index used for access
NLSS_Table QQNTNM NLSS table
NLSS_Library QQNLNM NLSS library
Start_Timestamp QQSTIM Start timestamp, when available.
End_Timestamp QQETIM End timestamp, when available
Table_Total_Rows QQTOTR Total rows in table
Created_Index_Entries QQRIDX Number of entries in index created
Estimated_Rows_Selected QQREST Estimated number of rows selected
Index_Probe_Keys QQFKEY Keys selected thru index scan-key positioning
Index_Scan_Keys QQKSEL Keys selected thru index scan-key selection
Estimated_Join_Rows QQAJN Estimated number of joined rows
Estimated_Processing_Time QQEPT Estimated processing time, in seconds
Join_Position QQJNP Join position - when available
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

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

Index_Advised_Probe_Count QQI2 Number of advised key columns that use index scan-key positioning
Index_Probe_Used QQKP Index scan-key positioning

Y - Yes

N - No

Index_Probe_Column_Count QQI3 Number of columns that use index scan-key positioning for the index used
Index_Scan_Used QQKS Index scan-key selection

Y - Yes

N - No

DataSpace_Selection QQDSS Dataspace selection

Y - Yes

N - No

Index_Advised QQIDXA Index advised

Y - Yes

N - No

Reason_Code QQRCOD Reason code

I1 - Row selection

I2 - Ordering/Grouping

I3 - Row selection and Ordering/Grouping

I4 - Nested loop join

Index_Advised_Columns QQIDXD Key columns for index advised
Created_Index_Columns QQ1000 Key columns for index created
Table_Name QVQTBL Queried table, long name
Table_Schema QVQLIB Schema of queried table, long name
Base_Table_Name QVPTBL Base table, long name
Base_Table_Schema QVPLIB Schema of base table, long name
Index_Name QVINAM Name of index (or constraint) used, long name
Index_Schema QVILIB Schema of index used, long name
Bound QVBNDY I/O or CPU bound. Possible values are:

I - I/O bound

C - CPU bound

Unique_Refresh_Counter QVRCNT Unique refresh counter
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
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_Cumulative_Time QVCTIM Estimated cumulative time, in seconds
Created_Index_Name QQC101 Name of index created - when available
Created_Index_Schema QQC102 Schema of index created - when available
Created_Index_Page_Size QQI4 Page size of index created
Created_Index_Row_Size QQI5 Row size of index created
Created_Index_Used_ACS_Table QQC14 Index Created used Alternate Collating Sequence Table (Y/N)
Created_Index_ACS_Table QQC103 Alternate Collating Sequence table of index created.
Created_Index_ACS_Library QQC104 Alternate Collating Sequence library of index created.
Created_Index_Reusable QVC13 Index created is reusable (Y/N)
Created_Index_Sparse QVC14 Index created is sparse index (Y/N)
Created_Index_Type QVC1F Type of index created. Possible values:

B - Binary Radix Index

E - Encoded Vector Index (EVI)

Created_Index_Unique_EVI_Count QVP15F Number of unique values of index created if index created is an EVI index.
Permanent_Index_Created QVC15 Permanent index created (Y/N)
Index_From_Index QVC16 Index from index (Y/N)
Created_Index_Parallel_Degree_Requested QVP151 Parallel degree requested (index created)
Created_Index_Parallel_Degree_Used QVP152 Parallel degree used (index created)
Created_Index_Parallel_Degree_Reason_Code QVP153 Reason parallel processing was limited (index created)
Index_Only_Access QVC17 Index only access (Y/N)
Index_Fits_In_Memory QVC18 Index fits in memory (Y/N)
Index_Type QVC1B Type of Index. Possible values are:

B - Binary Radix Index

C - Constraint (Binary Radix)

E - Encoded Vector Index (EVI)

T - Tertiary (AND/OR) Index

Index_Entries QQI6 Number of index entries, index used
Unique_Keys QQI7 Number of unique key values, index used
Percent_Overflow QVP158 Percent overflow, index used
Vector_Size QVP159 Vector size, index used
Index_Size QQI8 Size of index used.
Index_Page_Size QVP156 Index page size
Pool_Size QVP154 Pool size
Pool_ID QVP155 Pool id
Table_Size QVP157 Table size
Skip_Sequential_Table_Scan QVC1C Skip sequential table scan (Y/N)
DataSpace_Selection_Columns QVC3001 Columns used for dataspace selection
Derived_Column_Selection QVC1E Derived column selection (Y/N)
Derived_Column_Selection_Columns QVC3002 Columns used for derived column selection
Table_Columns_For_Index_Probe QVC3003 Columns used for index scan-key positioning
Table_Columns_For_Index_Scan QVC3004 Columns used for index scan-key selection
Read_Trigger QQC18 Read Trigger (Y/N)
MQT_Replacement QQC13 Materialized Query Table replaced queried table (Y/N)
Reused_Temporary_Index QQC16 Temporary index reused (Y/N)

Parent topic:
Optional database monitor SQL view format

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 name of base table
System_Index_Schema	QQILNM	Schema name of index used for access
System_Index_Name	QQIFNM	Name of index used for access
Index_Member_Name	QQIMNM	Member name of index used for access
NLSS_Table	QQNTNM	NLSS table
NLSS_Library	QQNLNM	NLSS library
Start_Timestamp	QQSTIM	Start timestamp, when available.
End_Timestamp	QQETIM	End timestamp, when available
Table_Total_Rows	QQTOTR	Total rows in table
Created_Index_Entries	QQRIDX	Number of entries in index created
Estimated_Rows_Selected	QQREST	Estimated number of rows selected
Index_Probe_Keys	QQFKEY	Keys selected thru index scan-key positioning
Index_Scan_Keys	QQKSEL	Keys selected thru index scan-key selection
Estimated_Join_Rows	QQAJN	Estimated number of joined rows
Estimated_Processing_Time	QQEPT	Estimated processing time, in seconds
Join_Position	QQJNP	Join position - when available
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 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
Index_Advised_Probe_Count	QQI2	Number of advised key columns that use index scan-key positioning
Index_Probe_Used	QQKP	Index scan-key positioning Y - Yes N - No
Index_Probe_Column_Count	QQI3	Number of columns that use index scan-key positioning for the index used
Index_Scan_Used	QQKS	Index scan-key selection Y - Yes N - No
DataSpace_Selection	QQDSS	Dataspace selection Y - Yes N - No
Index_Advised	QQIDXA	Index advised Y - Yes N - No
Reason_Code	QQRCOD	Reason code I1 - Row selection I2 - Ordering/Grouping I3 - Row selection and Ordering/Grouping I4 - Nested loop join
Index_Advised_Columns	QQIDXD	Key columns for index advised
Created_Index_Columns	QQ1000	Key columns for index created
Table_Name	QVQTBL	Queried table, long name
Table_Schema	QVQLIB	Schema of queried table, long name
Base_Table_Name	QVPTBL	Base table, long name
Base_Table_Schema	QVPLIB	Schema of base table, long name
Index_Name	QVINAM	Name of index (or constraint) used, long name
Index_Schema	QVILIB	Schema of index used, long name
Bound	QVBNDY	I/O or CPU bound. Possible values are: I - I/O bound C - CPU bound
Unique_Refresh_Counter	QVRCNT	Unique refresh counter
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
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_Cumulative_Time	QVCTIM	Estimated cumulative time, in seconds
Created_Index_Name	QQC101	Name of index created - when available
Created_Index_Schema	QQC102	Schema of index created - when available
Created_Index_Page_Size	QQI4	Page size of index created
Created_Index_Row_Size	QQI5	Row size of index created
Created_Index_Used_ACS_Table	QQC14	Index Created used Alternate Collating Sequence Table (Y/N)
Created_Index_ACS_Table	QQC103	Alternate Collating Sequence table of index created.
Created_Index_ACS_Library	QQC104	Alternate Collating Sequence library of index created.
Created_Index_Reusable	QVC13	Index created is reusable (Y/N)
Created_Index_Sparse	QVC14	Index created is sparse index (Y/N)
Created_Index_Type	QVC1F	Type of index created. Possible values: B - Binary Radix Index E - Encoded Vector Index (EVI)
Created_Index_Unique_EVI_Count	QVP15F	Number of unique values of index created if index created is an EVI index.
Permanent_Index_Created	QVC15	Permanent index created (Y/N)
Index_From_Index	QVC16	Index from index (Y/N)
Created_Index_Parallel_Degree_Requested	QVP151	Parallel degree requested (index created)
Created_Index_Parallel_Degree_Used	QVP152	Parallel degree used (index created)
Created_Index_Parallel_Degree_Reason_Code	QVP153	Reason parallel processing was limited (index created)
Index_Only_Access	QVC17	Index only access (Y/N)
Index_Fits_In_Memory	QVC18	Index fits in memory (Y/N)
Index_Type	QVC1B	Type of Index. Possible values are: B - Binary Radix Index C - Constraint (Binary Radix) E - Encoded Vector Index (EVI) T - Tertiary (AND/OR) Index
Index_Entries	QQI6	Number of index entries, index used
Unique_Keys	QQI7	Number of unique key values, index used
Percent_Overflow	QVP158	Percent overflow, index used
Vector_Size	QVP159	Vector size, index used
Index_Size	QQI8	Size of index used.
Index_Page_Size	QVP156	Index page size
Pool_Size	QVP154	Pool size
Pool_ID	QVP155	Pool id
Table_Size	QVP157	Table size
Skip_Sequential_Table_Scan	QVC1C	Skip sequential table scan (Y/N)
DataSpace_Selection_Columns	QVC3001	Columns used for dataspace selection
Derived_Column_Selection	QVC1E	Derived column selection (Y/N)
Derived_Column_Selection_Columns	QVC3002	Columns used for derived column selection
Table_Columns_For_Index_Probe	QVC3003	Columns used for index scan-key positioning
Table_Columns_For_Index_Scan	QVC3004	Columns used for index scan-key selection
Read_Trigger	QQC18	Read Trigger (Y/N)
MQT_Replacement	QQC13	Materialized Query Table replaced queried table (Y/N)
Reused_Temporary_Index	QQC16	Temporary index reused (Y/N)