DDL Operations on Tables Referenced by Cursors

Various DDL statements can drop, rename, or alter the schema of a table that is referenced directly (or indirectly, by the identifier of a prepared statement) in the DECLARE statement that defines a cursor. Subsequent OPEN operations on the cursor might fail with error -710, or might produce unexpected results. Changing the number of columns or the data type of a column has this effect, and the user typically must reissue the DESCRIBE statement, the PREPARE statement, and (for cursors associated with routines) the UPDATE STATISTICS statement for any SPL routines that reference a table whose schema has been modified.

These restrictions do not apply, however, if an index is added or dropped when automatic recompilation is enabled for prepared objects and for SPL routines that reference tables that ALTER TABLE, CREATE INDEX, or DROP INDEX operations have modified. This is the default behavior of HCL OneDB™. For more information about enabling or disabling automatic recompilation after schema changes, see the description of the IFX_AUTO_REPREPARE option to the SET ENVIRONMENT statement. For more information about the AUTO_REPREPARE configuration parameter, see your HCL OneDB Administrator's Reference.

When the AUTO_REPREPARE configuration parameter and the IFX_AUTO_REPREPARE session environment variable are set to disable recompilation of prepared objects, however, adding an index to a table that is referenced directly or indirectly in a DECLARE statement can similarly invalidate the associated cursor. Subsequent OPEN statements that specify the invalid cursor fail, even if they include the WITH REOPTIMIZATION keywords. If an index is added to the table that is associated with a cursor while automatic recompilation is disabled, you must prepare the statement again and declare the cursor again before you can open the cursor. For cursors associated with calls to SPL routines, you must run the UPDATE STATISTICS statement for routines that reference tables to which an index has been added or dropped. You cannot simply reopen a cursor that is based on a prepared statement that is no longer valid.