Database monitor view 3014 - Generic QQ Information

Database monitor view 3014 - Generic QQ Information
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    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 - Don't change current setting

*NONE - No parallel processing is allowed

*I/O - Any number of tasks may 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 - Don't change current setting

*DIST - Asynchronous jobs may be used for queries with distributed tables

*LOCAL - Asynchronous jobs may be used for queries with local tables only

*ANY - Asynchronous jobs may 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 absolutely 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 - Don't 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 This is 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 will not be performed.

*COST - The optimizer will determine if any EVIs can be used for star join optimization.

*FORCE - The optimizer will add 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, all redundant shifts will be 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 will be 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:

XL - Translation used in query

XU - Translation for UTF used in query

UF - User Defined Table Function used in query

LF - DDS logical file specified in query definition

LC - Lateral correlation

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

SQE_NotUsed_Reason_Code (continued)

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)

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
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 - Don't change current setting NONE - No parallel processing is allowed I/O - Any number of tasks may 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 - Don't change current setting DIST - Asynchronous jobs may be used for queries with distributed tables LOCAL - Asynchronous jobs may be used for queries with local tables only ANY - Asynchronous jobs may 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 absolutely 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 - Don't 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	This is 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 will not be performed. COST - The optimizer will determine if any EVIs can be used for star join optimization. *FORCE - The optimizer will add 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, all redundant shifts will be 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 will be 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: XL - Translation used in query XU - Translation for UTF used in query UF - User Defined Table Function used in query LF - DDS logical file specified in query definition LC - Lateral correlation 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
SQE_NotUsed_Reason_Code (continued)		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)