Rational Developer for System z
PL/I for Windows, Version 8.0, Programming Guide

Determining equivalent SQL and PL/I data types

The base SQLTYPE and SQLLEN of host variables are determined according to the following table. If a host variable appears with an indicator variable, the SQLTYPE is the base SQLTYPE plus one.

Table 3. SQL data types generated from PL/I declarations
    PL/I Data Type SQLTYPE of Host Variable SQLLEN of Host Variable SQL Data Type
BIN FIXED(n), n < 16 500 2 SMALLINT
BIN FIXED(n), n ranges from 16 to 31 496 4 INTEGER
DEC FIXED(p,s)   484   p (byte 1) s (byte 2) DECIMAL(p,s)
BIN FLOAT(p), 22 <= p <= 53 480 8 FLOAT
DEC FLOAT(m), 7 <= m <= 16 480 8 FLOAT
CHAR(n), 1 <= n <= 254 452 n CHAR(n)
CHAR(n) VARYING, 1 <= n <= 4000 448 n VARCHAR(n)
CHAR(n) VARYING, n > 4000 456 n LONG VARCHAR
GRAPHIC(n), 1 <= n <= 127 468 n GRAPHIC(n)
GRAPHIC(n) VARYING, 1 <= n <= 2000 464 n VARGRAPHIC(n)
GRAPHIC(n) VARYING, n > 2000 472 n LONG VARGRAPHIC

Since SQL does not have single or extended precision floating-point data type, if a single or extended precision floating-point host variable is used to insert data, it is converted to a double precision floating-point temporary and the value in the temporary is inserted into the database. If the single or extended precision floating-point host variable is used to retrieve data, a double precision floating-point temporary is used to retrieve data from the database and the result in the temporary variable is assigned to the host variable.

The following table can be used to determine the PL/I data type that is equivalent to a given SQL data type.

Table 4. SQL data types mapped to PL/I declarations
SQL Data Type PL/I Equivalent Notes
SMALLINT BIN FIXED(15)
INTEGER BIN FIXED(31)
DECIMAL(p,s) DEC FIXED(p) or DEC FIXED(p,s) p = precision and s = scale; 1 <= p <= 31 and 0 <= s <= p
FLOAT BIN FLOAT(p) or DEC FLOAT(m) 22 <= p <= 53 7 <= m <= 16
CHAR(n) CHAR(n) 1 <= n <= 254
VARCHAR(n) CHAR(n) VAR 1 <= n <= 4000
LONG VARCHAR CHAR(n) VAR n > 4000
GRAPHIC(n) GRAPHIC(n) n is a positive integer from 1 to 127 that refers to the number of double-byte characters, not to the number of bytes
VARGRAPHIC(n) GRAPHIC(n) VAR n is a positive integer that refers to the number of double-byte characters, not to the number of bytes; 1 <= n <= 2000
LONG VARGRAPHIC GRAPHIC(n) VAR n > 2000
DATE CHAR(n) n must be at least 10
TIME CHAR(n) n must be at least 8
TIMESTAMP CHAR(n) n must be at least 26

Determining compatibility of SQL and PL/I data types

PL/I host variables in SQL statements must be type compatible with the columns which use them:

Using host structures

A PL/I host structure name can be a structure name with members that are not structures or unions. For example:

  dcl 1 A,
        2 B,
          3 C1 char(...),
          3 C2 char(...);

In this example, B is the name of a host structure consisting of the scalars C1 and C2.

Host structures are limited to two levels. A host structure can be thought of as a named collection of host variables.

Each leaf element of a host structure must have one of the following valid host data attributes as discussed in Declaring host variables:

Using indicator variables

An indicator variable is a 2-byte integer (BIN FIXED(15)). On retrieval, an indicator variable is used to show whether its associated host variable has been assigned a null value. On assignment to a column, a negative indicator variable is used to indicate that a null value should be assigned.

Indicator variables are declared in the same way as host variables and the declarations of the two can be mixed in any way that seems appropriate to the programmer.

The SQL Preprocessor does not require that indicator arrays have a lower bound of one.

An indicator variable must have the attribute REAL NATIVE SIGNED FIXED BIN(15).

Given the statement:

  exec sql fetch Cls_Cursor into :Cls_Cd,
                                 :Day :Day_Ind,
                                 :Bgn :Bgn_Ind,
                                 :End :End_Ind;

Variables can be declared as follows:

  exec sql begin declare section;
  dcl Cls_Cd    char(7);
  dcl Day       bin fixed(15);
  dcl Bgn       char(8);
  dcl End       char(8);
  dcl (Day_Ind, Bgn_Ind, End_Ind)   bin fixed(15);
  exec sql end declare section;

Host structure example

The following example shows the declaration of a host structure and an indicator array followed by an SQL statements that could be used to retrieve the data into the host structure.

  dcl 1 games,
        5 sunday,
          10 opponents char(30),
          10 gtime     char(10),
          10 tv        char(6),
          10 comments  char(120) var;
  dcl indicator(4) fixed bin (15);

  exec sql
       fetch cursor_a
       into :games.sunday:indicator;

Using statements and compile-time options

This topic describes the way the preprocessor handles statements and compile-time options with SQL statements.

CONNECT TO statement

You can use a host variable to represent the database name you want your application to connect to, for example:

  exec sql connect to :dbase;

If a host variable is specified:

DECLARE TABLE statement

The preprocessor ignores all DECLARE TABLE statements.

DECLARE STATEMENT statement

The preprocessor ignores all DECLARE STATEMENT statements.

Use of varying strings under DFT(EBCDIC)

If you specify the compile-time option DFT(EBCDIC) and you use a varying string host variable as input to the database, you must initialize the host variable or you might get a protection exception during the execution of your program.

If you use an uninitialized varying string on mainframe DB2, your program would be in error and might also get a protection exception.

Rational Developer for System z
PL/I for Windows, Version 8.0, Programming Guide