Database monitor view 3006 - Access Plan Rebuilt

Displays the SQL logical view format for database monitor QQQ3006.

Create View QQQ3006 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,
          QQRCOD as Reason_Code, 
          QQC21 as SubCode, 
          QVRCNT as Unique_Refresh_Counter, 
          QQTIM1 as Last_Access_Plan_Rebuild_Timestamp, 
          QQC11 as Reoptimization_Done, 
          QVC22 as Previous_Reason_Code, 
          QVC23 as Previous_SubCode, 
          QQSMINTF as Plan_Iteration_Number 
   FROM   UserLib/DBMONTable 
   WHERE  QQRID=3006)

Table 1. QQQ3006 - Access Plan Rebuilt
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
Reason_Code	QQRCOD	Reason code why access plan was rebuilt A1 - A table or member is not the same object as the one referenced when the access plan was last built. Some reasons they might be different are: Object was deleted and recreated. Object was saved and restored. Library list was changed. Object was renamed. Object was moved. Object was overridden to a different object. This is the first run of this query after the object containing the query has been restored. A2 - Access plan was built to use a reusable Open Data Path (ODP) and the optimizer chose to use a non-reusable ODP for this call. A3 - Access plan was built to use a non-reusable Open Data Path (ODP) and the optimizer chose to use a reusable ODP for this call. A4 - The number of rows in the table has changed by more than 10% since the access plan was last built. A5 - A new index exists over one of the tables in the query A6 - An index that was used for this access plan no longer exists or is no longer valid. A7 - IBM® i Query requires the access plan to be rebuilt because of system programming changes. A8 - The CCSID of the current job is different than the CCSID of the job that last created the access plan. A9 - The value of one or more of the following is different for the current job than it was for the job that last created this access plan: date format date separator time format time separator.
Reason_Code (continued)	QQRCOD	AA - The sort sequence table specified is different than the sort sequence table that was used when this access plan was created. AB - Storage pool changed or DEGREE parameter of CHGQRYA command changed. AC - The system feature DB2® multisystem has been installed or removed. AD - The value of the degree query attribute has changed. AE - A view is either being opened by a high level language or a view is being materialized. AF - A sequence object or user-defined type or function is not the same object as the one referred to in the access plan; or, the SQL path used to generate the access plan is different than the current SQL path. B0 - The options specified have changed as a result of the query options file. B1 - The access plan was generated with a commitment control level that is different in the current job. B2 - The access plan was generated with a static cursor answer set size that is different than the previous access plan. B3 - The query was reoptimized because this is the first run of the query after a prepare. That is, it is the first run with real actual parameter marker values. B4 - The query was reoptimized because referential or check constraints have changed. B5 - The query was reoptimized because MQTs have changed. B6 - The query was reoptimized because the value of a host variable changed and the access plan is no longer valid. B7 - The query was reoptimized because AQP determined that the query should be reoptimized.
SubCode	QQC21	If the access plan rebuild reason code was A7 this two-byte hex value identifies which specific reason for A7 forced a rebuild.
Unique_Refresh_Counter	QVRCNT	Unique refresh counter
Last_Access_Plan_Rebuild_Timestamp	QQTIM1	Timestamp of last access plan rebuild
Reoptimization_Done	QQC11	Required optimization for this plan. Y - Yes, plan was really optimized. N - No, the plan was not reoptimized because of the QAQQINI option for the REOPTIMIZE_ACCESS_PLAN parameter value
Previous_Reason_Code	QVC22	Previous reason code
Previous_SubCode	QVC23	Previous reason subcode
Plan_Iteration_Number	QQSMINTF	AQP Plan iteration number, original optimization = 1