Database monitor logical table 3006 - Summary Row for Access Plan Rebuilt

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A*
     A* DB Monitor logical table 3006 - Summary Row for Access Plan Rebuilt 
     A* 
     A          R QQQ3006                   PTABLE(*CURLIB/QAQQDBMN) 
     A            QQRID 
     A            QQTIME 
     A            QQJFLD 
     A            QQRDBN 
     A            QQSYS 
     A            QQJOB 
     A            QQUSER 
     A            QQJNUM 
     A            QQTHRD                    RENAME(QQI9) + 
                                            COLHDG('Thread' + 
                                                   'Identifier') 
     A            QQUCNT 
     A            QQUDEF 
     A            QQQDTN 
     A            QQQDTL 
     A            QQMATN 
     A            QQMATL 
     A            QQINLN
     A            QQINFN	 
     A            QQRCOD 
     A* 
     A* Columns added for Visual Explain
     A*
     A            QVSUBRC                   RENAME(QQC21) + 
                                            COLHDG('Subtype' +
                                                   'Reason' + 
                                                   'Code')
     A            QVRCNT
     A            QVRPTS                    RENAME(QQTIM1) + 
                                            COLHDG('Timestamp' +
                                                   'Last' + 
                                                   'Rebuild')
     A          K QQJFLD 
     A          S QQRID                     CMP(EQ 3006)

Table 19. QQQ3006 - Summary Row for Access Plan Rebuilt

Logical Column Name Physical Column Name Description
QQRID QQRID Row identification
QQTIME QQTIME Time row was created
QQJFLD QQJFLD Join column (unique per job)
QQRDBN QQRDBN Relational database name
QQSYS QQSYS System name
QQJOB QQJOB Job name
QQUSER QQUSER Job user
QQJNUM QQJNUM Job number
QQTHRD QQI9 Thread identifier
QQUCNT QQUCNT Unique count (unique per query)
QQUDEF QQUDEF User defined column
QQQDTN QQQDTN Unique subselect number
QQQDTL QQQDTL Subselect nested level
QQMATN QQMATN Materialized view subselect number
QQMATL QQMATL Materialized view nested level
QQRCOD 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 could 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 - OS/400 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.

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 user-defined type or user-defined function is not the same object as the one referred to in the access plan.
B0 - The options specified have changed as a result of the query options file.

QVSUBRC QQC21 If the access plan rebuild reason code was A7 this reason code identifies which specific reason for A7 forced a rebuild. This value is a 2-byte hex value. The following values force a reoptimization.

X'0001' - Update specified and must check at run time or user forced reoptimizing.
X'0002' - If the query uses a select/omit index and also uses host variable values processed with V2 then we must reoptimize.
X'0003' - If the user intends to insert, update, or delete through a join cursor, make sure that the first table specified is the first dial in the join cursor. This check was not done when the access plan was created.
X'0004' - If the access plan indicates the query sort is to be used but the run time options do not allow it, then reoptimize.
X'0005' - Check to see if a fast sort is being attempted but one isn't allowed. If so reoptimize so the fast sort isn't used.
X'0006' - Need to reoptimize the query when the QDT requests the output to be forced to a temporary and the access plan does not already do this.
X'0007' - Reoptimize if we have a change in commitiment control level.
X'0008' - Reoptimize if we have a change in key feedback.
X'0009' - Reoptimize if pool size changed.
X'000A' - Reoptimize SMP PRODUCT status has changed.
X'000B' - Reoptimize if SMP parallelism or I/O parallelism is being used but the DEGREE query attribute is now *NONE.
X'000C' - Reoptimize if SMP parallel is being used but the DEGREE query attribute is now *IO.
X'000D' - Reoptimize if access plan was created for a view or updated during the direct open of a view, and it is now being materialized, or visa versa.
X'000E' - Reoptimize if SRVQRY is currently active and/or the access plan was created with SRVQRY active.
X'000F' - Reoptimize if it is a unique query, but table scan is used for the first dial.
X'0010' - Reoptimize if the query is set for hash join, but the last dial is not a hash join dial.
X'0011' - Reoptimize if access plan was created using index AND/OR and now index AND/OR is not allowed.
X'0012' - Reoptimize if an index used in the access plan is no longer usable.
X'0013' - Reoptimize if one, or more, of the index AND/OR indexes used in the access plan is no longer usable.
X'0014' - Reoptimize if the number rows is greater than 1000 and number of rows has changed by 10% (+ or -).
X'0015' - Reoptimize if a new permanent index has been created since the access plan was created.
X'0016' - Reoptimize if an index that was invalid when the access plan was created is now valid.
X'0017' and higher (excluding values starting with X'80xx') PTF changes that require the access plan to be reoptimized.

X'8001' - SQ requests a forced rebuild.
X'8002' - Revalidate if need to perform CHAR to ZONED conversions.
X'8003' - Revalidate if the query uses XLAT operators with CCSID conversion.
X'8004' - Revalidate if query uses subtraction or addition.
X'8005' - Revalidate if query uses maximum or minimum.
X'8006' - Revalidate if query uses at least one table that specifies NULL values to be returned.
X'8007' - Revalidate if query forced a revalidation.
X'8008' - Revalidate if the job default format separators for DATE or TIME columns have changed since the access plan was created.
X'8009' - Revalidate if default CCSID or final mapping CCSID is different now than when the access plan was created.
X'800A' - Revalidate if the current NLSS is different than the one saved in the access plan.
X'800B' - Revalidate if the SRTSEQ passed in is different that the SRTSEQ used when the access plan was created.
X'800C' - Revalidate if divide by zero preference has changed.
X'800D' - Revalidate if query uses a DDM table.
X'800E' - Revalidate if tables used in the access plan have been overridden to different tables or members.
X'800F' - Revalidate if table, member or data space objects have changed from when the access plan was created.
X'8010' - Revalidate if the format level identifiers of the tables do not match thosed saved in the access plan.
X'8011' - Revalidate if CCSIDs of the tables have changed.
X'8012' and higher - PTF changes that require the access plan to be rebuilt.

QVRCNT QVRCNT Unique refresh counter
QVRPTS QQTIM1 Timestamp of last access plan rebuild

Logical Column Name	Physical Column Name	Description
QQRID	QQRID	Row identification
QQTIME	QQTIME	Time row was created
QQJFLD	QQJFLD	Join column (unique per job)
QQRDBN	QQRDBN	Relational database name
QQSYS	QQSYS	System name
QQJOB	QQJOB	Job name
QQUSER	QQUSER	Job user
QQJNUM	QQJNUM	Job number
QQTHRD	QQI9	Thread identifier
QQUCNT	QQUCNT	Unique count (unique per query)
QQUDEF	QQUDEF	User defined column
QQQDTN	QQQDTN	Unique subselect number
QQQDTL	QQQDTL	Subselect nested level
QQMATN	QQMATN	Materialized view subselect number
QQMATL	QQMATL	Materialized view nested level
QQRCOD	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 could 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 - OS/400 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. 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 user-defined type or user-defined function is not the same object as the one referred to in the access plan. B0 - The options specified have changed as a result of the query options file.
QVSUBRC	QQC21	If the access plan rebuild reason code was A7 this reason code identifies which specific reason for A7 forced a rebuild. This value is a 2-byte hex value. The following values force a reoptimization. X'0001' - Update specified and must check at run time or user forced reoptimizing. X'0002' - If the query uses a select/omit index and also uses host variable values processed with V2 then we must reoptimize. X'0003' - If the user intends to insert, update, or delete through a join cursor, make sure that the first table specified is the first dial in the join cursor. This check was not done when the access plan was created. X'0004' - If the access plan indicates the query sort is to be used but the run time options do not allow it, then reoptimize. X'0005' - Check to see if a fast sort is being attempted but one isn't allowed. If so reoptimize so the fast sort isn't used. X'0006' - Need to reoptimize the query when the QDT requests the output to be forced to a temporary and the access plan does not already do this. X'0007' - Reoptimize if we have a change in commitiment control level. X'0008' - Reoptimize if we have a change in key feedback. X'0009' - Reoptimize if pool size changed. X'000A' - Reoptimize SMP PRODUCT status has changed. X'000B' - Reoptimize if SMP parallelism or I/O parallelism is being used but the DEGREE query attribute is now NONE. X'000C' - Reoptimize if SMP parallel is being used but the DEGREE query attribute is now IO. X'000D' - Reoptimize if access plan was created for a view or updated during the direct open of a view, and it is now being materialized, or visa versa. X'000E' - Reoptimize if SRVQRY is currently active and/or the access plan was created with SRVQRY active. X'000F' - Reoptimize if it is a unique query, but table scan is used for the first dial. X'0010' - Reoptimize if the query is set for hash join, but the last dial is not a hash join dial. X'0011' - Reoptimize if access plan was created using index AND/OR and now index AND/OR is not allowed. X'0012' - Reoptimize if an index used in the access plan is no longer usable. X'0013' - Reoptimize if one, or more, of the index AND/OR indexes used in the access plan is no longer usable. X'0014' - Reoptimize if the number rows is greater than 1000 and number of rows has changed by 10% (+ or -). X'0015' - Reoptimize if a new permanent index has been created since the access plan was created. X'0016' - Reoptimize if an index that was invalid when the access plan was created is now valid. X'0017' and higher (excluding values starting with X'80xx') PTF changes that require the access plan to be reoptimized.
		X'8001' - SQ requests a forced rebuild. X'8002' - Revalidate if need to perform CHAR to ZONED conversions. X'8003' - Revalidate if the query uses XLAT operators with CCSID conversion. X'8004' - Revalidate if query uses subtraction or addition. X'8005' - Revalidate if query uses maximum or minimum. X'8006' - Revalidate if query uses at least one table that specifies NULL values to be returned. X'8007' - Revalidate if query forced a revalidation. X'8008' - Revalidate if the job default format separators for DATE or TIME columns have changed since the access plan was created. X'8009' - Revalidate if default CCSID or final mapping CCSID is different now than when the access plan was created. X'800A' - Revalidate if the current NLSS is different than the one saved in the access plan. X'800B' - Revalidate if the SRTSEQ passed in is different that the SRTSEQ used when the access plan was created. X'800C' - Revalidate if divide by zero preference has changed. X'800D' - Revalidate if query uses a DDM table. X'800E' - Revalidate if tables used in the access plan have been overridden to different tables or members. X'800F' - Revalidate if table, member or data space objects have changed from when the access plan was created. X'8010' - Revalidate if the format level identifiers of the tables do not match thosed saved in the access plan. X'8011' - Revalidate if CCSIDs of the tables have changed. X'8012' and higher - PTF changes that require the access plan to be rebuilt.
QVRCNT	QVRCNT	Unique refresh counter
QVRPTS	QQTIM1	Timestamp of last access plan rebuild

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]