HP 3000 Manuals HP 3000 Manuals
VALIDATE
[REV BEG][REV END]
The VALIDATE statement validates modules and procedures that have already
been created.
Scope
ISQL or Application Programs
SQL Syntax
[REV BEG]
VALIDATE [FORCE ]
[DROP SETOPTINFO]
{MODULE {{[Owner.]ModuleName}[,...] } }
{ {{SECTION [Owner.]ModuleName (SectionNumber)}[,...]} }
{ }
{PROCEDURE {{[Owner.]ProcedureName}[,...] }}
{ {{SECTION [Owner.]ProcedureName (SectionNumber)}[,...]}}
{ }
{ALL {MODULES } [WITH AUTOCOMMIT] }
{ {PROCEDURES} }
[REV END]
Parameters
[REV BEG]
WITH AUTOCOMMIT executes automatically a COMMIT WORK after each
module or procedure is updated.[REV END]
[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.[REV BEG]
[Owner.]ModuleName identifies the section number as well as the
(SectionNumber) module to be validated.[REV END]
[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.[REV
BEG]
[Owner.]ProcedureName identifies the section number as well as the
(SectionNumber) procedure to be validated.
[REV END]
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.[REV BEG]
* 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.[REV END]
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
1. 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;
2. 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;
[REV BEG]
3. 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;
4. 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;
[REV END]