DB2 Version 9.7 for Linux, UNIX, and Windows

ADMIN_REVALIDATE_DB_OBJECTS procedure - Revalidate invalid database objects

The ADMIN_REVALIDATE_DB_OBJECTS procedure revalidates invalid database objects.

This procedure takes three input parameters, object_type, object_schema, and object_name, 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 all parameters.

Syntax


>>-ADMIN_REVALIDATE_DB_OBJECTS--(--object_type--,--object_schema--,-->

>--object_name--)----------------------------------------------><

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
METHOD
MODULE
PROCEDURE
SPECIFIC
TABLE
TRIGGER
TYPE
VIEW

This value is not case-sensitive. This value can be NULL.

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.

Authorization

EXECUTE privilege on the ADMIN_REVALIDATE_DB_OBJECTS procedure.

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.

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.