DDL Operations on Tables Referenced in Prepared Objects

Various DDL statements can drop, rename, or alter the schema of a table that a prepared object references, but subsequent attempts to execute the prepared object might fail with error -710, or might produce unexpected results.

These restrictions do not necessarily apply, however, if an index is added or dropped when automatic recompilation is enabled for prepared objects and routines that directly 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 using the SET ENVIRONMENT IFX_AUTO_REPREPARE statement to enable or disable automatic recompilation after changes to the schema of a table, and for contexts where the database server issues error -710 even when automatic recompilation is enabled, see IFX_AUTO_REPREPARE session environment option.

When the AUTO_REPREPARE configuration parameter and the IFX_AUTO_REPREPARE session environment variable are set to disable automatic recompilation, however, adding an index to a table that a prepared statement references indirectly can similarly invalidate the prepared statement. A subsequent OPEN statement fails if the cursor refers to the invalid prepared statement, even if the OPEN statement includes the WITH REOPTIMIZATION keywords. If an index on an indirectly referenced table is added after the statement was prepared while automatic recompilation is disabled, you must prepare the statement again and declare the cursor again. You cannot simply reopen the cursor if it is based on a prepared statement that is no longer valid.

Related Statements

For information about basic concepts that relate to the PREPARE statement, see the HCL OneDB Guide to SQL: Tutorial.

For information about more advanced concepts that relate to the PREPARE statement, see the HCL OneDB ESQL/C Programmer's Manual.