Query optimization performance information messages and open data paths

Several of the following SQL runtime messages refer to open data paths.

An open data path (ODP) definition is an internal object that is created when a cursor is opened or when other SQL statements are run. It provides a direct link to the data so that I/O operations can occur. ODPs are used on OPEN, INSERT, UPDATE, DELETE, and SELECT INTO statements to perform their respective operations on the data.

Even though SQL cursors are closed and SQL statements have run, in many cases, the database manager saves the associated ODPs of the SQL operations. These ODPs are then reused the next time the statement is run. For example, an SQL CLOSE statement could close the SQL cursor, but leave the ODP available to use again the next time the cursor is opened. This technique can significantly reduce the processing and response time in running SQL statements.

The ability to reuse ODPs when SQL statements are run repeatedly is an important consideration in achieving faster performance.

SQL7910 - All SQL cursors closed
Message Text: SQL cursors closed.
Cause Text: SQL cursors have been closed and all Open Data Paths (ODPs) have been deleted, except those that were opened by programs with the CLOSQLCSR(*ENDJOB) option or were opened by modules with the CLOSQLCSR(*ENDACTGRP) option. All SQL programs on the call stack have completed, and the SQL environment has been exited. This process includes the closing of cursors, the deletion of ODPs, the removal of prepared statements, and the release of locks.
Recovery Text: To keep cursors, ODPs, prepared statements, and locks available after the completion of a program, use the CLOSQLCSR precompile parameter.

-- The *ENDJOB option will allow the user to keep the SQL resources active for the duration of the job.

-- The *ENDSQL option will allow the user to keep SQL resources active across program calls, provided the SQL environment stays resident. Running an SQL statement in the first program of an application will keep the SQL environment active for the duration of that application.

-- The *ENDPGM option, which is the default for non-Integrated Language Environment® (ILE) programs, causes all SQL resources to only be accessible by the same invocation of a program. Once an *ENDPGM program has completed, if it is called again, the SQL resources are no longer active.

-- The *ENDMOD option causes all SQL resources to only be accessible by the same invocation of the module.

-- The *ENDACTGRP option, which is the default for ILE modules, will allow the user to keep the SQL resources active for the duration of the activation group.

SQL7911 - ODP reused
Message Text: ODP reused.
Cause Text: An ODP that was previously created has been reused. There was a reusable Open Data Path (ODP) found for this SQL statement, and it has been used. The reusable ODP may have been from the same call to a program or a previous call to the program. A reuse of an ODP will not generate an OPEN entry in the journal.
Recovery Text: None
SQL7912 - ODP created
Message Text: ODP created.
Cause Text: An Open Data Path (ODP) has been created. No reusable ODP could be found. This occurs in the following cases:

-- This is the first time the statement has been run.

-- A RCLRSC has been issued since the last run of this statement.

-- The last run of the statement caused the ODP to be deleted.

-- If this is an OPEN statement, the last CLOSE of this cursor caused the ODP to be deleted.

-- The Application Server (AS) has been changed by a CONNECT statement.

Recovery Text: If a cursor is being opened many times in an application, it is more efficient to use a reusable ODP, and not create an ODP every time. This also applies to repeated runs of INSERT, UPDATE, DELETE, and SELECT INTO statements. If ODPs are being created on every open, see the close message to determine why the ODP is being deleted.

The first time that the statement is run or the cursor is opened for a process, an ODP must always be created. However, if this message appears on every statement run or cursor open, use the tips recommended in Retaining cursor positions for non-ILE program calls in your application.

SQL7913 - ODP deleted
Message Text: ODP deleted.
Cause Text: The Open Data Path (ODP) for this statement or cursor has been deleted. The ODP was not reusable. This could be caused by using a host variable in a LIKE clause, ordering on a host variable, or because the query optimizer chose to accomplish the query with an ODP that was not reusable.
Recovery Text: See previous query optimizer messages to determine how the cursor was opened.
SQL7914 - ODP not deleted
Message Text: ODP not deleted.
Cause Text: The Open Data Path (ODP) for this statement or cursor has not been deleted. This ODP can be reused on a subsequent run of the statement. This will not generate an entry in the journal.
Recovery Text: None
SQL7915 - Access plan for SQL statement has been built
Message Text: Access plan for SQL statement has been built.
Cause Text: SQL had to build the access plan for this statement at run time. This occurs in the following cases:

-- The program has been restored from a different release and this is the first time this statement has been run.

-- All the files required for the statement did not exist at precompile time, and this is the first time this statement has been run.

-- The program was precompiled using SQL naming mode, and the program owner has changed since the last time the program was called.

Recovery Text: This is normal processing for SQL. Once the access plan is built, it will be used on subsequent runs of the statement.
SQL7916 - Blocking used for query
Message Text: Blocking used for query.
Cause Text: Blocking has been used in the implementation of this query. SQL will retrieve a block of records from the database manager on the first FETCH statement. Additional FETCH statements have to be issued by the calling program, but they do not require SQL to request more records, and therefore will run faster.
Recovery Text: SQL attempts to utilize blocking whenever possible. In cases where the cursor is not update capable, and commitment control is not active, there is a possibility that blocking will be used.
SQL7917 - Access plan not updated
Message Text: Access plan not updated.
Cause Text: The query optimizer rebuilt the access plan for this statement, but the program could not be updated. Another job may be running the program. The program cannot be updated with the new access plan until a job can obtain an exclusive lock on the program. The exclusive lock cannot be obtained if another job is running the program, if the job does not have proper authority to the program, or if the program is currently being saved. The query will still run, but access plan rebuilds will continue to occur until the program is updated.
Recovery Text: See previous messages from the query optimizer to determine why the access plan has been rebuilt. To ensure that the program gets updated with the new access plan, run the program when no other active jobs are using it.
SQL7918 - Reusable ODP deleted
Message Text: Reusable ODP deleted. Reason code &1.
Cause Text: An existing Open Data Path (ODP) was found for this statement, but it could not be reused for reason &1. The statement now refers to different files or uses different override options than are in the ODP. Reason codes and their meanings are:

1 -- Commitment control isolation level is not compatible.

2 -- The statement contains SQL special register USER, CURRENT DEBUG MODE, CURRENT DECFLOAT ROUNDING MODE, or CURRENT TIMEZONE, and the value for one of these registers has changed.

3 -- The PATH used to locate an SQL function has changed.

4 -- The job default CCSID has changed.

5 -- The library list has changed, such that a file is found in a different library. This only affects statements with unqualified table names, when the table exists in multiple libraries.

6 -- The file, library, or member for the original ODP was changed with an override.

7 -- An OVRDBF or DLTOVR command has been issued. A file referred to in the statement now refers to a different file, library, or member.

8 -- An OVRDBF or DLTOVR command has been issued, causing different override options, such as different SEQONLY or WAITRCD values.

9 -- An error occurred when attempting to verify the statement override information is compatible with the reusable ODP information.

10 -- The query optimizer has determined the ODP cannot be reused.

11 -- The client application requested not to reuse ODPs.

Recovery Text: Do not change the library list, the override environment, or the values of the special registers if reusable ODPs are to be used.
SQL7919 - Data conversion required on FETCH or embedded SELECT
Message Text: Data conversion required on FETCH or embedded SELECT.
Cause Text: Host variable &2 requires conversion. The data retrieved for the FETCH or embedded SELECT statement can not be directly moved to the host variables. The statement ran correctly. Performance, however, would be improved if no data conversion was required. The host variable requires conversion for reason &1.

-- Reason 1 - host variable &2 is a character or graphic string of a different length than the value being retrieved.

-- Reason 2 - host variable &2 is a numeric type that is different than the type of the value being retrieved.

-- Reason 3 - host variable &2 is a C character or C graphic string that is NUL-terminated, the program was compiled with option *CNULRQD specified, and the statement is a multiple-row FETCH.

-- Reason 4 - host variable &2 is a variable length string and the value being retrieved is not.

-- Reason 5 - host variable &2 is not a variable length string and the value being retrieved is.

-- Reason 6 - host variable &2 is a variable length string whose maximum length is different than the maximum length of the variable length value being retrieved.

-- Reason 7 - a data conversion was required on the mapping of the value being retrieved to host variable &2, such as a CCSID conversion.

-- Reason 8 - a DRDA connection was used to get the value being retrieved into host variable &2. The value being retrieved is either null capable or varying-length, is contained in a partial row, or is a derived expression.

-- Reason 10 - the length of host variable &2 is too short to hold a TIME or TIMESTAMP value being retrieved.

-- Reason 11 - host variable &2 is of type DATE, TIME or TIMESTAMP, and the value being retrieved is a character string.

-- Reason 12 - too many host variables were specified and records are blocked. Host variable &2 does not have a corresponding column returned from the query.

-- Reason 13 - a DRDA connection was used for a blocked FETCH and the number of host variables specified in the INTO clause is less than the number of result values in the select list.

-- Reason 14 - a LOB Locator was used and the commitment control level of the process was not *ALL.

Recovery Text: To get better performance, attempt to use host variables of the same type and length as their corresponding result columns.
SQL7939 - Data conversion required on INSERT or UPDATE
Message Text: Data conversion required on INSERT or UPDATE.
Cause Text: The INSERT or UPDATE values can not be directly moved to the columns because the data type or length of a value is different than one of the columns. The INSERT or UPDATE statement ran correctly. Performance, however, would be improved if no data conversion was required. The reason data conversion is required is &1.

-- Reason 1 is that the INSERT or UPDATE value is a character or graphic string of a different length than column &2.

-- Reason 2 is that the INSERT or UPDATE value is a numeric type that is different than the type of column &2.

-- Reason 3 is that the INSERT or UPDATE value is a variable length string and column &2 is not.

-- Reason 4 is that the INSERT or UPDATE value is not a variable length string and column &2 is.

-- Reason 5 is that the INSERT or UPDATE value is a variable length string whose maximum length is different that the maximum length of column &2.

-- Reason 6 is that a data conversion was required on the mapping of the INSERT or UPDATE value to column &2, such as a CCSID conversion.

-- Reason 7 is that the INSERT or UPDATE value is a character string and column &2 is of type DATE, TIME, or TIMESTAMP.

-- Reason 8 is that the target table of the INSERT is not a SQL table.

Recovery Text: To get better performance, try to use values of the same type and length as their corresponding columns.