Several of the following SQL run-time 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 already been run, the database manager in many cases will save the associated ODPs of the SQL operations to reuse them the next time the statement is run. So an SQL CLOSE statement may close the SQL cursor but leave the ODP available to be used again the next time the cursor is opened. This 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.
The following informational messages are issued at SQL run time:
This message is sent when the job's call stack no longer contains a program that has run an SQL statement.
Unless CLOSQLCSR(*ENDJOB) or CLOSQLCSR(*ENDACTGRP) was specified, the SQL environment for reusing ODPs across program calls exists only until the active programs that ran the SQL statements complete.
Except for ODPs associated with *ENDJOB or *ENDACTGRP cursors, all ODPs are deleted when all the SQL programs on the call stack complete and the SQL environment is exited.
This completion process includes closing of cursors, the deletion of ODPs, the removal of prepared statements, and the release of locks.
Putting an SQL statement that can be run in the first program of an application keeps the SQL environment active for the duration of that application. This allows ODPs in other SQL programs to be reused when the programs are repeatedly called. CLOSQLCSR(*ENDJOB) or CLOSQLCSR(*ENDACTGRP) can also be specified.
This message indicates that the last time the statement was run or when a CLOSE statement was run for this cursor, the ODP was not deleted. It will now be used again. This should be an indication of very efficient use of resources by eliminating unnecessary OPEN and CLOSE operations.
No ODP was found that could be used again. The first time that the statement is run or the cursor is opened for a process, an ODP will always have to be created. However, if this message appears on every run of the statement or open of the cursor, the tips recommended in "Database application design tips: Retaining cursor positions for non-ILE program calls" should be applied to this application.
For a program that is run only once per job, this message could be normal. However, if this message appears on every run of the statement or open of the cursor, then the tips recommended in "Database application design tips: Retaining cursor positions for non-ILE program calls" should be applied to this application.
If the statement is rerun or the cursor is opened again, the ODP should be available again for use.
The DB2 UDB for AS/400 precompilers allow the creation of the program objects even when required tables are missing. In this case the binding of the access plan is done when the program is first run. This message indicates that an access plan was created and successfully stored in the program object.
SQL will request multiple rows from the database manager when running this statement instead of requesting one row at a time.
The database manager rebuilt the access plan for this statement, but the program could not be updated with the new access plan. Another job is currently running the program that has a shared lock on the access plan of the program.
The program cannot be updated with the new access plan until the job can obtain an exclusive lock on the access plan of the program. The exclusive lock cannot be obtained until the shared lock is released.
The statement will still run and the new access plan will be used; however, the access plan will continue to be rebuilt when the statement is run until the program is updated.
A reusable ODP exists for this statement, but either the job's library list or override specifications have changed the query.
The statement now refers to different tables or uses different override specifications than are in the existing ODP. The existing ODP cannot be reused, and a new ODP must be created. To make it possible to reuse the ODP, avoid changing the library list or the override specifications.
When mapping data to host variables, data conversions were required. When these statements are run in the future, they will be slower than if no data conversions were required. The statement ran successfully, but performance could be improved by eliminating the data conversion. For example, a data conversion that would cause this message to occur would be the mapping of a character string of a certain length to a host variable character string with a different length. You could also cause this error by mapping a numeric value to a host variable that is a different type (decimal to integer). To prevent most conversions, use host variables that are of identical type and length as the columns that are being fetched.
The attributes of the INSERT or UPDATE values are different than the attributes of the columns receiving the values. Since the values must be converted, they cannot be directly moved into the columns. Performance could be improved if the attributes of the INSERT or UPDATE values matched the attributes of the columns receiving the values.