auto_reval - Automatic revalidation and invalidation configuration parameter

Configuration type

Database

Parameter type

• Configurable online
• Configurable by member in a Db2® pureScale® environment

Propagation class

Immediate

Default [range]

DEFERRED [IMMEDIATE, DISABLED, DEFERRED, DEFERRED_FORCE]

This configuration parameter is dynamic, meaning that a change in its value takes effect immediately. You do not have to reconnect to the database for the change to take effect.

If you create a new database, by default this configuration parameter is set to DEFERRED.

If you upgrade a database from Version 9.5, or earlier, auto_reval is set to DISABLED. The revalidation behavior is the same as in the previous releases.

If you set this parameter to IMMEDIATE it means that all dependent objects will be revalidated immediately after objects are invalidated. This applies to some DDL statements, such as ALTER TABLE, ALTER COLUMN, or CREATE OR REPLACE. The successful revalidation of the dependent objects does not rely on any other DDL changes; therefore, revalidation can be completed immediately.

If you set this parameter to DEFERRED, it means that all dependent objects are revalidated the next time that they are accessed. To reduce the impact of object revalidation on future access, once you have finished with the changes that cause object invalidation, it is strongly recommended that you run the SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS procedure to revalidate invalid objects affected and the db2rbind command to revalidate invalid packages.

Note that if you set this parameter either to IMMEDIATE or DEFERRED, and if any revalidation operation fails, the invalid dependent objects will remain invalid until the next time that they are accessed.

If you set this parameter to DEFERRED_FORCE it behaves the same way as when it is set to DEFERRED and an additional CREATE with error feature is enabled.

In some cases, the syntax that you explicitly specify might override the setting of auto_reval. For example, if you use the DROP COLUMN clause of the ALTER TABLE statement without specifying CASCADE or RESTRICT, the semantics are controlled by auto_reval. However, if you specify CASCADE or RESTRICT, the previous cascade or restrict semantics are used, overriding the new semantics specified by auto_reval.