Database monitor view 3014 - Generic QQ Information

Displays the SQL logical view format for database monitor QQQ3014.


Create View QQQ3014 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, 
          QQREST as Estimated_Rows_Selected, 
          QQEPT as Estimated_Processing_Time, 
          QQI1 as Open_Time, 
          QQORDG as Has_Ordering, 
          QQGRPG as Has_Grouping, 
          QQJNG as Has_Join, 
          QQC22 as Join_Type, 
          QQUNIN as Has_Union, 
          QQSUBQ as Has_Subquery, 
          QWC1F as Has_Scalar_Subselect, 
          QQHSTV as Has_Host_Variables, 
          QQRCDS as Has_Row_Selection, 
          QQC11 as Query_Governor_Enabled, 
          QQC12 as Stopped_By_Query_Governor, 
          QQC101 as Open_Id, 
          QQC102 as Query_Options_Library, 
          QQC103 as Query_Options_Table_Name, 
          QQC13 as Early_Exit, 
          QVRCNT as Unique_Refresh_Counter,
          QQI5 as Optimizer_Time, 
          QQTIM1 as Access_Plan_Timestamp, 
          QVC11 as Ordering_Implementation, 
          QVC12 as Grouping_Implementation, 
          QVC13 as Join_Implementation, 
          QVC14 as Has_Distinct, 
          QVC15 as Is_Distributed,
          QVC3001 as Distributed_Nodes, 
          QVC105 as NLSS_Table, 
          QVC106 as NLSS_Library, 
          QVC16 as ALWCPYDATA, 
          QVC21 as Access_Plan_Reason_Code, 
          QVC22 as Access_Plan_Reason_SubCode, 
          QVC3002 as Summary, 
          QWC16 as Last_Union_Subselect, 
          QVP154 as Query_PoolSize, 
          QVP155 as Query_PoolID, 
          QQI2 as Query_Time_Limit, 
          QVC81 as Parallel_Degree, 
          QQI3 as Max_Number_of_Tasks,
          QVC17 as Apply_CHGQRYA_Remote, 
          QVC82 as Async_Job_Usage, 
          QVC18 as Force_Join_Order_Indicator, 
          QVC19 as Print_Debug_Messages, 
          QVC1A as Parameter_Marker_Conversion, 
          QQI4 as UDF_Time_Limit, 
          QVC1283 as Optimizer_Limitations, 
          QVC1E as Reoptimize_Requested, 
          QVC87 as Optimize_All_Indexes, 
          QQC14 as Has_Final_Decomposed_QDT, 
          QQC15 as Is_Final_Decomposed_QDT, 
          QQC18 as Read_Trigger, 
          QQC81 as Star_Join, 
          SUBSTR(QVC23,1,1) as Optimization_Goal, 
          SUBSTR(QVC24,1,1) as VE_Diagram_Type, 
          SUBSTR(QVC24,2,1) as Ignore_Like_Redunant_Shifts, 
          QQC23 as Union_QDT, 
          QQC21 as Unicode_Normalization, 
          QVP153 as Pool_Fair_Share, 
          QQC82 as Force_Join_Order_Requested, 
          QVP152 as Force_Join_Order_Dataspace1, 
          QQI6 as No_Parameter_Marker_Reason_Code, 
          QVP151 as Hash_Join_Reason_Code, 
          QQI7 as MQT_Refresh_Age, 
          SUBSTR(QVC42,1,1) as MQT_Usage, 
          QVC43 as SQE_NotUsed_Reason_Code, 
          QVP156 as Estimated_IO_Count, 
          QVP157 as Estimated_Processing_Cost, 
          QVP158 as Estimated_CPU_Cost, 
          QVP159 as Estimated_IO_Cost, 
          SUBSTR(QVC44,1,1) as Has_Implicit_Numeric_Conversion, 
          QVCTIM as Accumulated_Est_Process_Time, 
          QQINT01 as Query_Gov_Storage_Limit, 
          QQINT02 as Estimated_Storage, 
          QQINT03 as Adjusted_Temp_Storage, 
          QQINT04 as Original_Cost_Estimate, 
          QQI8 as Parallel_Degree_Percentage, 
          QFC12 as FieldProc_Encoded_Comparison, 
          QFC13 as Allow_Array_Changes_INI_Opt, 
          QFC11 as SQL_Concurrent_Access_Resolution, 
          QQSMINTF as Plan_Iteration_Number, 
          QXC11 as Warm_IO_Requested, 
          QXC12 as Warm_IO_Used, 
          QXC13 as Optimization_ Goal_Override, 
          QXC1E as Plan_Signature_Match,
          QXC14 as Check_HostVars,
          QXC15 as FullOptimization,
          QXC16 as Pseudo_Open_Replace_Reason,
          QXC17 as WorkloadGroup,
          QXC18 as Concurrent_Access_Behavior,
          QQINT05 as Memory_Pool_Preference,
          QQINT06 as Longest_Key_Range_Estimate,
          QXC19 as KeyRangeEst_Timeout,
          QQC16 as SQE_Used_Indicator,
          QQC83 as QRO_Hash,
          QQSMINT1 as Shared_CTE_Usage
   FROM   UserLib/DBMONTable 
   WHERE  QQRID=3014)

Table 1. QQQ3014 - Generic QQ Information
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
Estimated_Rows_Selected	QQREST	Estimated number of rows selected
Estimated_Processing_Time	QQEPT	Estimated processing time, in seconds
Open_Time	QQI1	Time spent to open cursor, in milliseconds
Has_Ordering	QQORDG	Ordering (Y/N)
Has_Grouping	QQGRPG	Grouping (Y/N)
Has_Join	QQJNG	Join Query (Y/N)
Join_Type	QQC22	Join type - when available IN - Inner join PO - Left partial outer join EX - Exception join
Has_Union	QQUNIN	Union Query (Y/N)
Has_Subquery	QQSUBQ	Subquery (Y/N)
Has_Scalar_Subselect	QWC1F	Scalar Subselects (Y/N)
Has_Host_Variables	QQHSTV	Host variables (Y/N)
Has_Row_Selection	QQRCDS	Row selection (Y/N)
Query_Governor_Enabled	QQC11	Query governor enabled (Y/N)
Stopped_By_Query_Governor	QQC12	Query governor stopped the query (Y/N)
Open_Id	QQC101	Query open ID
Query_Options_Library	QQC102	Query Options library name
Query_Options_Table_Name	QQC103	Query Options file name
Early_Exit	QQC13	Query early exit value
Unique_Refresh_Counter	QVRCNT	Unique refresh counter
Optimizer_Time	QQI5	Time spent in optimizer, in milliseconds
Access_Plan_Timestamp	QQTIM1	Access Plan rebuilt timestamp, last time access plan was rebuilt.
Ordering_Implementation	QVC11	Ordering implementation. Possible values are: I - Index S - Sort
Grouping_Implementation	QVC12	Grouping implementation. Possible values are: I - Index H - Hash grouping
Join_Implementation	QVC13	Join Implementation. Possible values are: N - Nested Loop join H - Hash join C - Combination of Nested Loop and Hash
Has_Distinct	QVC14	Distinct query (Y/N)
Is_Distributed	QVC15	Distributed query (Y/N)
Distributed_Nodes	QVC3001	Distributed nodes
NLSS_Table	QVC105	Sort Sequence Table
NLSS_Library	QVC106	Sort Sequence Library
ALWCPYDATA	QVC16	ALWCPYDTA setting
Access_Plan_Reason_Code	QVC21	Reason code why access plan was rebuilt
Access_Plan_Reason_SubCode	QVC22	Subcode why access plan was rebuilt
Summary	QVC3002	Summary of query implementation. Shows dataspace number and name of index used for each table being queried.
Last_Union_Subselect	QWC16	Last part (last QDT) of Union (Y/N)
Query_PoolSize	QVP154	Pool size
Query_PoolID	QVP155	Pool id
Query_Time_Limit	QQI2	Query time limit
Parallel_Degree	QVC81	Parallel Degree SAME - Do not change current setting NONE - No parallel processing is allowed I/O - Any number of tasks might be used for I/O processing. SMP parallel processing is not allowed. OPTIMIZE - The optimizer chooses the number of tasks to use for either I/O or SMP parallel processing. MAX - The optimizer chooses to use either I/O or SMP parallel processing. SYSVAL - Use the current system value to process the query. ANY - Has the same meaning as I/O. *NBRTASKS - The number of tasks for SMP parallel processing is specified in column QVTASKN.
Max_Number_of_Tasks	QQI3	Max number of tasks
Apply_CHGQRYA_Remote	QVC17	Apply CHGQRYA remotely (Y/N)
Async_Job_Usage	QVC82	Asynchronous job usage SAME - Do not change current setting DIST - Asynchronous jobs might be used for queries with distributed tables LOCAL - Asynchronous jobs might be used for queries with local tables only ANY - Asynchronous jobs might be used for any database query *NONE - No asynchronous jobs are allowed
Force_Join_Order_Indicator	QVC18	Force join order (Y/N)
Print_Debug_Messages	QVC19	Print debug messages (Y/N)
Parameter_Marker_Conversion	QVC1A	Parameter marker conversion (Y/N)
UDF_Time_Limit	QQI4	User Defined Function time limit
Optimizer_Limitations	QVC1283	Optimizer limitations. Possible values: PERCENT followed by 2 byte integer containing the percent value MAX_NUMBER_OF_RECORDS followed by an integer value that represents the maximum number of rows
Reoptimize_Requested		Reoptimize access plan requested. Possible values are: O - Only reoptimize the access plan when required. Do not reoptimize for subjective reasons. Y - Yes, force the access plan to be reoptimized. N - No, do not reoptimize the access plan, unless optimizer determines that it is necessary. May reoptimize for subjective reasons.
Optimize_All_Indexes		Optimize all indexes requested SAME - Do not change current setting YES - Examine all indexes NO - Allow optimizer to time out TIMEOUT - Force optimizer to time out
Has_Final_Decomposed_QDT	QQC14	Final decomposed QDT built indicator (Y/N)
Is_Final_Decomposed_QDT	QQC15	The final decomposed QDT indicator (Y/N)
Read_Trigger	QQC18	One of the files contains a read trigger (Y/N)
Star_Join	QQC81	Star join optimization requested. NO - Star join optimization is not performed. COST - The optimizer determines if any EVIs can be used for star join optimization. *FORCE - The optimizer adds any EVIs that can be used for star join optimization.
Optimization_Goal	QVC23	Byte 1 = Optimization goal. Possible values are: F - First I/O, optimize the query to return the first screen full of rows as quickly as possible. A - All I/O, optimize the query to return all rows as quickly as possible.
VE_Diagram_Type	QVC24	Byte 1 = Type of Visual Explain diagram. Possible values are: D - Detail B - Basic
Ignore_Like_Redunant_Shifts	QVC24	Byte 2 - Ignore LIKE redundant shifts. Possible values are: O - Optimize, the query optimizer determines which redundant shifts to ignore. A - All redundant shifts are ignored.
Union_QDT	QQC23	Byte 1 = This QDT is part of a UNION that is contained within a view (Y/N). Byte 2 = This QDT is the last subselect of the UNION that is contained within a view (Y/N).
Unicode_Normalization	QQC21	Unicode data normalization requested (Y/N)
Pool_Fair_Share	QVP153	Fair share of the pool size as determined by the optimizer
Force_Join_Order_Requested	QQC82	Force Join Order requested. Possible values are: NO - The optimizer was allowed to reorder join files YES - The optimizer was not allowed to reorder join files as part of its optimization process SQL - The optimizer only forced the join order for those queries that used the SQL JOIN syntax PRIMARY - The optimizer was only required to force the primary dial for the join.
Force_Join_Order_Dataspace1	QVP152	Primary dial to force if Force_Join_Order_Indicator is *PRIMARY.
No_Parameter_Marker_Reason_Code	QQI6	Reason code for why Parameter Marker Conversion was not performed: Argument of function must be a literal LOCALTIME or LOCALTIMESTAMP Duration literal in arithmetic expression UPDATE query with no WHERE clause BLOB literal Special register in UPDATE or INSERT with values Result expression for CASE GROUP BY expression ESCAPE character Double Negative value -(-1) INSERT or UPDATE with a mix of literals, parameter markers, and NULLs UPDATE with a mix of literals and parameter markers INSERT with VALUES containing NULL value and expressions UPDATE with list of expressions 99. Parameter marker conversion disabled by QAQQINI
Hash_Join_Reason_Code	QVP151	Reason code why hash join was not used.
MQT_Refresh_Age	QQI7	Value of the MATERIALIZED_QUERY_TABLE_REFRESH_AGE duration. If the QAQQINI parameter value is set to *ANY, the timestamp duration is 99999999999999.
MQT_Usage	QVC42,1,1	Byte 1 - Contains the MATERIALIZED_QUERY_TABLE_USAGE. Possible values are: N - NONE - no materialized query tables used in query optimization and implementation A - ALL - User-maintained. Refresh-deferred query tables can be used. U - *USER - Only user-maintained materialized query tables can be used.
SQE_NotUsed_Reason_Code	QVC43	SQE Not Used Reason Code. Possible values: LF - DDS logical file specified in query definition DK - An index with derived key or select/omit was found over a queried table NF - Too many tables in query NS - Not an SQL query or query not run through an SQL interface DF - Distributed table in query RT - Read Trigger defined on queried table PD - Program described file in query WC - WHERE CURRENT OF a partition table IO - Simple INSERT query CV - Create view statement
Estimated_IO_Count	QVP156	Estimated I/O count
Estimated_Processing_Cost	QVP157	Estimated processing cost in milliseconds
Estimated_CPU_Cost	QVP158	Estimated CPU cost in milliseconds
Estimated_IO_Cost	QVP159	Estimated I/O cost in milliseconds
Has_Implicit_Numeric_Conversion	QVC44	Byte 1: Implicit numeric conversion (Y/N)
Accumulated_Est_Process_Time	QVCTIM	Accumulated estimated processing time across all subselects, in seconds.
Query_Gov_Storage_Limit	QQINT01	Specified query governor storage limit, in megabytes
Estimated_Storage	QQINT02	Original estimated temporary storage used, in megabytes.
Adjusted_Temp_Storage	QQINT03	Adjusted temporary storage used, in Adjusted megabytes. This value accumulates the actual time and storage it took to create any temporary indexes and temporary tables. Set by CQE only.
Original_Cost_Estimate	QQINT04	Original cost estimate as determined by the CQE query optimizer. Set by CQE only.
Parallel_Degree_Percentage	QQI8	Percentage specified on Parallel_Degree OPTIMIZE and MAX.
FieldProc_Encoded_Comparison	QFC12	FIELDPROC_ENCODED_COMPARISON option active for this query. Specifies the amount of optimization that the optimizer might use when queried columns have attached field procedures. 'N' - NONE 'E' - ALLOW_ EQUAL 'R' - ALLOW_ RANGE 'A' - ALL
Allow_Array_Change_INI_Opt	QFC13	ALLOW_ARRAY_VALUE_CHANGES QAQQINI option active for this query. 'N' - Do not allow changes to values in arrays referenced in the query to be visible after the query is opened. 'Y' - Allow changes to values in arrays to be visible to the query while the query is running.
SQL_Concurrent_Access_Resolution	QFC11	SQL_CONCURRENT_ACCESS_RESOLUTION QAQQINI option active for this query. 'U' - USE CURRENTLY COMMITTED 'W' - WAIT FOR OUTCOME
Plan_Iteration_Number	QQSMINTF	AQP Plan iteration number; original optimization = 1
Warm_IO_Requested	QXC11	Warm I/O value that was requested. 'Y' - Yes, use Warm I/O 'N' - No, do not use Warm I/O 'D' - Default
Warm_IO_Used	QXC12	Warm I/O values used to implement the query. 'Y' - Yes, use Warm I/O 'N' - No, do not use Warm I/O 'D' - Default
Optimization_Goal_Override	QXC13	Optimization Goal Override. 'O' - Override the specified Optimize For N Rows value and use Optimize For All Rows. 'D' - Default, use the specified Optimize For N Rows value.
Plan_Signature_Match	QXC1E	Plan signature match. 'Y' - New plan matched old plan it replaced; same plan signature. 'N' - New plan different from old plan it replaced; different plan signature.
Check_HostVars	QXC14	Indicates if this query is enabled for host variable selectivity checking at pseudo-open time. ‘N’ – No pseudo-open host variable checking enabled ‘O’ – This query is a candidate for pseudo-open host variable checking and the QAQQINI option was OPTIMIZE ‘Y’ - This query is a candidate for pseudo-open host variable checking and the QAQQINI option was YES
FullOptimization	QXC15	Plan was rebuilt (Y/N)
Pseudo_Open_Replace_Reason	QXC16	Indicates if the plan was replaced due to QAQQINI PSEUDO_OPEN_CHECK_HOST_VARS option '0' - Plan was not replaced '1' - Plan was replaced
WorkloadGroup	QXC17	Workload Group is in effect (Y/N)
Concurrent_Access_Behavior	QXC18	Controls how queries with an isolation level of Cursor Stability (CS) or Read Stability (RS) interact with uncommitted table changes. 'N' - Not Applicable 'O' - OPTIMIZE - Uncommitted changes that delete or update records so that they are no longer selected by the query will not be considered as candidates for query synchronization. 'S' - STRICTSCAN - All records referenced by a table scan query access plan will synchronize with any changes that are not yet committed.
Memory_Pool_Preference	QQINT05	The pool identifier where paging will occur for database operation that supports targeted paging. The pool identifier will be 0 if paging will occur in the same pool as the job is running in.
Longest_Key_Range_Estimate	QQINT06	Number of seconds required to run the longest key range estimate (or EKR) used to optimize this query.
KeyRangeEst_Timeout	QXC19	Indicates whether a key range estimate exceeded the time limit. This limit may be specified in the QAQQINI file. 'N' - No 'Y' - One or more estimates exceeded the time limit. The estimates have been queued for background processing.
SQE_Used_Indicator	QQC16	Indicator of which optimizer was used 'Y' - Query ran using SQE 'N' - Query ran using CQE
QRO_HASH	QQC83	For SQE queries, the QRO hash is an internally generated identifier for an SQE query. In general, this identifier will be unique for each SQE query and uses implicit schema qualification among other data to generate the QRO hash. If the SQE optimizer generates multiple plans for the same query, then multiple plans will have the same QRO hash. The QRO hash for a statement may change on release boundaries or after loading PTFs.
Shared_CTE_Usage	QQSMINT1	For SQE queries, this field describes whether the query uses a common table expression (CTE) that is referenced more than once in the query. This is referred to as a shared CTE and requires special processing by the optimizer. 0 Query does not contain CTEs with multiple references. 1 Query contains at least one CTE with multiple references. 2 Query contains at least one CTE with multiple references and at least one of these CTEs is considered complex by the optimizer.