SQL and Host Variables - Preprocessor2 for Embedded SQL

SQL and Host Variables

BEGIN DECLARE and END DECLARE are documented in SQL Stored Procedures and Embedded SQL (B035‑1148).

Code a SQL string as a varying character field. If using the SQL string as the database name variable for a SQL DATABASE statement, or as the checkpoint label variable for a SQL CHECKPOINT statement, name the level 49 fields. Do not refer to them as FILLER.

For LPI COBOL, name the first level 49 field if the varying character field is defined within a structure.

In all other cases, coding the level 49 fields as FILLER is valid. Use FILLER only where reference to the individual portions of the varying character field is not necessary.

An OCCURS clause in the definition of a SQL string is valid only when a REDEFINES clause precedes the OCCURS clause.

Restrictions on variable type do not apply.

The REDEFINES and the OCCURS clause can appear anywhere in the SQL string. The REDEFINES clause causes the remaining portion of the 49 level definition to be ignored.

Individual level 49 fields (LENGTH1, LENREDEF, STRING1 in the following example) are not valid as separate entities in SQL requests.

01 WS-SQL-STRING.

   49 LENGTH1  PIC S9(4) <comp>.

   49 LENREDEF REDEFINES LENGTH1 PIC X OCCURS 2 TIMES.

   49 STRING1  PIC X(5).

   49 STRING2  PIC X(5).

   49 STRING3  PIC X(5).

   49 STRING4  PIC X(5).

   49 STRING5  PIC X(5).

   49 STRING6  PIC X(5).

   49 NAME REDEFINES STRING6 PIC X OCCURS 5 TIMES.

   49 FILLER   PIC X(100).

where <comp> is COMP-5 for MF COBOL and COMP for all other COBOL compilers.

PP2 ignores the REDEFINES clause.

Referencing the variable in a SQL statement is valid, provided the definition for the variable is valid for PP2.

In the example below, STRUCTURE1 and FIELD1 could not be used in a SQL request (FIELD1 has an invalid PP2 definition; STRUCTURE1 is invalid because a subfield in the structure is invalid).

FIELD2 could be used in a SQL request.

01 STRUCTURE1.

   02 FIELD1 PIC -(15)9.

   02 FIELD2 REDEFINES FIELD1 PIC X(16).

Code a varying character field as a structure consisting of a structure name followed by at least two level 49 entries.

The first entry must conform to a signed or unsigned small integer type (PIC S9(4) <comp> or PIC 9(4) <comp>).

The second and subsequent level 49 fields must be of character type (PIC X(n)).

For example, to declare a varying character field of maximum length 255, code the variable as:

01 VARCHAR1.

   49 FILLER  PIC S9(4) <comp> VALUE +255.

   49 FILLER  PIC X(255).

or

01 VARCHAR1.

   49 FILLER  PIC S9(4) <comp> VALUE +255.

   49 FILLER  PIC X(100).

   49 FILLER  PIC X(100).

   49 FILLER  PIC X(55).

where <comp> is COMP-5 for MF COBOL and COMP for all other COBOL compilers.

The use of FILLER and multiple level 49 entries is an extension over DB2 provisions.

All DB2 conforming declarations are valid for PP2.

PP2 accepts the OCCURS clause only to define an indicator array. See “Indicator Variables” on page 171.

The SYNCHRONIZED clause is ignored by PP2.

The variable can be referenced in a SQL statement, provided the definition for the variable is valid to PP2.

PP2 ignores the VALUE clause.

Reference the variable in a SQL statement, provided the definition for the variable is valid to PP2.