|
|
The VALIDATE statement validates modules and procedures that have
already been created.
ISQL or Application Programs
VALIDATE [FORCE
DROP SETOPTINFO]
{MODULE { {[Owner.]ModuleName} [,...]
{SECTION [ Owner.]ModuleName (SectionNumber)} [,...] }
PROCEDURE { {[Owner.]ProcedureName} [,...]
{SECTION [Owner.]ProcedureName (SectionNumber)} [,...] }
ALL{MODULES
PROCEDURES} [WITH AUTOCOMMIT] }
- 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.
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
Chapter 4 "Constraints, Procedures, and
Rules"
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.
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.
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;
|