Database monitor view 3000 - Table Scan
Create View QQQ3000 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, QQTOTR as Table_Total_Rows, QQREST as Estimated_Rows_Selected, 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_Columns_Count, QQDSS as DataSpace_Selection, QQIDXA as Index_Advised, QQRCOD as Reason_Code, QQIDXD as Index_Advised_Columns, QVQTBL as Table_Name, QVQLIB as Table_Schema, QVPTBL as Base_Table_Name, QVPLIB as Base_Table_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, QQC11 as Skip_Sequential_Table_Scan, QQI3 as Table_Size, QVC3001 as DataSpace_Selection_Columns, QQC14 as Derived_Column_Selection, QVC3002 as Derived_Column_Selection_Columns, QQC18 as Read_Trigger, QVP157 as UDTF_Cardinality, QVC1281 as UDTF_Specific_Name, QVC1282 as UDTF_Specific_Schema, QVP154 as Pool_Size, QVP155 as Pool_Id, QQC13 as MQT_Replacement FROM UserLib/DBMONTABLE WHERE QQRID=3000)
Table 1. QQQ3000 - Table Scan 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 Table_Total_Rows QQTOTR Total rows in table Estimated_Rows_Selected QQREST Estimated number of rows selected 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_Columns_Count QQI2 Number of advised columns that use index scan-key positioning DataSpace_Selection QQDSS Dataspace selection
- Y - Yes
- N - No
Index_Advised QQIDXA Index advised
- Y - Yes
- N - No
Reason_Code QQRCOD Reason code
- T1 - No indexes exist.
- T2 - Indexes exist, but none can be used.
- T3 - Optimizer chose table scan over available indexes.
Index_Advised_Columns QQIDXD Columns for the index advised 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 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 (Y/N) Parallel_Degree_Requested QVPARD Parallel degree requested Parallel_Degree_Used QVPARU Parallel degree used Parallel_Degree_Reason_Code QVPARRC Reason parallel processing was limited Estimated_Cumulative_Time QVCTIM Estimated cumulative time, in seconds Skip_Sequential_Table_Scan QQC11 Skip sequential table scan (Y/N) Table_Size QQI3 Size of table being queried DataSpace_Selection_Columns QVC3001 Columns used for dataspace selection Derived_Column_Selection QQC14 Derived column selection (Y/N) Derived_Column_Selection_Columns QVC3002 Columns used for derived column selection Read_Trigger QQC18 Read Trigger (Y/N) UDTF_Cardinality QVP157 User-defined table function Cardinality UDTF_Specific_Name QVC1281 User-defined table function specific name UDTF_Specific_Schema QVC1282 User-defined table function specific schema Pool_Size QVP154 Pool size Pool_Id QVP155 Pool id MQT_Replacement QQC13 Materialized Query Table replaced queried table (Y/N)
Parent topic:
Optional database monitor SQL view format