ADMIN_REVALIDATE_DB_OBJECTS procedure - Revalidate invalid database objects

The ADMIN_REVALIDATE_DB_OBJECTS procedure revalidates database objects.

This procedure takes various input parameters that control the level of revalidation that is performed:
  • 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

Read syntax diagramSkip visual syntax diagramADMIN_REVALIDATE_DB_OBJECTS(object_type ,object_schema,object_name,force, debug_flag)

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
This value is not case-sensitive. This value can be a null value which means that all of the object types are considered.

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.
The argument is ignored for SQL PL and PL/SQL objects that do not meet the criteria listed previously and for any object that is not a SQL PL or PL/SQL object.
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.
The argument is ignored for SQL PL and PL/SQL objects that do not meet the criteria listed previously and for any object that is not a SQL PL or PL/SQL object.
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

One of the following authorities is required:
  • 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

Example 1: Revalidate everything in the current database.
  CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(NULL, NULL, NULL)
Or, alternatively, call the procedure without any parameters.
  CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS()
Example 2: Revalidate all objects that are qualified by the schema MY_SCHEMA.
  CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(NULL, 'MY_SCHEMA', NULL) 
Example 3: Revalidate all trigger objects in the database.
  CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('trigger', NULL, NULL) 
Example 4: Revalidate a specific view object.
  CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('view', 'MY_SCHEMA', 'MY_VIEW') 
Example 5: Revalidate all procedures under MY_SCHEMA. In this example, there are three procedures (proc1, proc2, and proc3) under this schema. The referenced object used by proc1 does not exist. The following call revalidates proc2 and proc3, but proc1 remains invalid. In this situation, the call returns a warning.
  CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('procedure', 'MY_SCHEMA', NULL) 
Example 6: Revalidate an object that does not exist. This example returns an error.
  CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('procedure', 'MY_SCHEMA', 'MY_VIEW')
Example 7: Revalidate all procedures under MY_SCHEMA using the named parameter notation.
  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.