VALIDATE

The VALIDATE statement validates modules and procedures that have already been created.

Scope

ISQL or Application Programs

SQL Syntax

VALIDATE [ FORCE DROP SETOPTINFO ] { MODULE { { [Owner.] ModuleName }[,...] { SECTION [Owner.]ModuleName (SectionNumber)}[,...] } PROCEDURE { {[Owner.] ProcedureName }[,...] { SECTION [Owner.] ProcedureName (SectionNumber)}[,...] } ALL { MODULES PROCEDURES } [WITH AUTOCOMMIT] }

Parameters

WITH AUTOCOMMIT: executes automatically a COMMIT WORK after each module or procedure is updated.
[Owner.]ModuleName: identifies the module containing sections to be validated. The owner name is the DBEUserID of the person who preprocessed the program or the owner name specified when the program was preprocessed. The module name is the name stored in the SYSTEM.SECTION view.
[Owner.]ModuleName (SectionNumber): identifies the section number as well as the module to be validated.
[Owner.]ProcedureName: identifies the procedure to validate. The owner name is the DBEUserID of the person who created the procedure or the owner name specified when the procedure was created. The procedure name is the name stored in the SYSTEM.SECTION view.
[Owner.]ProcedureName (SectionNumber): identifies the section number as well as the procedure to be validated.

Description

When you validate a module or procedure, all the sections within it are checked and validation is attempted. If an embedded SQL statement accesses an object that does not exist or that the module or procedure owner is not authorized to execute, then the corresponding section is marked invalid.
You may find it convenient to use the VALIDATE statement after an UPDATE STATISTICS, since UPDATE STATISTICS will invalidate stored sections. If you issue both statements during a period of low activity for the DBEnvironment, the optimizer will have current statistics on which to base its calculations, with minimal performance degradation.
A temporary section cannot be validated.
Users can specify the access plan of a query with the SETOPT statement. To validate a module or procedure without the user-specified access plan, include the DROP SETOPTINFO keyword in the VALIDATE statement. The default access plan determined by ALLBASE/SQL is stored in the system catalog instead.
If a module or procedure cannot be validated, ALLBASE/SQL returns an error.
If a section is still invalid after revalidation, the module is considered invalid.
To find the names of procedures with invalid sections, use ISQL to query the SYSTEM.SECTION view with Stype = 0.
The VALIDATE statement will not revalidate sections that have been stored prior to this release, for example, sections that have been migrated from a previous release. These sections can only be revalidated by running the application to execute all the sections. An alternative is to recreate the module by preprocessing the application again. Thereafter, you can use the VALIDATE statement.
For detailed information on modules refer to the section "Invalidation and Revalidation of Sections" in the "Maintenance" chapter of the ALLBASE/SQL Database Administration Guide and the "Using the Preprocessor" chapter in your ALLBASE/SQL application programming guide.
For detailed information on procedures, refer to the "Constraints, Procedures, and Rules" chapter.
When the WITH AUTOCOMMIT clause is used, a COMMIT WORK statement is executed automatically after each MODULE or PROCEDURE is validated. This can reduce both log space and shared memory requirements for the VALIDATE command.
When the FORCE clause is used, all sections associated with the MODULE or PROCEDURE are revalidated, regardless of whether they are valid or invalid.
When the FORCE clause is used with VALIDATE ALL MODULES and VALIDATE ALL PROCEDURES, every stored section in the database is forced to recompile using the latest release. These statements have essentially the same effect as preprocessing every program again that uses the database.

Authorization

You can execute this statement if you have OWNER or RUN authority on a module or you have OWNER or EXECUTE authority for a procedure or if you have DBA authority.

Examples

Validating sections in a module

ALLBASE/SQL validates sections at preprocessing time and run time. To validate a section before running your application, you can use the VALIDATE statement. To find the names of modules with invalid sections, use ISQL to query the SYSTEM.SECTION view.

   isql=> SELECT Name, Section FROM System.Section

   > WHERE valid = 0 and stype = 0;



   SELECT Name, Section FROM System.Section WHERE Valid=0 and Stype=0;

   --------------------+---------------

   NAME                |SECTION         

   --------------------+---------------

   CEXP06              |1         

   CEXP06              |2         

   CEXP06              |3         

   --------------------------------------------------------------------------

   First 3 rows have been selected.

   U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]>

Three sections of the module named CEX06 are invalid. Issue the VALIDATE statement to attempt validation.

   isql => VALIDATE MODULE CEXP06;

Dropping SETOPT access plan
The following SETOPT statement specifies that every table with an index is accessed with an index scan.
isql => SETOPT GENERAL INDEXSCAN;
Validate the CEX09 module, but ignore the access plan specified in the preceding SETOPT statement.
isql => VALIDATE DROP SETOPTINFO MODULE CEXP09;
When the WITH AUTOCOMMIT clause is used, a COMMIT WORK statement is executed automatically after each module or procedure is validated.
VALIDATE ALL MODULES WITH AUTOCOMMIT; VALIDATE ALL PROCEDURES WITH AUTOCOMMIT;
When the FORCE clause is used, all sections associated with the MODULE or PROCEDURE are revalidated regardless of whether they are valid or invalid.
VALIDATE FORCE ALL MODULES WITH AUTOCOMMIT; VALIDATE FORCE ALL PROCEDURES WITH AUTOCOMMIT;