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

  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.




	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 11-1 GENPLAN WITH Clause Data Types — COBOL

COBOL Host Variable Data Type Declaration	GENPLAN WITH Clause 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 11-2 GENPLAN WITH Clause Data Types — Pascal

Pascal Host Variable Data Type Declaration	GENPLAN WITH Clause SQL Data Type
`DataName` : char;	CHAR
`DataName` : array [1..`n`] of char;	CHAR(n)
`DataName` : packed array [1..`n`] of char;	CHAR(n)
`DataName` : string[`n`];	VARCHAR(`n`)
`DataName` : smallint;	SMALLINT
`DataName` : integer;	INTEGER
`DataName` : longreal;	FLOAT
`DataName` : real;	REAL

Table 11-3 GENPLAN WITH Clause Data Types — FORTRAN

FORTRAN Host Variable Data Type Declaration	GENPLAN WITH Clause SQL Data Type
CHARACTER `DataName`	CHAR
CHARACTERn `DataName`*	CHAR(n)
INTEGER2 `DataName`*	SMALLINT
INTEGER `DataName`	INTEGER
REAL `DataName`	REAL
REAL4 `DataName`*	REAL
DOUBLE PRECISION `DataName`	FLOAT
REAL8 `DataName`*	FLOAT

Table 11-4 GENPLAN WITH Clause Data Types — C

C Host Variable Data Type Declaration	GENPLAN WITH Clause 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.
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.
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

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

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], *lt;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

Example of GENPLAN for a MODULE SECTION.

GENPLAN FOR MODULE SECTION MyModule(10);