HP 3000 Manuals HP 3000 Manuals
GENPLAN
The GENPLAN statement places the access plan generated by the optimizer
for a SELECT, UPDATE, or DELETE 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
[REV BEG]
GENPLAN [WITH (HostVariableDefinition )] FOR
{SQLStatement }
{MODULE SECTION [Owner.]ModuleName(Section Number) }
{PROCEDURE SECTION [Owner.]ProcedureName(Section Number)}
[REV END]
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.[REV
BEG]
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 identifies the module section whose access plan
(Section Number) 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 identifies the procedure section whose access
(Section Number) 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.[REV END]
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.
_________________________________________________________________
NOTE GENPLAN checks only for syntax errors. It does not check
for mismatched data types or other errors that may occur.
In order to guarantee complete error checking, do not
include a statement in GENPLAN unless it has previously run
without errors.
_________________________________________________________________
* You should take the following steps when embedding a statement
from an application in the GENPLAN statement:
* In the GENPLAN WITH clause, define variable names and
compatible SQL data types for each input host variable
appearing in the application statement. Do not include
indicator variables in the WITH clause for columns that
allow nulls. Indicator variables are not used by GENPLAN.
* Remove the INTO clause and its associated output host
variables. Only input host variables are considered when
generating the access plan.
* The following language specific tables show the SQL data type that
must be placed in the WITH clause of the GENPLAN statement for
each type of host variable, if an accurate access plan is to be
generated. In some cases, the data type specified in the WITH
clause of the GENPLAN statement is not the same data type which is
compatible with the SQL data type of the column containing the
data. The data type specified below must be used, regardless of
the SQL column data type. This ensures that the plan displayed by
the GENPLAN statement is the same as the plan chosen by the
optimizer when the statement is preprocessed in an application.
Table 10-3. GENPLAN WITH Clause Data Types--COBOL
-------------------------------------------------------------------
| | |
| COBOL Host Variable | GENPLAN WITH Clause |
| Data Type Declaration | SQL Data Type |
| | |
-------------------------------------------------------------------
| | |
| 01 DATA-NAME PIC X. | CHAR |
| | |
| 01 DATA-NAME PIC X(n). | CHAR(n) |
| | |
| 01 GROUP-NAME. | VARCHAR(n) |
| | |
| 49 LENGTH-NAME PIC S9(9) COMP. | |
| | |
| 49 VALUE-NAME PIC X(n). | |
| | |
| 01 DATA-NAME PIC S9(4) COMP. | SMALLINT |
| | |
| 01 DATA-NAME PIC S9(9) COMP. | INTEGER |
| | |
| 01 DATA-NAME PIC S9(p-s)V9(s) COMP-3.| DECIMAL(p,s) |
| | |
-------------------------------------------------------------------
Table 10-4. GENPLAN WITH Clause Data Types--Pascal
-------------------------------------------------------------------
| | |
| Pascal Host Variable | GENPLAN WITH Clause |
| Data Type Declaration | SQL Data Type |
| | |
-------------------------------------------------------------------
| | |
| DataName : char; | CHAR |
| | |
| DataName : array [1..n] of char; | CHAR(n) |
| | |
| DataName : packed array [1..n] of char;HAR(n) |
| | |
| DataName : string[n]; | VARCHAR(n) |
| | |
| DataName : smallint; | SMALLINT |
| | |
| DataName : integer; | INTEGER |
| | |
| DataName : longreal; | FLOAT |
| | |
| DataName : real; | REAL |
| | |
-------------------------------------------------------------------
Table 10-5. GENPLAN WITH Clause Data Types--FORTRAN
-------------------------------------------------------------------
| | |
| FORTRAN Host Variable | GENPLAN WITH Clause |
| Data Type Declaration | SQL Data Type |
| | |
-------------------------------------------------------------------
| | |
| CHARACTER DataName | CHAR |
| | |
| CHARACTER*n DataName | CHAR(n) |
| | |
| INTEGER*2 DataName | SMALLINT |
| | |
| INTEGER DataName | INTEGER |
| | |
| REAL DataName | REAL |
| | |
| REAL*4 DataName | REAL |
| | |
| DOUBLE PRECISION DataName | FLOAT |
| | |
| REAL*8 DataName | FLOAT |
| | |
-------------------------------------------------------------------
Table 10-6. GENPLAN WITH Clause Data Types--C
-------------------------------------------------------------------
| | |
| C Host Variable | GENPLAN WITH Clause |
| Data Type Declaration | SQL Data Type |
| | |
-------------------------------------------------------------------
| | |
| char dataname; | CHAR |
| | |
| char dataname[n+1]; | VARCHAR(n) |
| | |
| short dataname; | SMALLINT |
| | |
| short int dataname; | SMALLINT |
| | |
| int dataname; | INTEGER |
| | |
| long int dataname; | INTEGER |
| | |
| long dataname; | INTEGER |
| | |
| float dataname; | REAL |
| | |
| double dataname; | FLOAT |
| | |
-------------------------------------------------------------------
_________________________________________________________________
NOTE It is your responsibility to ensure that for each simulated
host variable defined in the GENPLAN statement WITH clause,
you use the SQL data type shown in the tables. If you use
an incorrect data type, GENPLAN will generate a plan.
However, it may not be the plan the optimizer will choose
when your application is preprocessed.
_________________________________________________________________
* For each individual session, SYSTEM.PLAN stores the result of only
one GENPLAN at a time. If GENPLAN is issued twice in succession,
the second plan will replace the first. The access plan generated
by GENPLAN is removed from SYSTEM.PLAN as soon as a COMMIT WORK or
ROLLBACK WORK statement is issued.[REV BEG]
* GENPLAN can be applied to a type II insert query.
* The active SETOPT will be used for the statement of GENPLAN on an
SQLStatement only. A currently active SETOPT is ignored if a
GENPLAN statement is executed on a section.
* You can find the section number from the source file produced by
the preprocessor after the application is processed.
* Use the following information to find the section number for a
procedure statement:
* A section exists for each SQL statement in a procedure
except:
* BEGIN WORK
* ROLLBACK WORK
* SAVEPOINT
* OPEN cursor
* CLOSE cursor
* Procedure sections are numbered consecutively, starting
with 1, from the start of the procedure, with no regard to
any branching or looping constructs in the procedure.
[REV END]
* Multiple sessions may issue the GENPLAN statement at the same time
because each session has its own individual copy of SYSTEM.PLAN.
* See the section "Using GENPLAN to Display the Access Plan" in the
"SQL Queries" chapter for information on how to interpret the
plan.
* You cannot use GENPLAN with the SYSTEM or CATALOG views.
Authorization
To execute GENPLAN, you must have DBA authority or the appropriate
combination of SELECT, UPDATE, or DELETE authorities for the tables and
views accessed by the included SQL statement. In the case of views, you
must have the appropriate authorities for all underlying views and base
tables, as well.
Examples
1. Interactive SQL statement for the following query:
>isql=> SELECT PartName, VendorNumber, UnitPrice
> FROM Purchdb.Parts p, PurchDB.SupplyPrice sp
> WHERE p.PartNumber = sp.PartNumber
> AND p.PartNumber = '1123-P-01';
Generate the Plan:
isql=> GENPLAN FOR
> SELECT partname, vendornumber, UnitPrice
> FROM PurchDB.Parts p, PurchDB.SupplyPrice sp
> WHERE p.PartNumber = sp.PartNumber
> AND p.PartNumber = '1123-P-01';
Display the Plan:
isql=> SELECT * FROM System.Plan;
SELECT * FROM System.Plan;
-----------+-----------+-----------+--------------------+------------------
QUERYBLOCK |STEP |LEVEL |OPERATION |TABLENAME
-----------+-----------+-----------+--------------------+------------------
1| 1| 2|index scan |PARTS
1| 2| 2|serial scan |SUPPLYPRICE
1| 3| 1|nestedloop join |
---------------------------------------------------------------------------
Number of rows selected is 3
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int], <n>, or e[nd] >r
+--------------------+----------------
|OWNER |INDEXNAME
+--------------------+----------------
|PURCHDB |PARTNUMINDEX
|PURCHDB |
| |
---------------------------------------------------------------------------
Number of rows selected is 3
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int], <n>, or e[nd] >e
2. SQL statement simulating use of host variables in an application
for the following query taken from an application:
EXEC SQL SELECT PartName, VendorNumber, UnitPrice
INTO :PartName, :VendorNumber, :UnitPrice
FROM PurchDB.Parts p, PurchDB.SupplyPrice sp
WHERE p.PartNumber = sp.PartNumber
AND p.PartNumber = :PartNumber
Remove INTO clause when placing the statement into GENPLAN.
Generate the plan in ISQL:
Define input host variable names and compatible SQL data types in
WITH clause.
isql=> GENPLAN WITH (PartNumber char(16)) FOR
> SELECT PartName, VendorNumber, UnitPrice
> FROM PurchDB.Parts p, PurchDB.SupplyPrice sp
> WHERE p.PartNumber = sp.PartNumber
> AND p.PartNumber = :PartNumber;
Display the plan:
isql=> SELECT * FROM System.Plan;
SELECT * FROM System.Plan;
-----------+-----------+-----------+--------------------+------------------
QUERYBLOCK |STEP |LEVEL |OPERATION |TABLENAME
-----------+-----------+-----------+--------------------+------------------
1| 1| 2|index scan |PARTS
1| 2| 2|serial scan |SUPPLYPRICE
1| 3| 1|nestedloop join |
---------------------------------------------------------------------------
Number of rows selected is 3
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int], <n>, or e[nd] >r
+--------------------+----------------
|OWNER |INDEXNAME
+--------------------+----------------
|PURCHDB |PARTNUMINDEX
|PURCHDB |
| |
---------------------------------------------------------------------------
Number of rows selected is 3
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int], <n>, or e[nd] >e
[REV BEG]
3. Example of GENPLAN for a MODULE SECTION.
GENPLAN FOR MODULE SECTION MyModule(10);
[REV END]