PRTSQLINF message reference

The following messages are returned from PRTSQLINF.

SQL400A - Temporary distributed result file &1 was created to contain join result
Message Text: Temporary distributed result file &1 was created to contain join result. Result file was directed.
Cause Text: Query contains join criteria over a distributed file and a distributed join was performed in parallel. A temporary distributed result file was created to contain the results of the distributed join.
Recovery Text: For more information about processing of distributed files, refer to the Distributed database programming topic collection.
SQL400B - Temporary distributed result file &1 was created to contain join result
Message Text: Temporary distributed result file &1 was created to contain join result. Result file was broadcast.
Cause Text: Query contains join criteria over a distributed file and a distributed join was performed in parallel. A temporary distributed result file was created to contain the results of the distributed join.
Recovery Text: For more information about processing of distributed files, refer to the Distributed database programming topic collection.
SQL400C - Optimizer debug messages for distributed query step &1 and &2 follow
Message Text: Optimizer debug messages for distributed query step &1 of &2 follow:
Cause Text: A distributed file was specified in the query which caused the query to be processed in multiple steps. The optimizer debug messages that follow provide the query optimization information about the current step.
Recovery Text: For more information about processing of distributed files, refer to the Distributed database programming topic collection.
SQL400D - GROUP BY processing generated
Message Text: GROUP BY processing generated.
Cause Text: GROUP BY processing was added to the query step. Adding the GROUP BY reduced the number of result rows which should, in turn, improve the performance of subsequent steps.
Recovery Text: For more information refer to the SQL programming topic collection.
SQL400E - Temporary distributed result file &1 was created while processing distributed subquery
Message Text: Temporary distributed result file &1 was created while processing distributed subquery.
Cause Text: A temporary distributed result file was created to contain the intermediate results of the query. The query contains a subquery which requires an intermediate result.
Recovery Text: Generally, if the fields correlated between the query and subquery do not match the partition keys of the respective files, the query must be processed in multiple steps and a temporary distributed file will be built to contain the intermediate results. For more information about processing of distributed files, refer to the Distributed database programming topic collection.
SQL4001 - Temporary result created
Message Text: Temporary result created.
Cause Text: Conditions exist in the query which cause a temporary result to be created. One of the following reasons may be the cause for the temporary result:

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

-- The format specified for the logical file refers to more than one physical table.

-- The table is a complex SQL view requiring a temporary table to contain the results of the SQL view.

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

Recovery Text: Performance may be improved if the query can be changed to avoid temporary results.
SQL4002 - Reusable ODP sort used
Message Text: Reusable ODP sort used.
Cause Text: Conditions exist in the query which cause a sort to be used. This allowed the open data path (ODP) to be reusable. One of the following reasons may be the cause for the sort:

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

-- The grouping and ordering columns are not compatible.

-- DISTINCT was specified for the query.

-- UNION was specified for the query.

-- The query had to be implemented using a sort. Key length of more than 2000 bytes, more than 120 ordering columns, or an ordering column containing a reference to an external user-defined function was specified for ordering.

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

Recovery Text: A reusable ODP generally results in improved performance when compared to a non-reusable ODP.
SQL4003 - UNION
Message Text: UNION, EXCEPT, or INTERSECT.
Cause Text: A UNION, EXCEPT, or INTERSECT operator was specified in the query. The messages preceding this keyword delimiter correspond to the subselect preceding the UNION, EXCEPT, or INTERSECT operator. The messages following this keyword delimiter correspond to the subselect following the UNION, EXCEPT, or INTERSECT operator.
Recovery Text: None
SQL4004 - SUBQUERY
Message Text: SUBQUERY.
Cause Text: The SQL statement contains a subquery. The messages preceding the SUBQUERY delimiter correspond to the subselect containing the subquery. The messages following the SUBQUERY delimiter correspond to the subquery.
Recovery Text: None
SQL4005 - Query optimizer timed out for table &1
Message Text: Query optimizer timed out for table &1.
Cause Text: The query optimizer timed out before it could consider all indexes built over the table. This is not an error condition. The query optimizer may time out in order to minimize optimization time. The query can be run in debug mode (STRDBG) to see the list of indexes which were considered during optimization. The table number refers to the relative position of this table in the query.
Recovery Text: To ensure an index is considered for optimization, specify the logical file of the index as the table to be queried. The optimizer will first consider the index of the logical file specified on the SQL select statement. Note that SQL created indexes cannot be queried. An SQL index can be deleted and recreated to increase the chances it will be considered during query optimization. Consider deleting any indexes no longer needed.
SQL4006 - All indexes considered for table &1
Message Text: All indexes considered for table &1.
Cause Text: The query optimizer considered all index built over the table when optimizing the query. The query can be run in debug mode (STRDBG) to see the list of indexes which were considered during optimization. The table number refers to the relative position of this table in the query.
Recovery Text: None
SQL4007 - Query implementation for join position &1 table &2
Message Text: Query implementation for join position &1 table &2.
Cause Text: The join position identifies the order in which the tables are joined. A join position of 1 indicates this table is the first, or left-most, table in the join order. The table number refers to the relative position of this table in the query.
Recovery Text: Join order can be influenced by adding an ORDER BY clause to the query. Refer to Join optimization for more information about join optimization and tips to influence join order.
SQL4008 - Index &1 used for table &2
Message Text: Index &1 used for table &2.
Cause Text: The index was used to access rows from the table for one of the following reasons:

-- Row selection.

-- Join criteria.

-- Ordering/grouping criteria.

-- Row selection and ordering/grouping criteria.

The table number refers to the relative position of this table in the query.

The query can be run in debug mode (STRDBG) to determine the specific reason the index was used.

Recovery Text: None
SQL4009 - Index created for table &1
Message Text: Index created for table &1.
Cause Text: A temporary index was built to access rows from the table for one of the following reasons:

-- Perform specified ordering/grouping criteria.

-- Perform specified join criteria.

The table number refers to the relative position of this table in the query.

Recovery Text: To improve performance, consider creating a permanent index if the query is run frequently. The query can be run in debug mode (STRDBG) to determine the specific reason the index was created and the key columns used when creating the index. NOTE: If permanent index is created, it is possible the query optimizer may still choose to create a temporary index to access the rows from the table.
SQL401A - Processing grouping criteria for query containing a distributed table
Message Text: Processing grouping criteria for query containing a distributed table.
Cause Text: Grouping for queries that contain distributed tables can be implemented using either a one or two step method. If the one step method is used, the grouping columns (GROUP BY) match the partitioning keys of the distributed table. If the two step method is used, the grouping columns do not match the partitioning keys of the distributed table or the query contains grouping criteria but no grouping columns were specified. If the two step method is used, message SQL401B will appear followed by another SQL401A message.
Recovery Text: For more information about processing of distributed tables, refer to the Distributed database programming topic collection.
SQL401B - Temporary distributed result table &1 was created while processing grouping criteria
Message Text: Temporary distributed result table &1 was created while processing grouping criteria.
Cause Text: A temporary distributed result table was created to contain the intermediate results of the query. Either the query contains grouping columns (GROUP BY) that do not match the partitioning keys of the distributed table or the query contains grouping criteria but no grouping columns were specified.
Recovery Text: For more information about processing of distributed tables, refer to the Distributed database programming topic collection.
SQL401C - Performing distributed join for query
Message Text: Performing distributed join for query.
Cause Text: Query contains join criteria over a distributed table and a distributed join was performed in parallel. See the following SQL401F messages to determine which tables were joined together.
Recovery Text: For more information about processing of distributed tables, refer to the Distributed database programming topic collection.
SQL401D - Temporary distributed result table &1 was created because table &2 was directed
Message Text: Temporary distributed result table &1 was created because table &2 was directed.
Cause Text: Temporary distributed result table was created to contain the intermediate results of the query. Data from a distributed table in the query was directed to other nodes.
Recovery Text: Generally, a table is directed when the join columns do not match the partitioning keys of the distributed table. When a table 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. For more information about processing of distributed tables, refer to the Distributed database programming topic collection.
SQL401E - Temporary distributed result table &1 was created because table &2 was broadcast
Message Text: Temporary distributed result table &1 was created because table &2 was broadcast.
Cause Text: Temporary distributed result table was created to contain the intermediate results of the query. Data from a distributed table in the query was broadcast to all nodes.
Recovery Text: Generally, a table is broadcast when join columns do not match the partitioning keys of either table being joined or the join operator is not an equal operator. When a table is broadcast the query is processed in multiple steps and processed in parallel. A temporary distributed result table is required to contain the intermediate results for each step. For more information about processing of distributed tables, refer to the Distributed database programming topic collection.
SQL401F - Table &1 used in distributed join
Message Text: Table &1 used in distributed join.
Cause Text: Query contains join criteria over a distributed table and a distributed join was performed in parallel.
Recovery Text: For more information about processing of distributed tables, refer to the Distributed database programming topic collection.
SQL4010 - Table scan access for table &1
Message Text: Table scan access for table &1.
Cause Text: Table scan access was used to select rows from the table. The table number refers to the relative position of this table in the query.
Recovery Text: Table scan is generally a good performing option when selecting a high percentage of rows from the table. The use of an index, however, may improve the performance of the query when selecting a low percentage of rows from the table.
SQL4011 - Index scan-key row positioning used on table &1
Message Text: Index scan-key row positioning used on table &1.
Cause Text: Index scan-key row positioning is defined as applying selection against the index to position directly to ranges of keys that match some or all of the selection criteria. Index scan-key row positioning only processes a subset of the keys in the index and is a good performing option when selecting a small percentage of rows from the table.

The table number refers to the relative position of this table in the query.

Recovery Text: Refer to Data access methods for more information about index scan-key row positioning.
SQL4012 - Index created from index &1 for table &2
Message Text: Index created from index &1 for table &2.
Cause Text: A temporary index was created using the specified index to access rows from the queried table for one of the following reasons:

-- Perform specified ordering/grouping criteria.

-- Perform specified join criteria.

The table number refers to the relative position of this table in the query.

Recovery Text: Creating an index from an index is generally a good performing option. Consider creating a permanent index for frequently run queries. The query can be run in debug mode (STRDBG) to determine the key columns used when creating the index. NOTE: If a permanent index is created, it is possible the query optimizer may still choose to create a temporary index to access the rows from the table.
SQL4013 - Access plan has not been built
Message Text: Access plan has not been built.
Cause Text: An access plan was not created for this query. Possible reasons may include:

-- Tables were not found when the program was created.

-- The query was complex and required a temporary result table.

-- Dynamic SQL was specified.

Recovery Text: If an access plan was not created, review the possible causes. Attempt to correct the problem if possible.
SQL4014 - &1 join column pair(s) are used for this join position
Message Text: &1 join column pair(s) are used for this join position.
Cause Text: The query optimizer may choose to process join predicates as either join selection or row selection. The join predicates used in join selection are determined by the final join order and the index used. This message indicates how many join column pairs were processed as join selection at this join position. Message SQL4015 provides detail on which columns comprise the join column pairs.

If 0 join column pairs were specified then index scan-key row positioning with row selection was used instead of join selection.

Recovery Text: If fewer join pairs are used at a join position than expected, it is possible no index exists which has keys matching the desired join columns. Try creating an index whose keys match the join predicates.

If 0 join column pairs were specified then index scan-key row positioning was used. Index scan-key row positioning is normally a good performing option. Message SQL4011 provides more information on index scan-key row positioning.

SQL4015 - From-column &1.&2, to-column &3.&4, join operator &5, join predicate &6
Message Text: From-column &1.&2, to-column &3.&4, join operator &5, join predicate &6.
Cause Text: Identifies which join predicate was implemented at the current join position. The replacement text parameters are:

-- &1: The join 'from table' number. The table number refers to the relative position of this table in the query.

-- &2: The join 'from column' name. The column within the join from table which comprises the left half of the join column pair. If the column name is *MAP, the column is an expression (derived field).

-- &3: The join 'to table' number. The table number refers to the relative position of this table in the query.

-- &4. The join 'to column' name. The column within the join to column which comprises the right half of the join column pair. If the column name is *MAP, the column is an expression (derived field).

-- &5. The join operator. Possible values are EQ (equal), NE (not equal), GT (greater than), LT (less than), GE (greater than or equal), LE (less than or equal), and CP (cross join or cartesian product).

-- &6. The join predicate number. Identifies the join predicate within this set of join pairs.

Recovery Text: Refer to Join optimization for more information about joins.
SQL4016 - Subselects processed as join query
Message Text: Subselects processed as join query.
Cause Text: The query optimizer chose to implement some or all of the subselects with a join query. Implementing subqueries with a join generally improves performance over implementing alternative methods.
Recovery Text: None
SQL4017 - Host variables implemented as reusable ODP
Message Text: Host variables implemented as reusable ODP.
Cause Text: The query optimizer has built the access plan allowing for the values of the host variables to be supplied when the query is opened. This query can be run with different values being provided for the host variables without requiring the access plan to be rebuilt. This is the normal method of handling host variables in access plans. The open data path (ODP) that will be created from this access plan will be a reusable ODP.
Recovery Text: Generally, reusable open data paths perform better than non-reusable open data paths.
SQL4018 - Host variables implemented as non-reusable ODP
Message Text: Host variables implemented as non-reusable ODP.
Cause Text: The query optimizer has implemented the host variables with a non-reusable open data path (ODP).
Recovery Text: This can be a good performing option in special circumstances, but generally a reusable ODP gives the best performance.
SQL4019 - Host variables implemented as file management row positioning reusable ODP
Message Text: Host variables implemented as file management row positioning reusable ODP.
Cause Text: The query optimizer has implemented the host variables with a reusable open data path (ODP) using file management row positioning.
Recovery Text: Generally, a reusable ODP performs better than a non-reusable ODP.
SQL402A - Hashing algorithm used to process join
Message Text: Hashing algorithm used to process join.
Cause Text: The hash join algorithm is typically used for longer running join queries. The original query will be subdivided into hash join steps. Each hash join step will be optimized and processed separately. Access plan implementation information for each of the hash join steps is not available because access plans are not saved for the individual hash join dials. Debug messages detailing the implementation of each hash dial can be found in the joblog if the query is run in debug mode using the STRDBG CL command.
Recovery Text: The hash join method is usually a good implementation choice, however, if you want to disallow the use of this method specify ALWCPYDTA(*YES). Refer to the &qryopt. for more information on hashing algorithm for join processing.
SQL402B - Table &1 used in hash join step &2
Message Text: Table &1 used in hash join step &2.
Cause Text: This message lists the table number used by the hash join steps. The table number refers to the relative position of this table in the query. If there are two or more of these messages for the same hash join step, then that step is a nested loop join. Access plan implementation information for each of the hash join step are not available because access plans are not saved for the individual hash steps. Debug messages detailing the implementation of each hash step can be found in the joblog if the query is run in debug mode using the STRDBG CL command.
Recovery Text: Refer to Data access methods for more information about hashing.
SQL402C - Temporary table created for hash join results
Message Text: Temporary table created for hash join results.
Cause Text: The results of the hash join were written to a temporary table so that query processing could be completed. The temporary table was required because the query contained one or more of the following: GROUP BY or summary functions ORDER BY DISTINCT Expression containing columns from more than one table Complex row selection involving columns from more than one table
Recovery Text: Refer to Data access methods for more information about the hashing algorithm for join processing.
SQL402D - Query attributes overridden from query options file &2 in library &1
Message Text: Query attributes overridden from query options file &2 in library &1.
Cause Text: None
Recovery Text: None
SQL4020 - Estimated query run time is &1 seconds
Message Text: Estimated query run time is &1 seconds.
Cause Text: The total estimated time, in seconds, of executing this query.
Recovery Text: None
SQL4021 - Access plan last saved on &1 at &2
Message Text: Access plan last saved on &1 at &2.
Cause Text: The date and time reflect the last time the access plan was successfully updated in the program object.
Recovery Text: None
SQL4022 - Access plan was saved with SRVQRY attributes active
Message Text: Access plan was saved with SRVQRY attributes active.
Cause Text: The access plan that was saved was created while SRVQRY was active. Attributes saved in the access plan may be the result of SRVQRY.
Recovery Text: The query will be re-optimized the next time it is run so that SRVQRY attributes will not be permanently saved.
SQL4023 - Parallel table prefetch used
Message Text: Parallel table prefetch used.
Cause Text: The query optimizer chose to use a parallel prefetch access method to reduce the processing time required for the table scan.
Recovery Text: Parallel prefetch can improve the performance of queries. Even though the access plan was created to use parallel prefetch, the system will actually run the query only if the following are true:

-- The query attribute degree was specified with an option of *IO or *ANY for the application process.

-- There is enough main storage available to cache the data being retrieved by multiple I/O streams. Normally, 5 megabytes would be a minimum. Increasing the size of the shared pool may improve performance.

For more information about parallel table prefetch, refer to Data access methods.

SQL4024 - Parallel index preload access method used

 
Message Text: Parallel index preload access method used.
Cause Text: The query optimizer chose to use a parallel index preload access method to reduce the processing time required for this query. This means that the indexes used by this query will be loaded into active memory when the query is opened.
Recovery Text: Parallel index preload can improve the performance of queries. Even though the access plan was created to use parallel preload, the system will actually use parallel preload only if the following are true:

-- The query attribute degree was specified with an option of *IO or *ANY for the application process.

-- There is enough main storage to load all of the index objects used by this query into active memory. Normally, a minimum of 5 megabytes would be a minimum. Increasing the size of the shared pool may improve performance.

For more information about parallel table prefetch, refer to Data access methods.
SQL4025 - Parallel table preload access method used
Message Text: Parallel table preload access method used.
Cause Text: The query optimizer chose to use a parallel table preload access method to reduce the processing time required for this query. This means that the data accessed by this query will be loaded into active memory when the query is opened.
Recovery Text: Parallel table preload can improve the performance of queries. Even though the access plan was created to use parallel preload, the system will actually use parallel preload only if the following are true:

-- The query attribute degree must have been specified with an option of *IO or *ANY for the application process.

-- There is enough main storage available to load all of the data in the file into active memory. Normally, 5 megabytes would be a minimum. Increasing the size of the shared pool may improve performance.

For more information about parallel table prefetch, refer to Data access methods.
SQL4026 - Index only access used on table number &1
Message Text: Index only access used on table number &1.
Cause Text: Index only access is primarily used in conjunction with either index scan-key row positioning index scan-key selection. This access method will extract all of the data from the index rather than performing random I/O to the data space. The table number refers to the relative position of this table in the query.
Recovery Text: Refer to Data access methods for more information about index only access.
SQL4027 - Access plan was saved with DB2® Symmetric Multiprocessing installed on the system
Message Text: Access plan was saved with DB2 Symmetric Multiprocessing installed on the system.
Cause Text: The access plan saved was created while the system feature DB2 Symmetric Multiprocessing was installed on the system. The access plan may have been influenced by the presence of this system feature. Having this system feature installed may cause the implementation of the query to change.
Recovery Text: For more information about how the system feature DB2 Symmetric Multiprocessing can influence a query, refer to the Controlling parallel processing for queries
SQL4028 - The query contains a distributed table
Message Text: The query contains a distributed table.
Cause Text: A distributed table was specified in the query which may cause the query to be processed in multiple steps. If the query is processed in multiple steps, additional messages will detail the implementation for each step. Access plan implementation information for each step is not available because access plans are not saved for the individual steps. Debug messages detailing the implementation of each step can be found in the joblog if the query is run in debug mode using the STRDBG CL command.
Recovery Text: For more information about how a distributed table can influence the query implementation refer to the Distributed database programming topic collection.
SQL4029 - Hashing algorithm used to process the grouping
Message Text: Hashing algorithm used to process the grouping.
Cause Text: The grouping specified within the query was implemented with a hashing algorithm.
Recovery Text: Implementing the grouping with the hashing algorithm is generally a performance advantage since an index does not have to be created. However, if you want to disallow the use of this method simply specify ALWCPYDTA(*YES). Refer to Data access methods for more information about the hashing algorithm.
SQL4030 - &1 tasks specified for parallel scan on table &2
Message Text: &1 tasks specified for parallel scan on table &2.
Cause Text: The query optimizer has calculated the optimal number of tasks for this query based on the query attribute degree. The table number refers to the relative position of this table in the query.
Recovery Text: Parallel table or index scan can improve the performance of queries. Even though the access plan was created to use the specified number of tasks for the parallel scan, the system may alter that number based on the availability of the pool in which this job is running or the allocation of the table's data across the disk units. Refer to Data access methods for more information about parallel scan.
SQL4031 - &1 tasks specified for parallel index create over table &2
Message Text: &1 tasks specified for parallel index create over table &2.
Cause Text: The query optimizer has calculated the optimal number of tasks for this query based on the query attribute degree. The table number refers to the relative position of this table in the query.
Recovery Text: Parallel index create can improve the performance of queries. Even though the access plan was created to use the specified number of tasks for the parallel index build, the system may alter that number based on the availability of the pool in which this job is running or the allocation of the table's data across the disk units. Refer to Data access methods for more information about parallel index create.
SQL4032 - Index &1 used for bitmap processing of table &2
Message Text: Index &1 used for bitmap processing of table &2.
Cause Text: The index was used, in conjunction with query selection, to create a bitmap. The bitmap, in turn, was used to access rows from the table. This message may appear more than once per table. If this occurs, then a bitmap was created from each index of each message. The bitmaps were then combined into one bitmap using boolean logic and the resulting bitmap was used to access rows from the table. The table number refers to the relative position of this table in the query.
Recovery Text: The query can be run in debug mode (STRDBG) to determine more specific information. Also, refer to Data access methods for more information about bitmap processing.
SQL4033 - &1 tasks specified for parallel bitmap create using &2
Message Text: &1 tasks specified for parallel bitmap create using &2.
Cause Text: The query optimizer has calculated the optimal number of tasks to use to create the bitmap based on the query attribute degree.
Recovery Text: Using parallel index scan to create the bitmap can improve the performance of queries. Even though the access plan was created to use the specified number of tasks, the system may alter that number based on the availability of the pool in which this job is running or the allocation of the file's data across the disk units. Refer to Data access methods for more information about parallel scan.
SQL4034 - Multiple join classes used to process join
Message Text: Multiple join classes used to process join.
Cause Text: Multiple join classes are used when join queries are written that have conflicting operations or cannot be implemented as a single query. Each join class will be optimized and processed as a separate step of the query with the results written out to a temporary table. Access plan implementation information for each of the join classes is not available because access plans are not saved for the individual join class dials. Debug messages detailing the implementation of each join dial can be found in the joblog if the query is run in debug mode using the STRDBG CL command.
Recovery Text: Refer to Join optimization for more information about join classes.
SQL4035 - Table &1 used in join class &2
Message Text: Table &1 used in join class &2.
Cause Text: This message lists the table numbers used by each of the join classes. The table number refers to the relative position of this table in the query. All of the tables listed for the same join class will be processed during the same step of the query. The results from all of the join classes will then be joined together to return the final results for the query. Access plan implementation information for each of the join classes are not available because access plans are not saved for the individual classes. Debug messages detailing the implementation of each join class can be found in the joblog if the query is run in debug mode using the STRDBG CL command.
Recovery Text: Refer to Join optimization for more information about join classes.