AUTO_REPREPARE configuration parameter

The AUTO_REPREPARE configuration parameter controls whether the database server automatically reoptimizes SPL routines and reprepares prepared objects after the schema of a table that is referenced by the SPL routine or by the prepared object was changed.

onconfig.std value
Not set. If the AUTO_TUNE configuration parameter is set to 1, SPL routines are automatically reoptimized and prepared objects are automatically reprepared.
values
0 = Disables the automatic repreparation of prepared objects after the schema of a directly or an indirectly referenced table is modified. Also disables the automatic reoptimization of SPL routines after the schema of an indirectly referenced table is modified.

1 = Enables 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.

takes effect
After you edit your onconfig file and restart the database server.
When you reset the value dynamically in your onconfig file by running the onmode -wf command.
If an AUTO_REPREPARE value is not set in your current onconfig file and you edit the AUTO_TUNE configuration parameter and restart the database server
When you reset the value in memory by running the onmode -wm command.

Usage

Enable the AUTO_REPREPARE configuration parameter to reduce the number of reprepare operations that you must perform explicitly after modifying the schema of a table that is referenced by a dynamic SQL statement or a DML statement in an SPL routine.

For example, certain DDL statements modify the schema of a table, such as CREATE INDEX, DROP INDEX, DROP COLUMN, and RENAME COLUMN. If the AUTO_REPREPARE configuration parameter is disabled when these DDL statements are run, users might receive -710 errors. These errors occur the next time that you run:
  • An SPL routine that directly or indirectly references tables that were modified by the DDL statements
  • A prepared object that references the tables that were modified by the DDL statements

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

Set automatic repreparation on update statistics if you want to avoid the database server using an older, suboptimal execution plan.
Restriction:

Enabling AUTO_REPREPARE might have no effect on prepared statements or on 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. After these schema changes, typically you must reissue the DESCRIBE statement, the PREPARE statement (for prepared objects), and the UPDATE STATISTICS FOR ROUTINE statement (for cursors associated with routines) for optimized execution plans of SPL routines that reference the table whose schema has been modified. Otherwise, the database server might issue SQL error -710.


Copyright© 2019 HCL Technologies Limited