Database monitor view 3004

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

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)