Writing an SQL Program › Host Variables › INCLUDE TABLE Directive

INCLUDE TABLE Directive

INCLUDE TABLE Statement

You can use the INCLUDE TABLE statement, a CA IDMS extension of the SQL standard, to define a host language data structure for table columns. INCLUDE TABLE is a precompiler directive that defines host variables for all columns of a table, view, table procedure, procedure or function, or for a subset of columns that you specify in the statement.

If INCLUDE TABLE falls within the scope of an SQL declare section, embedded SQL statements can reference the variables defined by the precompiler as host variables.

Statement Example

The following INCLUDE statement directs the precompiler to define host variables for the DIVISION table, which has columns DIV_CODE, DIV_NAME, and DIV_HEAD_ID:

WORKING-STORAGE SECTION.
 .
 .
 .
EXEC SQL
   INCLUDE TABLE DIVISION
END-EXEC.

Structure Example

When the precompiler processes the INCLUDE TABLE statement in the prior example, it defines this structure:

*EXEC SQL
*   INCLUDE TABLE DIVISION
*END-EXEC.
 01   DIVISION.
         03   DIV-CODE               PIC X(3).
         03   DIV-HEAD-ID            PIC S9(4) COMP.
         03   DIV-HEAD-ID-I          COMP PIC S9(8).
*                                    SQLIND.
         03   DIV-NAME.
               49   DIV-NAME-LEN     PIC S9(4) COMP.
               49   DIV-NAME-TEXT    PIC X(40).

INCLUDE Statement Options

You can use options on the INCLUDE statement to perform the following:

• Override the default element level
• Direct the precompiler not to group elements under a structure
• Specify the columns to be included
• Specify names for the generated record and element definitions
• Specify a prefix or suffix for an element name
• Direct the precompiler to generate a multiply-occurring array

Note: For more information about INCLUDE statement syntax and options, see the CA IDMS SQL Reference Guide.

Including an Array

You can use the INCLUDE statement to generate a host variable array by specifying the NUMBER OF ROWS parameter. A host variable array is used in bulk processing.

Note: For more information about bulk processing, see Bulk Processing.

Host Variable Array Structure

When the precompiler generates a host variable array, it creates a structure using three levels. In the next example, a structure has been generated by an INCLUDE TABLE statement with NUMBER OF ROWS = 100:

DIVISION.
    02   DIVISION-BULK OCCURS 100 TIMES.
         03   DIV-CODE               PIC X(3).
         03   DIV-HEAD-ID            PIC S9(4) COMP.
         03   DIV-HEAD-ID-I          COMP PIC S9(8).
*                                    SQLIND.
         03   DIV-NAME.
               49   DIV-NAME-LEN     PIC S9(4) COMP.
               49   DIV-NAME-TEXT    PIC X(40).

Usefulness of INCLUDE TABLE

The INCLUDE TABLE statement is a programming tool. It assures that host variable definitions correspond to current table column definitions in the dictionary: the data types are equivalent, and indicator variables are declared for all columns that allow null values.

When Not to Use INCLUDE TABLE

Using INCLUDE TABLE is not appropriate if:

• The program must conform to the SQL standard
• The host variable declaration is for temporary table columns