Database monitor view 3004 - Temp Table
Create View QQQ3004 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, QQSTIM as Start_Timestamp, QQETIM as End_Timestamp, QQC11 as Has_Default_Values, QQTMPR as Table_Rows, QQRCOD as Reason_Code, QVQTBL as Table_Name, QVQLIB as Table_Schema, QVPTBL as Base_Table_Name, QVPLIB as Base_Table_Schema, QQC101 as Temporary_Table_Name, QQC102 as Temporary_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, QQEPT as Estimated_Processing_Time, QVCTIM as Estimated_Cumulative_Time, QQAJN as Estimated_Join_Rows, QQJNP as Join_Position, QQI6 as DataSpace_Number, QQC21 as Join_Method, QQC22 as Join_Type, QQC23 as Join_Operator, QQI2 as Temporary_Table_Row_Size, QQI3 as Temporary_Table_Size, QQC12 as Temporary_Query_Result, QQC13 as Distributed_Temporary_Table, QVC3001 as Distributed_Temporary_Data_Nodes, QQI7 as Materialized_Subqery_QDT_Level, QQI8 as Materialized_Union_QDT_Level, QQC14 as View_Contains_Union FROM UserLib/DBMONTable WHERE QQRID=3004)
Table 1. QQQ3004 - Temp Table 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 Start_Timestamp QQSTIM Start timestamp, when available End_Timestamp QQETIM End timestamp, when available Has_Default_Values QQC11 Default values may be present in temporary
- Y - Yes
- N - No
Table_Rows QQTMPR Estimated number of rows in the temporary Reason_Code QQRCOD Reason code. Possible values are:
- F1 - Query contains grouping columns (GROUP BY) from more that one table, or contains grouping columns from a secondary table of a join query that cannot be reordered.
- F2 - Query contains ordering columns (ORDER BY) from more that one table, or contains ordering columns from a secondary table of a join query that cannot be reordered.
- F3 - The grouping and ordering columns are not compatible.
- F4 - DISTINCT was specified for the query.
- F5 - UNION was specified for the query.
- F6 - Query had to be implemented using a sort. Key length of more than 2000 bytes or more than 120 key columns specified for ordering.
- F7 - Query optimizer chose to use a sort rather than an index to order the results of the query.
- F8 - Perform specified row selection to minimize I/O wait time.
- F9 - The query optimizer chose to use a hashing algorithm rather than an index to perform the grouping.
- FA - The query contains a join condition that requires a temporary table
- FB - The query optimizer creates a run-time temporary file in order to implement certain correlated group by queries.
- FC - The query contains grouping fields and there is a read trigger on at least one of the physical files in the query.
- FD - The query optimizer creates a runtime temporary file for a static-cursor request.
- H1 - Table is a join logical file and its join type does not match the join type specified in the query.
- H2 - Format specified for the logical table references more than one base table.
- H3 - Table is a complex SQL view requiring a temporary table to contain the results of the SQL view.
- H4 - For an update-capable query, a subselect references a column in this table which matches one of the columns being updated.
- H5 - For an update-capable query, a subselect references an SQL view which is based on the table being updated.
- H6 - For a delete-capable query, a subselect references either the table from which rows are to be deleted, an SQL view, or an index based on the table from which rows are to be deleted
- H7 - A user-defined table function was materialized.
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 Library of base table, long name Temporary_Table_Name QQC101 Temporary table name Temporary_Table_Schema QQC102 Temporary table schema 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_Processing_Time QQEPT Estimated processing time, in seconds Estimated_Cumulative_Time QVCTIM Estimated cumulative time, in seconds Estimated_Join_Rows QQAJN Estimated number of joined rows Join_Position QQJNP Join position - when available DataSpace_Number QQI6 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
Temporary_Table_Row_Size QQI2 Row size of temporary table, in bytes Temporary_Table_Size QQI3 Estimated size of temporary table, in bytes. Temporary_Query_Result QQC12 Temporary result table that contains the results of the query. (Y/N) Distributed_Temporary_Table QQC13 Distributed Table (Y/N) Distributed_Temporary_Data_Nodes QVC3001 Data nodes of temporary table Materialized_Subqery_QDT_Level QQI7 Materialized subquery QDT level Materialized_Union_QDT_Level QQI8 Materialized Union QDT level View_Contains_Union QQC14 Union in a view (Y/N)
Parent topic:
Optional database monitor SQL view format