ADMIN_REVALIDATE_DB_OBJECTS procedure - Revalidate invalid database objects
The ADMIN_REVALIDATE_DB_OBJECTS procedure revalidates database objects.
- To revalidate all of the invalid objects in the database, either specify NULL for all parameters, or call the procedure without parameters.
- To revalidate all of the invalid database objects under a specific schema, specify a value for object_schema, and specify NULL for object_name and object_type.
- To revalidate a specific invalid database object, specify valid values for the first parameters.
- To force the revalidation of compiled SQL PL or PL/SQL objects regardless of their state being valid or invalid, specify a value for object_type, object_schema, object_name, and specify 'Y' for force.
- To enable or disable debug on compiled SQL PL or PL/SQL objects being revalidated, specify 'Y' or 'N' for debug_flag.
Syntax
The schema is SYSPROC.
Procedure parameters
- object_type
- An input argument of type VARCHAR(30) that identifies the type
of the database object. The following types are valid:
- FUNCTION
- GLOBAL_VARIABLE
- MASK
- METHOD
- MODULE
- PERMISSION
- PROCEDURE
- SPECIFIC
- TABLE
- TRIGGER
- TYPE
- USAGELIST
- VIEW
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.
- object_schema
- An input argument of type VARCHAR(128) that identifies the schema name used to qualify database object references. The name is case-sensitive. This value can be NULL.
- object_name
- An input argument of type VARCHAR(128) that identifies a database object. The name is case-sensitive. This value cannot be the value of a typed table or a row function, because the procedure does not support these types of objects; if the name of such an object is specified, an error is returned. This value can be NULL.
- force
- An input argument of type CHAR(1) to indicate if revalidation
should be forced for the following objects (regardless of their current
state).
- All SQL PL and PL/SQL objects if the invoking user holds DBADM authority.
- SQL PL and PL/SQL objects that the invoking user owns.
Values are:- 'Y' or 'y' - All compiled SQL PL and PL/SQL objects will be revalidated.
- 'N', 'n' or NULL - Default behavior, only invalid objects will be revalidated.
- debug_flag
- An input argument of type CHAR(1) to indicate whether debug mode
is enabled or disabled for the following objects processed by the
procedure:
- All SQL PL and PL/SQL objects if the invoking user holds DBADM authority or is a member of the built-in role, SYSDEBUG.
- SQL PL and PL/SQL objects that the invoking user owns.
Values are:- 'Y' or 'y' - Compiled SQL PL and PL/SQL objects are revalidated with the debug mode enabled.
- 'N' or 'n' - Compiled SQL PL and PL/SQL objects are revalidated with the debug mode disabled.
- NULL - Compiled SQL PL and PL/SQL objects are revalidated with no change in the debug mode. This is the default behavior.
Authorization
- EXECUTE privilege on the ADMIN_REVALIDATE_DB_OBJECTS procedure
- SECADM authority when object_type is MASK or PERMISSION
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.
Default PUBLIC privilege
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.
Examples
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')
Usage notes
All of the non-null parameter values that are passed to the ADMIN_REVALIDATE_DB_OBJECTS procedure must be satisfied, or the procedure cannot identify the objects that need to be revalidated. For example, if you specify a view name, but with a trigger type, the procedure does not revalidate the view, because the type does not match.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.