Database monitor view 3007 - Optimizer Timed Out

Displays the SQL logical view format for database monitor QQQ3007.


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, 
          QQIDXNL as Index_Names_2, 
          QQSMINTF as Plan_iteration_number 
   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.
  1. Access path was not in a valid state. The system invalidated the access path.
  2. Access path was not in a valid state. The user requested that the access path be rebuilt.
  3. Access path is a temporary access path (resides in library QTEMP) and was not specified as the file to be queried.
  4. The cost to use this access path, as determined by the optimizer, was higher than the cost associated with the chosen access method.
  5. 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.
  6. The keys of the access path did not match the fields specified for the join criteria.
  7. 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.
  8. 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.
  9. 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.
  10. 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
Index_Names_2 QQ1000L Index names when the list will not fit into QQ1000. Set to null otherwise
Plan_Iteration_Number QQSMINTF AQP Plan iteration number, original optimization = 1