ALLBASE/SQL Reference Manual (36216-90216)

430 Chapter11
SQL Statements E - R
GENPLAN
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)