You can evaluate the structure and performance of the given SQL statements in a program using informational messages put in the job log by the database manager. The messages are issued for an SQL program or interactive SQL when running in the debug mode. The database manager may send any of the following messages when appropriate. The ampersand variables (&1, &X) are replacement variables that contain either an object name or some other substitution value when the message appears in the job log. The messages are:
These messages provide feedback on how a query was run and, in some cases, indicate the improvements that can be made to help the query run faster.
The messages contain message help that provides information about the cause for the message, object name references, and possible user responses.
The time at which the message is sent does not necessarily indicate when the associated function was performed. Some messages are sent altogether at the start of a query run.
The causes and user responses for the following messages are paraphrased. The actual message help is more complete and should be used when trying to determine the meaning and responses for each message.
The possible user action for each message are described in the following sections:
This message indicates that a temporary index was created to process the query. The new index is created by reading all of the rows in the specified table.
The time required to create an index on each run of a query can be significant. Consider creating a logical file (CRTLF) or an SQL index (CREATE INDEX SQL statement):
Consider creating the logical file with select or omit criteria that either match or partially match the query's predicates involving constants. The database manager will consider using select or omit logical files even though they are not explicitly specified on the query.
For certain queries, the optimizer may decide to create an index even when an existing one can be used. This might occur when a query has an ordering column as a key column for an index, and the only row selection specified uses a different column. If the row selection results in roughly 20% of the rows or more to be returned, then the optimizer may create a new index to get faster performance when accessing the data. The new index minimizes the amount of data that needs to be read.
This message indicates that a temporary index was created from the index of a keyed table.
Generally, this action should not take a significant amount of time or resource because only a subset of the data in the table needs to be read. Sometimes even faster performance can be achieved by creating a logical file or SQL index that satisfies the index requirement stated in the message help.
For more detail, see the previous message, CPI4321.
This message can be sent for a variety of reasons. The specific reason is provided in the message help.
Most of the time, this message is sent when the queried table environment has changed, making the current access plan obsolete. An example of the table environment changing is when an index required by the query no longer exists on the system.
An access plan contains the instructions for how a query is to be run and lists the indexes for running the query. If a needed index is no longer available, the query is again optimized, and a new access plan is created, replacing the old one.
The process of again optimizing the query and building a new access plan at runtime is a function of DB2 UDB for AS/400. It allows a query to be run as efficiently as possible, using the most current state of the database without user intervention.
The infrequent appearance of this message is not a cause for action. For example, this message will be sent when an SQL package is run the first time after a restore, or anytime the optimizer detects that a change has occurred (such as a new index was created), that warrants an implicit rebuild. However, excessive rebuilds should be avoided because extra query processing will occur. Excessive rebuilds may indicate a possible application design problem or inefficient database management practices.
Before the query processing could begin, the data in the specified table had to be copied into a temporary table to simplify running the query. The message help contains the reason why this message was sent.
If the specified table selects few rows, usually less than 1000 rows, then the row selection part of the query's implementation should not take a significant amount of resource and time. However if the query is taking more time and resources than can be allowed, consider changing the query so that a temporary table is not required.
One way to do this is by breaking the query into multiple steps. Consider using an INSERT statement with a subselect to select only the rows that are required into a table, and then use that table's rows for the rest of the query.
A temporary result table was created to contain the intermediate results of the query. The message help contains the reason why a temporary result table is required.
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.
This message provides the join position of the specified table when an index is used to access the table's data. Join position pertains to the order in which the tables are joined.
The order in which tables are joined can significantly influence the efficiency of a query. The system processes the join of two tables with different numbers of selected rows more efficiently when the table with the smaller number of selected rows is joined to the table with the larger number of selected rows. For example, if two tables are being joined, the table with the fewest selected rows should be in join position 1 and the table with the larger number of selected rows should be in join position 2.
If the GROUP BY or ORDER BY clause is specified where all the columns in the clause are referenced from one of the tables in the query, that table becomes the first table in the final join order. If the referenced table is a large table, the query may be slow. To improve performance, consider one of the following:
When a query is changed as suggested above, a temporary result table may be used to change the join order. The improved efficiency of the join order will, in most cases, make up for any loss of efficiency caused by the temporary result.
If the query uses the JOIN clause or refers to a join logical file within the table specifications, the order in which the tables are specified will help determine the join order the optimizer uses. The optimizer cannot change the table join order if the query contains a join logical file, or if either a left outer or exception join is specified using the JOIN clause.
This message provides the name of the first or primary table of the join when arrival sequence is used to select rows from the table.
See the previous message, CPI4326, for information on join position and join performance tips.
This message names an existing index that was used by the query.
The reason the index was used is given in the message help.
No index was used to access the data in the specified table. The rows were scanned sequentially in arrival sequence.
The use of an index may improve the performance of the query if row selection is specified.
If an index does not exist, you may want to create one whose key column matches one of the columns in the row selection. You should only create an index if the row selection (WHERE clause) selects 20% or fewer rows in the table.
To force the use of an existing index, change the ORDER BY clause of the query to specify the first key column of the index.
The optimizer stops considering indexes when the time spent optimizing the query exceeds an internal value that is associated with the estimated time to run the query and the number of rows in the queried tables. Generally, the more rows in the tables, the greater the number of indexes that will be considered.
When the estimated time to run the query is exceeded, the optimizer uses the current best method for running the query. Either an index has been found to get the best performance, or an index will have to be created, if necessary. Exceeding the estimated time to run the query could mean that the optimizer did not consider the best index to run the query.
The message help contains a list of indexes that were considered before the optimizer exceeded the estimated time.
To ensure that an index is considered for optimization, specify the logical file associated with the index as the table to be queried. The optimizer will consider the index of the table specified on the query or SQL statement first. Remember that SQL indexes cannot be queried.
You may want to delete any indexes that are no longer needed.
Two or more SQL subselects were combined by the query optimizer and processed as a join query. Generally, this method of processing is a good performing option.
The optimizer considered all indexes built over the specified table. Since the optimizer examined all indexes for the table, it determined the current best access to the table.
The message help contains a list of the indexes. With each index a reason code is added. The reason code explains why the index was not used.
Message CPI432A or CPI432C was issued immediately before this message. Because of message length restrictions, some of the reason codes used by messages CPI432A and CPI432C are explained in the message help of CPI432D. Use the message help from this message to interpret the information returned from message CPI432A or CPI432C.
This message indicates that the query optimizer was not able to consider the usage of an index to resolve one or more of the selection specifications of the query. If there was an index available which otherwise could have been used to limit the processing of the query to just a few rows, then the performance of this query will be affected.
The attributes of a comparison value and a comparison column must match otherwise a conversion will occur so that they do match. Generally, this conversion occurs such that the value with the smallest attributes is mapped to the attributes of the other value. When the attributes of the comparison column have to be mapped to be compatible with that of the comparison value, the optimizer can no longer use an index to implement this selection.
The optimizer chooses to use one or more indexes, in conjunction with the query selection (WHERE clause), to build a bitmap. This resulting bitmap indicates which rows will actually be selected.
Conceptually, the bitmap contains one bit per row in the underlying table. Corresponding bits for selected rows are set to '1'. All other bits are set to '0'.
Once the bitmap is built, it is used, as appropriate, to avoid mapping in rows from the table not selected by the query. The use of the bitmap depends on whether the bitmap is used in combination with the arrival sequence or with a primary index.
When bitmap processing is used with arrival sequence, either message CPI4327 or CPI4329 will precede this message. In this case, the bitmap will help to selectively map only those rows from the table that the query selected.
When bitmap processing is used with a primary index, either message CPI4326 or CPI4328 will precede this message. Rows selected by the primary index will be checked against the bitmap before mapping the row from the table.