Database monitor view 3023 - Temp Hash Table Created

Displays the SQL logical view format for database monitor QQQ3023.


Create View QQQ3023 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, 
          QVRCNT as Unique_Refresh_Counter, 
          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, 
          QQREST as Estimated_Rows_Selected, 
          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, 
          QVJFANO as Join_Fanout, 
          QVFILES as Join_Table_Count, 
          QVC1F as HashTable_ReasonCode, 
          QQI2 as HashTable_Entries, 
          QQI3 as HashTable_Size, 
          QQI4 as HashTable_Row_Size, 
          QQI5 as HashTable_Key_Size, 
          QQIA as HashTable_Element_Size, 
          QQI7 as HashTable_PoolSize, 
          QQI8 as HashTable_PoolID, 
          QVC101 as HashTable_Name, 
          QVC102 as HashTable_Library, 
          QVC3001 as HashTable_Columns, 
          QQINT03 as Storage_Estimate, 
          QQSMINTF as Plan_Iteration_Number,
          QQC11 as Entry_Count_Modified_By_AQP 
   FROM   UserLib/DBMONTable 
   WHERE  QQRID=3023)

Table 1. QQQ3023 - Temp Hash Table 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
Unique_Refresh_Counter	QVRCNT	Unique refresh counter
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_Processing_Time	QQEPT	Estimated processing time, in seconds
Estimated_Cumulative_Time	QVCTIM	Estimated cumulative time, in seconds
Estimated_Rows_Selected	QQREST	Estimated rows selected
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
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
HashTable_ReasonCode	QVC1F	Hash table reason code J - Created for hash join G - Created for hash grouping
HashTable_Entries	QQI2	Hash table entries
HashTable_Size	QQI3	Hash table size
HashTable_Row_Size	QQI4	Hash table row size
HashTable_Key_Size	QQI5	Hash table key size
HashTable_Element_Size	QQIA	Hash table element size
HashTable_PoolSize	QQI7	Hash table pool size
HashTable_PoolID	QQI8	Hash table pool ID
HashTable_Name	QVC101	Hash table internal name
HashTable_Library	QVC102	Hash table library
HashTable_Columns	QVC3001	Columns used to create hash table
Storage_Estimate	QQINT03	Estimated amount of temporary storage used, in megabytes, to create the hash table. Only set by CQE.
Plan_Iteration_Number	QQSMINTF	AQP Plan iteration number, original optimization = 1
Entry_Count_Modified_By_AQP	QQC11	If the number of entries available in a hash table is modified by AQP, QQC11 contains 'Y'. Otherwise, QQC11 contains 'N'. QQI2 normally contains the optimizer-determined number of entries in a hash table. If QQC11 is 'Y', QQI2 contains the number of entries that were determined by AQP.