ALLBASE/SQL Reference Manual (36216-90216)

Chapter 11 429
SQL Statements E - R
GENPLAN
GENPLAN
The GENPLAN statement places the access plan generated by the optimizer for a SELECT,
UPDATE,orDELETE statement into the pseudotable SYSTEM.PLAN. You can then view the
access plan by issuing the following statement from within the same transaction:
isql=> SELECT * FROM SYSTEM.PLAN;
Scope
ISQL or Application Programs
SQL Syntax
GENPLAN [WITH ( HostVariableDefinition )] FOR
{ SQLStatement
MODULE SECTION [ Owner. ] ModuleName(Section Number )
PROCEDURE SECTION [ Owner. ] ProcedureName(Section Number )}
Parameters
WITH is used when simulating embedded statements taken from application
programs. The WITH clause defines variables of a specified data type. The
variables are used in the WHERE clause where an input host variable
would appear if the
SQLStatement were embedded in an application.
HostVariableDefinition designates a variable used to simulate a host variable that
would appear in a statement in an application program. This clause is only
allowed for an
SQLStatement .
SQLStatement can be any valid SQL SELECT, UPDATE, or DELETE statement including
complex statements containing UNION, OUTER JOIN, or nested
subqueries.
[
Owner ]. ModuleName ( Section Number ) identifies the module section whose access plan
is to be generated. 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
CATALOG.SECTION view.
[
Owner .] ProcedureName ( Section Number ) identifies the procedure section whose
access plan is to be generated. The owner name is the DBEUserID of the
person who created the procedure or the owner name specified when the
procedure was created. The
ProcedureName is the name stored in the
CATALOG.PROCEDURE view or CATALOG.SECTION view.
Description
• The GENPLAN statement can only be used in ISQL. It cannot be used in an application,
in a static SQL statement, or in dynamic preprocessing.