IFX_AUTO_REPREPARE session environment option

Use the IFX_AUTO_REPREPARE session environment option to temporarily change the system-wide setting of the AUTO_REPREPARE configuration parameter. The new setting is in effect for the remainder of the session or until you reset IFX_AUTO_REPREPARE for the session.

If IFX_AUTO_REPREPARE is enabled during table schema changes that do not require reissuing the DESCRIBE statement, the database server automatically identifies and reprepares prepared statements and reoptimizes SPL routines that reference the modified table. Enabling this option might also reduce the need to issue the PREPARE statement explicitly to reprepare prepared objects, or to issue the UPDATE STATISTICS FOR ROUTINE statement explicitly to reoptimize SPL routines.

While the IFX_AUTO_REPREPARE option is enabled, you can avoid -710 errors after some changes to the schema of a database table, such as adding an enabled index. However, enabling this option does not prevent -710 errors altogether. Error -710 might be issued when a cursor attempts to execute a prepared object, or when an SPL routine performs a query, after DDL operations have changed the schema of a table that the prepared object or the SPL routine references.

The IFX_AUTO_REPREPARE session environment option has this syntax:

IFX_AUTO_REPREPARE session environment option

{ IFX_AUTO_REPREPARE } { OFF | '0' | ON | '1' | '3' | '5' | '7' }

Usage

The value that is specified by the SET ENVIRONMENT IFX_AUTO_REPREPARE statement can enable or disable automatic repreparation:

'1' or ON
Enables automatic repreparation
'0' or OFF
Disables automatic repreparation
'3'
Enables automatic repreparation in optimistic mode
'5'
Enables automatic repreparation on update statistics
'7'
Enables automatic repreparation in optimistic mode and on update statistics

Numeric values require single- or double-quotation delimiters. Alphabetic values are not case-sensitive.

Optimistic mode offers faster performance by not checking statements that successfully ran less than a second ago. In the unlikely event that tables were modified in the interim, some -710 errors might occur.

The database server might not detect some changes to a table schema that invalidate prepared objects or SPL routines, even when IFX_AUTO_REPREPARE is enabled. For example, changes to a table schema by one session causes concurrent sessions to receive error -710 when they attempt to read the same table after they obtain a shared lock.

Enabling IFX_AUTO_REPREPARE might have no effect on prepared statements and SPL routines that reference tables in which DDL operations change the number of columns in the table, or change the data type of a column. To avoid error -710 after these schema changes, you typically must reissue the DESCRIBE statement, the PREPARE statement, and (for cursors associated with routines) the UPDATE STATISTICS FOR ROUTINE statement for any routines that reference the table whose schema has been modified.

If enabling the IFX_AUTO_REPREPARE session environment variable results in a runtime error, that error is passed back to the application.

Examples

The following statement enables automatic repreparation after DDL operations on tables that prepared objects or SPL routines reference: 
SET ENVIRONMENT IFX_AUTO_REPREPARE '1';
That setting overrides the setting of the AUTO_REPREPARE configuration parameter, if it is zero or 'None', for the remainder of the current session, or until you reset IFX_AUTO_REPREPARE.
The following statement, which uses an equivalent keyword, has the same effect for the current session:
SET ENVIRONMENT IFX_AUTO_REPREPARE on;

The following sequence of statements from different sessions will result in a -710 error, even though IFX_AUTO_REPREPARE is enabled for Session 1:

  1. Session 1 prepares the following statement: PREPARE s FROM "SELECT c1, c2 FROM t1"
  2. Session 2 drops column c2 from table t1: ALTER TABLE t1 DROP (c2)
  3. Session 1 attempts to execute the following statement: EXECUTE s INTO :v1, :v2

As a result, the database server sends the -710 error to the client application because it cannot send a value for column c2, which no longer exists. For the statement to succeed, you must resubmit it without referencing column c2.

When you are satisfied with how your client application currently handles errors from schema changes, you can stop overriding the system-wide setting of the AUTO_REPREPARE configuration parameter. For example, you can use one of the following equivalent statements: 
SET ENVIRONMENT IFX_AUTO_REPREPARE OFF;
SET ENVIRONMENT IFX_AUTO_REPREPARE off;
SET ENVIRONMENT IFX_AUTO_REPREPARE "0";
SET ENVIRONMENT IFX_AUTO_REPREPARE '0';