Database monitor view 3007 - Optimizer Timed Out

Database monitor view 3007 - Optimizer Timed Out
Create View QQQ3007 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, 
          QQ1000 as Index_Names, 
          QQC11 as Optimizer_Timed_Out, 
          QQC301 as Reason_Codes, 
          QVQTBL as Table_Name, 
          QVQLIB as Table_Schema, 
          QVPTBL as Base_Table_Name, 
          QVPLIB as Base_Table_Schema, 
          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, 
          QVRCNT as Unique_Refresh_Counter    FROM   UserLib/DBMONTable    WHERE  QQRID=3007) 
Table 1. QQQ3007 - Optimizer Timed Out
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
Index_Names QQ1000 Names of indexes not used and reason code.

Access path was not in a valid state. The system invalidated the access path.

Access path was not in a valid state. The user requested that the access path be rebuilt.

Access path is a temporary access path (resides in library QTEMP) and was not specified as the file to be queried.

The cost to use this access path, as determined by the optimizer, was higher than the cost associated with the chosen access method.

The keys of the access path did not match the fields specified for the ordering/grouping criteria. For distributed file queries, the access path keys must exactly match the ordering fields if the access path is to be used when ALWCPYDTA(*YES or *NO) is specified.

The keys of the access path did not match the fields specified for the join criteria.

Use of this access path will not minimize delays when reading records from the file. The user requested to minimize delays when reading records from the file.

The access path cannot be used for a secondary file of the join query because it contains static select/omit selection criteria. The join-type of the query does not allow the use of select/omit access paths for secondary files.

File contains record ID selection. The join-type of the query forces a temporary access path to be built to process the record ID selection.

The user specified ignore decimal data errors on the query. This disallows the use of permanent access paths.

Index_Names (continued) QQ1000

11. The access path contains static select/omit selection criteria which is not compatible with the selection in the query.

12. The access path contains static select/omit selection criteria whose compatibility with the selection in the query cannot be determined. Either the select/omit criteria or the query selection became too complex during compatibility processing.

13. The access path contains one or more keys which may be changed by the query during an insert or update.

14. The access path is being deleted or is being created in an uncommitted unit of work in another process.

15. The keys of the access path matched the fields specified for the ordering/grouping criteria. However, the sequence table associated with the access path did not match the sequence table associated with the query.

16. The keys of the access path matched the fields specified for the join criteria. However, the sequence table associated with the access path did not match the sequence table associated with the query.

17. The left-most key of the access path did not match any fields specified for the selection criteria. Therefore, key row positioning cannot be performed, making the cost to use this access path higher than the cost associated with the chosen access method.

18. The left-most key of the access path matched a field specified for the selection criteria. However, the sequence table associated with the access path did not match the sequence table associated with the query. Therefore, key row positioning cannot be performed, making the cost to use this access path higher than the cost associated with the chosen access method.

19. The access path cannot be used because the secondary file of the join query is a select/omit logical file. The join-type requires that the select/omit access path associated with the secondary file be used or, if dynamic, that an access path be created by the system.

Optimizer_Timed_Out QQC11 Optimizer timed out (Y/N)
Reason_Codes QQC301 List of unique reason codes used by the indexes that timed out (each index has a corresponding reason code associated with it)
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 Schema of base table, long name
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
Unique_Refresh_Counter QVRCNT Unique refresh counter

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
Index_Names	QQ1000	Names of indexes not used and reason code. Access path was not in a valid state. The system invalidated the access path. Access path was not in a valid state. The user requested that the access path be rebuilt. Access path is a temporary access path (resides in library QTEMP) and was not specified as the file to be queried. The cost to use this access path, as determined by the optimizer, was higher than the cost associated with the chosen access method. The keys of the access path did not match the fields specified for the ordering/grouping criteria. For distributed file queries, the access path keys must exactly match the ordering fields if the access path is to be used when ALWCPYDTA(YES or NO) is specified. The keys of the access path did not match the fields specified for the join criteria. Use of this access path will not minimize delays when reading records from the file. The user requested to minimize delays when reading records from the file. The access path cannot be used for a secondary file of the join query because it contains static select/omit selection criteria. The join-type of the query does not allow the use of select/omit access paths for secondary files. File contains record ID selection. The join-type of the query forces a temporary access path to be built to process the record ID selection. The user specified ignore decimal data errors on the query. This disallows the use of permanent access paths.
Index_Names (continued)	QQ1000	11. The access path contains static select/omit selection criteria which is not compatible with the selection in the query. 12. The access path contains static select/omit selection criteria whose compatibility with the selection in the query cannot be determined. Either the select/omit criteria or the query selection became too complex during compatibility processing. 13. The access path contains one or more keys which may be changed by the query during an insert or update. 14. The access path is being deleted or is being created in an uncommitted unit of work in another process. 15. The keys of the access path matched the fields specified for the ordering/grouping criteria. However, the sequence table associated with the access path did not match the sequence table associated with the query. 16. The keys of the access path matched the fields specified for the join criteria. However, the sequence table associated with the access path did not match the sequence table associated with the query. 17. The left-most key of the access path did not match any fields specified for the selection criteria. Therefore, key row positioning cannot be performed, making the cost to use this access path higher than the cost associated with the chosen access method. 18. The left-most key of the access path matched a field specified for the selection criteria. However, the sequence table associated with the access path did not match the sequence table associated with the query. Therefore, key row positioning cannot be performed, making the cost to use this access path higher than the cost associated with the chosen access method. 19. The access path cannot be used because the secondary file of the join query is a select/omit logical file. The join-type requires that the select/omit access path associated with the secondary file be used or, if dynamic, that an access path be created by the system.
Optimizer_Timed_Out	QQC11	Optimizer timed out (Y/N)
Reason_Codes	QQC301	List of unique reason codes used by the indexes that timed out (each index has a corresponding reason code associated with it)
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	Schema of base table, long name
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
Unique_Refresh_Counter	QVRCNT	Unique refresh counter