The ADMIN_REVALIDATE_DB_OBJECTS procedure revalidates database objects.
>>-ADMIN_REVALIDATE_DB_OBJECTS--(--object_type--,--object_schema--,--> >--object_name--,--force--,--debug_flag--)---------------------><
The schema is SYSPROC.
If any of these types is specified, the procedure revalidates all of the invalid objects of that type, with the exception of those that belong to a MODULE. If you want to revalidate objects that are inside of a module, use the MODULE type with the name of a specific module, and all of the invalid objects inside of that module will be revalidated.
If there is a routine that has more than one parameter signature and you only want to revalidate one of them, use the SPECIFIC type with the name of the routine that you want to revalidate.
If you use the TABLE type, the specified tables will be reorganized and their statistics will be collected. The procedure invokes the reorg utility, followed by the runstats utility, against regular or materialized query tables that are in reorg-pending state. The procedure will attempt to use a user profile for runstats, if one exists. If not, a default runstats operation is invoked.
When the value for the force option is 'Y', the invoking user must either be the owner of the object being revlaidated or hold DBADM authority.
When the value for the debug_flag option is 'Y', the invoking user must be the owner of the object, hold DBADM authority, or be a member of the SYSDEBUG system defined role.
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.
CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(NULL, NULL, NULL)
Or,
alternatively, call the procedure without any parameters. CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS()
CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(NULL, 'MY_SCHEMA', NULL)
CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('trigger', NULL, NULL)
CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('view', 'MY_SCHEMA', 'MY_VIEW')
CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('procedure', 'MY_SCHEMA', NULL)
CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('procedure', 'MY_SCHEMA', 'MY_VIEW')
CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(
object_type=>'PROCEDURE',object_schema=>'MY_SCHEMA')
This procedure will revalidate only invalid objects and regular or materialized query tables in reorg-pending state. All invalid objects can be found in SYSCAT.INVALIDOBJECTS. To find out which tables are in reorg-pending state, use the ADMIN_GET_TAB_INFO table function.
If a valid object is specified as input, the procedure will not perform any operation and returns a success code. If a failure occurs during the revalidation of tables, the procedure fails. If a failure occurs during the revalidation of other objects, the procedure ignores the failure and continues revalidating the other objects. If there is at least one failure, the procedure returns a warning (SQLSTATE 0168B). If the revalidation of all objects fails, the procedure returns an error (SQLSTATE 429C4). The details of all revalidation failures of objects except tables can be found in SYSCAT.INVALIDOBJECTS.
In order to revalidate invalid masks or permissions, the user that runs ADMIN_REVALIDATE_DB_OBJECTS must have SECADM authority. If there is at least one failure, and the first failure is because the user does not have SECADM authority during revalidation of a mask or permission, the procedure returns a warning (SQLSTATE 0168B, SQLCODE +361), msg-token2 contains CREATE PERMISSION or CREATE MASK. If the revalidation of all objects fails, and the first failure is because the user does not have SECADM authority during revalidation of a mask or permission, the procedure returns an error (SQLSTATE 42501, SQLCODE -551).
When a global variable is revalidated, it is also instantiated for the current session.
To monitor the progress of a table revalidation, you can monitor the progress of the associated table reorg operation. For all other objects, query the SYSCAT.INVALIDOBJECTS catalog view; objects are deleted from this view when they are successfully revalidated, and entries are updated if revalidation fails.
To ensure consistency of catalogs data, the ADMIN_REVALIDATE_DB_OBJECTS routine is designed to run explicit COMMIT statements. Since the ADMIN_REVALIDATE_DB_OBJECTS routine runs explicit COMMIT statements, it might not be possible to use a ADMIN_REVALIDATE_DB_OBJECTS routine as a part of a Unit of Work (UOW). For example, when a UOW runs a ROLLBACK statement, any changes that are made to database objects previous to and by the ADMIN_REVALIDATE_DB_OBJECTS routine before the ROLLBACK statement was run remains in effect.