Query optimization performance information messages

1 - Perform specified ordering/grouping criteria.

2 - Perform specified join criteria.

3 - Perform specified record selection to minimize I/O wait time.

The access path was built using the following key fields. The key fields and their corresponding sequence (ASCEND or DESCEND) will be shown:

&17.

A key field of *MAP indicates the key field is an expression (derived field).

The access path was built using sequence table &13 in library &14.

A sequence table of *N indicates the access path was built without a sequence table. A sequence table of *I indicates the table was an internally derived table that is not available to the user.

If &18 &19 in library &5 is a logical file then the access path is built over member &9 of physical file &7 in library &8.

A file name starting with *QUERY or *N indicates the access path was built over a temporary file.

If *MAP is returned for one of the key fields or *I is returned for the sequence table, then a permanent access path cannot be created. A permanent access path cannot be built with these specifications.

1 - Perform specified ordering/grouping criteria.

2 - Perform specified join criteria.

3 - Perform specified record selection to minimize I/O wait time.

The access path was built using the following key fields. The key fields and their corresponding sequence (ASCEND or DESCEND) will be shown:

&17.

A key field of *MAP indicates the key field is an expression (derived field).

The temporary access path was built using sequence table &13 in library &14.

A sequence table of *N indicates the access path was built without a sequence table. A sequence table of *I indicates the table was an internally derived table that is not available to the user.

If file &4 in library &5 is a logical file then the temporary access path is built over member &9 of physical file &7 in library &8. Creating an access path from a keyed file generally results in improved performance.

If *MAP is returned for one of the key fields or *I is returned for the sequence table, then a permanent access path cannot be created. A permanent access path cannot be built with these specifications.

A temporary access path can only be created using index only access if all of the fields that were used by this temporary access path are also key fields for the access path from the keyed file.

0 - A new access plan was created.

1 - A file or member is not the same object as the one referred to in the access plan. Some reasons include the object being recreated, restored, or overridden to a new object.

2 - Access plan was using a reusable Open Data Path (ODP), and the optimizer chose to use a non-reusable ODP.

3 - Access plan was using a non-reusable Open Data Path (ODP) and the optimizer chose to use a reusable ODP.

4 - The number of records in member &3 of file &1 in library &2 has changed by more than 10%.

5 - A new access path exists over member &6 of file &4 in library &5.

6 - An access path over member &9 of file &7 in library &8 that was used for this access plan no longer exists or is no longer valid.

7 - The query access plan had to be rebuilt because of system programming changes.

8 - The CCSID (Coded Character Set Identifier) of the current job is different than the CCSID used in the access plan.

9 - The value of one of the following is different in the current job: date format, date separator, time format, or time separator.

10 - The sort sequence table specified has changed.

11 - The number of active processors or the size or paging option of the storage pool has changed.

12 - The system feature DB2® Symmetric Multiprocessing has either been installed or removed.

13 - The value of the degree query attribute has changed either by the CHGSYSVAL or CHGQRYA CL commands or with the query options file &15 in library &16.

14 - A view is either being opened by a high level language open, or is being materialized.

15 - 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.

16 - Query attributes have been specified from the query options file &15 in library &16.

17 - The access plan was generated with a commitment control level that is different in the current job.

18 - The access plan was generated with a different static cursor answer set size.

19 - This is the first run of the query since a prepare or compile.

20 and greater -- View the second level message text of the next message issued (CPI4351) for an explanation of these reason codes.

If the reason code is 4, 5, 6, 20, or 21 and the file specified in the reason code explanation is a logical file, then member &12 of physical file &10 in library &11 is the file with the specified change.

1 - The file is a join logical file and its join-type (JDFTVAL) does not match the join-type specified in the query.

2 - The format specified for the logical file references more than one physical file.

3 - The file is a complex SQL view, or nested table expression, or common table expression, or is a data change table reference that requires a temporary file.

4 - For an update-capable query, a subselect references a field in this file which matches one of the fields being updated.

5 - For an update-capable query, a subselect references SQL view &1, which is based on the file being updated.

6 - For a delete-capable query, a subselect references either the file from which records are to be deleted or an SQL view or logical file based on the file from which records are to be deleted.

7 - The file is user-defined table function &8 in &2, and all the records were retrieved from the function. The processing time is not returned for this reason code.

8 - The file is a partition file requiring a temporary file for processing the grouping or join.

1 - The query contains grouping fields (GROUP BY) from more than one file, or contains grouping fields from a secondary file of a join query that cannot be reordered.

2 - The query contains ordering fields (ORDER BY) from more than one file, or contains ordering fields from a secondary file of a join query that cannot be reordered.

3 - The grouping and ordering fields are not compatible.

4 - DISTINCT was specified for the query.

5 - Set operator (UNION, EXCEPT, or INTERSECT) was specified for the query.

6 - The query had to be implemented using a sort. More than 120 key fields specified for ordering.

7 - The query optimizer chose to use a sort rather than an access path to order the results of the query.

8 - Perform specified record selection to minimize I/O wait time.

9 - The query optimizer chose to use a hashing algorithm rather than an access path to perform the grouping for the query.

10 - The query contains a join condition that requires a temporary file.

11 - The query optimizer creates a run-time temporary file in order to implement certain correlated group by queries.

12 - The query contains grouping fields (GROUP BY, MIN/MAX, COUNT, etc.) and there is a read trigger on one or more of the underlying physical files in the query.

13 - The query involves a static cursor or the SQL FETCH FIRST clause.

1 - Perform specified record selection.

2 - Perform specified ordering/grouping criteria.

3 - Record selection and ordering/grouping criteria.

4 - Perform specified join criteria.

If file &13 in library &1 is a logical file then member &8 of physical file &6 in library &7 is the actual file in join position &10.

A file name starting with *TEMPX for the access path indicates it is a temporary access path built over file &6.

A file name starting with *N or *QUERY for the file indicates it is a temporary file.

Index only access was used for this file within the query: &11.

A value of *YES for index only access processing indicates that all of the fields used from this file for this query can be found within the access path of file &3. A value of *NO indicates that index only access could not be performed for this access path.

Index only access is generally a performance advantage since all of the data can be extracted from the access path and the data space does not have to be paged into active memory.

If ordering is desired, specifying ORDER BY fields over more than one file forces the creation of a temporary file and allows the optimizer to optimize the join order of all the files. No file is forced to be first.

An access path can only be considered for index only access if all of the fields used within the query for this file are also key fields for that access path.

Refer to the Data access methods for additional tips on optimizing a query's join order and index only access.

In some cases, creating a temporary result table provides the fastest way to run a query. Other queries that have many rows to be copied into the temporary result table can take a significant amount of time. However, if the query is taking more time and resources than can be allowed, consider changing the query so that a temporary result table is not required.

1 - Perform specified record selection.

2 - Perform specified ordering/grouping criteria.

3 - Record selection and ordering/grouping criteria.

4 - Perform specified join criteria.

If file &13 in library &1 is a logical file then member &8 of physical file &6 in library &7 is the actual file in join position &10.

A file name starting with *TEMPX for the access path indicates it is a temporary access path built over file &6.

A file name starting with *N or *QUERY for the file indicates it is a temporary file.

Index only access was used for this file within the query: &11.

A value of *YES for index only access processing indicates that all of the fields used from this file for this query can be found within the access path of file &3. A value of *NO indicates that index only access could not be performed for this access path.

Index only access is generally a performance advantage since all of the data can be extracted from the access path and the data space does not have to be paged into active memory.

If ordering is desired, specifying ORDER BY fields over more than one file forces the creation of a temporary file and allows the optimizer to optimize the join order of all the files. No file is forced to be first.

An access path can only be considered for index only access if all of the fields used within the query for this file are also key fields for that access path.

Refer to the Data access methods for additional tips on optimizing a query's join order and index only access.

In some cases, creating a temporary result table provides the fastest way to run a query. Other queries that have many rows to be copied into the temporary result table can take a significant amount of time. However, if the query is taking more time and resources than can be allowed, consider changing the query so that a temporary result table is not required.

If file &13 in library &1 is a logical file then member &8 of physical file &6 in library &7 is the actual file in join position &10.

A file name that starts with *QUERY for the file indicates it is a temporary file.

Refer to the Data access methods for additional tips on optimizing a query's join order.

1 - Record selection.

2 - Ordering/grouping criteria.

3 - Record selection and ordering/grouping criteria.

If file &13 in library &1 is a logical file then member &8 of physical file &6 in library &7 is the actual file being accessed.

Index only access was used for this query: &11.

A value of *YES for index only access processing indicates that all of the fields used for this query can be found within the access path of file &3. A value of *NO indicates that index only access could not be performed for this access path.

Index only access is generally a performance advantage since all of the data can be extracted from the access path and the data space does not have to be paged into active memory.

Refer to the Data access methods. for additional tips on index only access.

If file &13 in library &1 is a logical file then member &8 of physical file &6 in library &7 is the actual file from which records are being selected.

A file name starting with *N or *QUERY for the file indicates it is a temporary file.

If an access path does not exist, you may want to create one whose left-most key fields match fields in the record selection. Matching more key fields in the access path with fields in the record selection will result in improved performance.

Generally, to force the use of an existing access path, specify order by fields that match the left-most key fields of that access path.

For more information refer to Data access methods.

The list below shows the access paths considered before the optimizer timed out. If file &1 in library &2 is a logical file then the access paths specified are actually built over member &9 of physical file &7 in library &8. Following each access path name in the list is a reason code which explains how the optimizer considered the access path.

&11.

The reason codes and their meanings follow:

0 - The access path was used to implement the query.

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.

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

7 - Use of this access path would not minimize delays when reading records from the file as the user requested.

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 &1 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 and greater - View the second level message text of the next message issued (CPI432D) for an explanation of these reason codes.

The user may want to delete any access paths no longer needed.

The list below shows the access paths considered. If file &1 in library &2 is a logical file then the access paths specified are actually built over member &9 of physical file &7 in library &8. Following each access path name in the list is a reason code which explains how the optimizer considered the access path.

&11.

The reason codes and their meanings follow:

0 - The access path was used to implement the query.

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 would 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 &1 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 and greater - View the second level message text of the next message issued (CPI432D) for an explanation of these reason codes.

The reason codes and their meanings follow:

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

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 could not be determined. Either the select/omit criteria or the query selection became too complex during compatibility processing.

13 - The access path cannot be used because it 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 could not 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 could not 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.

99 - The access path was used to gather statistics information for the query optimizer.

The data type of the field may have been changed to match the comparison operand. For a numeric field, the number of total digits or fractional digits of the comparison operand may have exceeded that of the field.

1 - For a literal, change the literal value so that its attributes match the field's attributes. Normally, an attributes mismatch is caused by a numeric literal that has non-significant leading or trailing zeroes.

2 - For a host variable, either change the host variable's definition to match the field's definition or define a new host variable that matches the field's definition.

3 - For a field, change the attributes of one of the fields to match the other's attributes.

In the list of key fields that follow, the query optimizer is recommending the first &10 key fields as primary key fields. The remaining key fields are considered secondary key fields and are listed in order of expected selectivity based on this query. Primary key fields are fields that significantly reduce the number of keys selected based on the corresponding selection predicate. Secondary key fields are fields that may or may not significantly reduce the number of keys selected. It is up to the user to determine the true selectivity of secondary key fields and to determine whether those key fields should be used when creating the access path.

The query optimizer is able to perform key positioning over any combination of the primary key fields, plus one additional secondary key field. Therefore it is important that the first secondary key field be the most selective secondary key field. The query optimizer will use key selection with any remaining secondary key fields. While key selection is not as fast as key positioning it can still reduce the number of keys selected. Hence, secondary key fields that are fairly selective should be included. When building the access path all primary key fields should be specified first followed by the secondary key fields which are prioritized by selectivity. The following list contains the suggested primary and secondary key fields:

&11.

If file &1 in library &2 is a logical file then the access path should be built over member &9 of physical file &7 in library &8.

For more information, refer to Data access methods.

If file &1 in library &2 is a logical file, then member &9 of physical file &7 in library &8 is the actual file from which records are being selected.

A file name starting with *QUERY or *N for the file indicates a temporary result file is being used.

The query optimizer has calculated that the optimal number of tasks is &5 which was limited for reason code &4. The reason code definitions are:

1 - The *NBRTASKS parameter value was specified for the DEGREE parameter of the CHGQRYA CL command.

2 - The optimizer calculated the number of tasks which would use all of the central processing units (CPU).

3 - The optimizer calculated the number of tasks which can efficiently run in this job's share of the memory pool.

4 - The optimizer calculated the number of tasks which can efficiently run using the entire memory pool.

5 - The optimizer limited the number of tasks to equal the number of disk units which contain the file's data.

The database manager may further limit the number of tasks used if the allocation of the file's data is not evenly distributed across disk units.

A larger number of tasks might further improve performance. The following actions based on the optimizer reason code might allow the optimizer to calculate a larger number:

1 - Specify a larger number of tasks value for the DEGREE parameter of the CHGQRYA CL command. Start with a value for number of tasks which is a slightly larger than &5.

2 - Simplify the query by reducing the number of fields being mapped to the result buffer or by removing expressions. Also, try specifying a number of tasks as described by reason code 1.

3 - Specify *MAX for the query attribute DEGREE.

4 - Increase the size of the memory pool.

5 - Use the CHGPF CL command or the SQL ALTER statement to redistribute the file's data across more disk units.

If file &1 in library &2 is a logical file, then member &9 of physical file &7 in library &8 is the actual file over which the index is being built.

A file name starting with *QUERY or *N for the file indicates a temporary result file is being used.

The query optimizer has calculated that the optimal number of tasks is &5 which was limited for reason code &4. The definition of reason codes are:

1 - The *NBRTASKS parameter value was specified for the DEGREE parameter of the CHGQRYA CL command.

2 - The optimizer calculated the number of tasks which would use all of the central processing units (CPU).

3 - The optimizer calculated the number of tasks which can efficiently run in this job's share of the memory pool.

4 - The optimizer calculated the number of tasks which can efficiently run using the entire memory pool.

The database manager may further limit the number of tasks used for the parallel index build if either the allocation of the file's data is not evenly distributed across disk units or the system has too few disk units.

A larger number of tasks might further improve performance. The following actions based on the reason code might allow the optimizer to calculate a larger number:

1 - Specify a larger number of tasks value for the DEGREE parameter of the CHGQRYA CL command. Start with a value for number of tasks which is a slightly larger than &5 to see if a performance improvement is achieved.

2 - Simplify the query by reducing the number of fields being mapped to the result buffer or by removing expressions. Also, try specifying a number of tasks for the DEGREE parameter of the CHGQRYA CL command as described by reason code 1.

3 - Specify *MAX for the query attribute degree.

4 - Increase the size of the memory pool.

The host variables values displayed above may have been special values. An explanation of the special values follow:

- DBCS data is displayed in hex format.

- *N denotes a value of NULL.

- *Z denotes a zero length string.

- *L denotes a value too long to display in the replacement text.

- *U denotes a value that could not be displayed.

Each hash join step will be optimized and processed separately. Debug messages which explain the implementation of each hash join step follow this message in the joblog.

The list below shows the names of the files or the table functions used in this query. If the entry is for a file, the format of the entry in this list is the number of the hash join step, the filename as specified in the query, the member name as specified in the query, the filename actually used in the hash join step, and the member name actually used in the hash join step. If the entry is for a table function, the format of the entry in this list is the number of the hash join step and the function name as specified in the query.

If there are two or more files or functions listed for the same hash step, then that hash step is implemented with nested loop join.

Bitmap processing is a method of allowing one or more access path(s) to be used to access the selected records from a file. Using bitmap processing, record selection is applied against each access path, similar to key row positioning, to create a bitmap. The bitmap has marked in it only the records of the file that are to be selected. If more than one access path is used, the resulting bitmaps are merged together using boolean logic. The resulting bitmap is then used to reduce access to just those records actually selected from the file.

Bitmap processing is used in conjunction with the two primary access methods: arrival sequence (CPI4327 or CPI4329) or keyed access (CPI4326 or CPI4328). The message that describes the primary access method immediately precedes this message.

When the bitmap is used with the keyed access method then it is used to further reduce the number of records selected by the primary access path before retrieving the selected records from the file.

When the bitmap is used with arrival sequence then it allows the sequential scan of the file to skip records which are not selected by the bitmap. This is called skip sequential processing.

The list below shows the names of the access paths used in the bitmap processing:

&8

If file &2 in library &3 is a logical file then member &7 of physical file &5 in library &6 is the actual file being accessed.

1 - Library &1 was not found.

2 - File &2 in library &1 was not found.

3 - The file was damaged.

4 - The file was locked by another process which prevented successful retrieval of the query options.

5 - File &2 and the internal query options structures are out of sync.

6 - An unexpected error occurred while trying to retrieve the options file.

The query options file is used by the Query Optimizer to determine how a query will be implemented.

1 - Either create the library (CRTLIB command) or correct the library name and then try the request again.

2 - Either specify the library name that contains the query options file or create a duplicate object (CRTDUPOBJ command) of file &2 from library QSYS into the specified library.

4 - Wait for lock on file &2 in library &1 to be released and try the request again.

3, 5, or 6 - Delete query options file &2 in library &1 and then duplicate it from QSYS. If the problem still persists, report the problem (ANZPRB command).

1 - The library &1 was not found.

2 - The file &2 in library &1 was not found.

3 - The parameter &5 was not found.

4 - The value &6 for parameter &5 was not valid.

5 - An unexpected error occurred while trying to update the options file.

1 - Either create the library (CRTLIB) command or correct the library name and then try the request again.

2 - Either specify the library name that contains the query options file or create duplicate object (CRTDUPOBJ) command of QAQQINI from library QSYS into the specified library.

3 - Either specify a valid parameter or correct the parameter name and then try the request again.

4 - Either specify a valid parameter value or correct the parameter value and then try the request again. (WRKJOB) command.

If the function is defined to use an external program, the associated program or service program is &3 in library &2.

Each join class step will be optimized and processed separately. Debug messages detailing the implementation of each join class follow this message in the joblog.

The list below shows the file names of the files used in this query. The format of each entry in this list is the number of the join class step, the number of the join position in the join class step, the file name as specified in the query, the member name as specified in the query, the file name actually used in the join class step, and the member name actually used in the join class step.

The library, file and member names of each file involved in the join follow: &2.

A file name beginning with *QQTDF indicates it is a temporary distributed result file created by the query optimizer and it will not contain an associated library or member name.

1 - Data from member &2 of &7 &8 in library &1 was directed to other nodes.

2 - Data from member &2 of &7 &8 in library &1 was broadcast to all nodes.

3 - Either the query contains grouping fields (GROUP BY) that do not match the partitioning keys of the distributed file or the query contains grouping criteria but no grouping fields were specified or the query contains a subquery.

4 - Query contains join criteria over a distributed file and the query was processed in multiple steps.

A library and member name of *N indicates the data comes from a query temporary distributed file.

File &3 was built on nodes: &9.

It was built using partitioning keys: &10.

A partitioning key of *N indicates no partitioning keys were used when building the temporary distributed result file.

1 - Generally, a file is directed when the join fields do not match the partitioning keys of the distributed file. When a file is directed, the query is processed in multiple steps and processed in parallel. A temporary distributed result file is required to contain the intermediate results for each step.

2 - Generally, a file is broadcast when join fields do not match the partitioning keys of either file being joined or the join operator is not an equal operator. When a file is broadcast the query is processed in multiple steps and processed in parallel. A temporary distributed result file is required to contain the intermediate results for each step.

3 - Better performance may be achieved if grouping fields are specified that match the partitioning keys.

4 - Because the query is processed in multiple steps, a temporary distributed result file is required to contain the intermediate results for each step. See preceding message CPI4342 to determine which files were joined together.

For more information about processing of distributed files, refer to the Distributed database programming

Each step will be optimized and processed separately. Debug messages which explain the implementation of each step follow this message in the joblog.

The list below shows the file names of the files used in this query. The format of each entry in this list is the number of the join step, the filename as specified in the query, the member name as specified in the query, the filename actually used in the step, and the member name actually used in the step.

1 - Either the length of the new LIKE pattern is zero and the length of the old LIKE pattern is nonzero or the length of the new LIKE pattern is nonzero and the length of the old LIKE pattern is zero.

2 - An additional wildcard was specified in the LIKE pattern on this invocation of the cursor.

3 - SQL indicated to the query optimizer that the cursor cannot be refreshed.

4 - The system code could not obtain a lock on the file being queried.

5 - The length of the host variable value is too large for the the host variable as determined by the query optimizer.

6 - The size of the ODP to be refreshed is too large.

7 - Refresh of the local ODP of a distributed query failed.

8 - SQL hard closed the cursor prior to the fast path refresh code.

1 - The new host variable value is not null and old host variable value is null or the new host variable value is zero length and the old host variable value is not zero length.

2 - The attributes of the new host variable value are not the same as the attributes of the old host variable value.

3 - The length of the host variable value is either too long or too short. The length difference cannot be handled in the fast path refresh code.

4 - The host variable has a data type of IGC ONLY and the the length is not even or is less than 2 bytes.

5 - The host variable has a data type of IGC ONLY and the new host variable value does not contain an even number of bytes.

6 - A translate table with substitution characters was used.

7 - The host variable contains DBCS data and a CCSID translate table with substitution characters is required.

8 - The host variable contains DBCS that is not well formed. That is, a shift-in without a shift-out or visa versa.

9 - The host variable must be translated with a sort sequence table and the sort sequence table contains substitution characters.

10 - The host variable contains DBCS data and must be translated with a sort sequence table that contains substitution characters.

11 - The host variable is a Date, Time or Timestamp data type and the length of the host variable value is either too long or too short.

1 -- Sort sequence table &2 in library &3 is an ICU (International Components of Unicode) sort sequence table that is not supported by SQE.

Following each materialized query table name in the list is a reaon code which explains why the materialized query table was not used. A reason code of 0 indicates that the materialized query table was used to implement the query.

The reason codes and their meanings follow:

1 - The cost to use the materialized query table, as determined by the optimizer, was higher than the cost associated with the chosen implementation.

2 - The join specified in the materialized query was not compatible with the query.

3 - The materialized query table had predicates that were not matched in the query.

4 - The grouping or distinct specified in the materialized query table is not compatible with the grouping or distinct specified in the query.

5 - The query specified columns that were not in the select-list of the materialized query table.

6 - The materialized query table query contains functionality that is not supported by the query optimizer.

7 - The materialized query table specified the DISABLE QUERY OPTIMIZATION clause.

8 - The ordering specified in the materialized query table is not compatible with the ordering specified in the query.

9 - The query contains functionality that is not supported by the materialized query table matching algorithm.

10 - Materialized query tables may not be used for this query.

11 - The refresh age of this materialized query table exceeds the duration specified by the MATERIALIZED_QUERY_TABLE_REFRESH_AGE QAQQINI option.

12 - The commit level of the materialized query table is lower than the commit level specified for the query.

14 - The FETCH FOR FIRST n ROWS clause of the materialized query table is not compatible with the query.

15 - The QAQQINI options used to create the materialized query table are not compatible with the QAQQINI options used to run this query.

16 - The materialized query table is not usable.

17 - The UNION specified in the materialized query table is not compatible with the query.

18 - The constants specified in the materialized query table are not compatible with host variable values specified in the query.

19 - The materialized query table is in Check Pending status and cannot be used.

20 - The UDTF specified in the materialized query table is not compatible with UDTF in the query.

21 - The Values clause specified in the materialized query table is not compatible with Values specified in the query.

20 - Referential or check constraints for member &19 of file &17 in library &18 have changed since the access plan was generated.

21 - Materialized query tables for member &22 of file &20 in library &21 have changed since the access plan was generated. If the file is *N then the file name is not available.

22 - The value of a host variable changed and the access plan is no longer valid.

23 - Adaptive Query Processing (AQP) determined that a new access plan is needed.

If multiple monitors have been started using the same generic job name, the monitor ID is needed to uniquely identify which monitor is to be ended with the ENDDBMON command.