GENPLAN

   isql=> SELECT * FROM SYSTEM.PLAN;

Scope

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
  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 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

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
   
   

3. Example of GENPLAN for a MODULE SECTION.

   
      GENPLAN FOR MODULE SECTION MyModule(10);