This error can occur with explicitly prepared statements. These statements have the following form:
PREPARE statement id FROM "quoted_string"
After a statement has been prepared in the database server and before execution of the statement, a table to which the statement refers might have been renamed or altered, possibly changing the structure of the table. Problems might occur as a result. Adding an index to the table or altering the fragmentation scheme after preparing the statement can also invalidate the statement.
This error can also occur with SPL routines. Before the database server executes a new SPL routine the first time, it optimizes the code (statements) in the SPL routine. Optimization makes the code depend on the structure of the tables that the procedure references. If the table structure changes after the procedure is optimized, but before it is executed, this error can occur.
The Auto Re-prepare feature allows the database server to check prepared statements and stored procedures prior to execution for changes to any tables that they reference. This feature prevents many of the common causes for the -710 error.
When the Auto Re-prepare feature is enabled the following semantics apply:
Before execution of a prepared statement or SPL routine the database server checks to determine if the structure of directly or indirectly referenced tables may have changed. If changes are detected the statement is checked prior to re-prepare:
* A change in the number or type of columns in the SELECT list will result in error -710 being raised. In this case the application should prepare and execute the statement again to allow the database server to re-describe the SELECT list.
* For other changes the database server automatically re-prepares the statement. If this is successful the statement will then be executed.
Stored procedures are re-optimized and executed if the reoptimization succeeds.
An error that occurs during the re-prepare of a statement or re-optimization of a SPL routine is returned to the application in place of the -710 error.
When the Auto Re-pepare feature is not enabled the following semantics apply:
A subsequent OPEN command for a cursor will fail if the cursor refers to the invalid prepared statement; the failure will occur even if the OPEN command has the WITH REOPTIMIZATION clause.
If an index was added or the fragmentation scheme was altered after the statement was prepared, you must prepare the statement again and declare the cursor again. You cannot simply reopen the cursor if it was based on a prepared statement that is no longer valid.
This error can also occur if the table referenced by the statement has any foreign key references to tables that were renamed after the statement was prepared.
Each SPL routine is optimized the first time that it is run (not when it is created). This behavior means that an SPL routine might succeed the first time it is run but fail later under virtually identical circumstances. The failure of an SPL routine can also be intermittent because failure during one execution forces an internal warning to reoptimize the procedure before the next execution. The database server keeps a list of tables that the SPL routine references explicitly. Whenever any of these explicitly referenced tables is modified, the database server reoptimizes the procedure the next time the procedure is executed.
However, if the SPL routine depends on a table that is referenced only indirectly, the database server cannot detect the need to reoptimize the procedure after that table is changed. For example, a table can be referenced indirectly if the SPL routine invokes a trigger. If a table that is referenced by the trigger (but not directly by the SPL routine) is changed, the database server does not know that it should reoptimize the SPL routine before running it. When the procedure is run after the table has been changed, this error can occur.
Use one of two methods to recover from this error:
* Issue the UPDATE STATISTICS statement to force reoptimization of the procedure.
* Rerun the procedure.
To prevent this error, you can force reoptimization of the SPL routine. To force reoptimization, execute the following statement:
UPDATE STATISTICS FOR PROCEDURE procedure_name
You can add this statement to your program in either of the following ways:
* Place the UPDATE STATISTICS statement after each statement that changes the mode of an object.
* Place the UPDATE STATISTICS statement before each execution of the SPL routine.
For efficiency, you can put the UPDATE STATISTICS statement with the action that occurs less frequently in the program (change of object mode or execution of the procedure). In most cases, the action that occurs less frequently in the program is the change of object mode.
When you follow this method of recovering from this error, you must execute the UPDATE STATISTICS statement for each procedure that references the changed tables indirectly unless the procedure also references the tables explicitly.
You can also recover from this error by simply rerunning the SPL routine. The first time that the stored procedure fails, the database server marks the procedure as in need of reoptimization. The next time that you run the procedure, the database server reoptimizes the procedure before running it. However, running the SPL routine twice might be neither practical nor safe. A safer choice is to use the UPDATE STATISTICS statement to force reoptimization of the procedure.