ALLBASE/SQL Reference Manual (36216-90216)

594 Chapter12
SQL Statements S - Z
VALIDATE
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;
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;