Host-variable arrays in PL/I

In PL/I programs, you can specify numeric, character, graphic, binary, LOB, XML, and ROWID host-variable arrays. You can also specify LOB locators and LOB and XML file reference variables.

Host-variable arrays can be referenced only as a simple reference in the following contexts. In syntax diagrams, host-variable-array designates a reference to a host-variable array.

  • In a FETCH statement for a multiple-row fetch. See FETCH statement.
  • In the FOR n ROWS form of the INSERT statement with a host-variable array for the source data. See INSERT statement.
  • In a MERGE statement with multiple rows of source data. See MERGE statement.
  • In an EXECUTE statement to provide a value for a parameter marker in a dynamic FOR n ROWS form of the INSERT statement or a MERGE statement. See EXECUTE statement.

Start of changeIf an application references a host-variable array in other contexts that are not supported, Db2 returns the SQLCODE -312 error.End of change

Restrictions:
  • Only some of the valid PL/I declarations are valid host variable declarations. The precompiler uses the data attribute defaults that are specified in the PL/I DEFAULT statement. If the declaration for a host variable is not valid, any SQL statement that references the host-variable array might result in the message UNDECLARED HOST VARIABLE ARRAY.
  • The alignment, scope, and storage attributes of host-variable arrays have the following restrictions:
    • A declaration with the EXTERNAL scope attribute and the STATIC storage attribute must also have the INITIAL storage attribute.
    • If you use the BASED storage attribute, you must follow it with a PL/I element-locator-expression.
    • Host variables can be STATIC, CONTROLLED, BASED, or AUTOMATIC storage class, or options. However, CICS® requires that programs be reentrant.
    Although the precompiler uses only the names and data attributes of variable arrays and ignores the alignment, scope, and storage attributes, you should not ignore these restrictions. If you do ignore them, you might have problems compiling the PL/I source code that the precompiler generates.
  • You must specify the ALIGNED attribute when you declare varying-length character arrays or varying-length graphic arrays that are to be used in multiple-row INSERT and FETCH statements.

Numeric host-variable arrays

The following diagram shows the syntax for declaring numeric host-variable arrays.

Read syntax diagramSkip visual syntax diagramDECLAREDCLvariable-name(,variable-name)(dimension)(,variable-name(dimension1))BINARYBINDECIMALDECFIXED(precision, scale2)FLOAT(precision)3Alignment and/or Scope and/or Storage
Notes:
  • 1 dimension must be an integer constant in the range 1–32767.
  • 2 You can specify the scale for only DECIMAL FIXED.
  • 3 You can specify host-variable array attributes in any order that is acceptable to PL/I. For example, BIN FIXED(31), BINARY FIXED(31), BIN(31) FIXED, and FIXED BIN(31) are all acceptable.

Example

The following example shows a declaration of an indicator array. 
DCL IND_ARRAY(100) BIN FIXED(15);   /* DCL ARRAY of 100 indicator variables */

To use the PL/I decimal floating-point host data types, you need to use the FLOAT(DFP) and ARCH(7) compiler options and the Db2 coprocessor. The maximum precision for extended DECIMAL FLOAT will be 34 (not 33 as it is for hexadecimal float). The maximum precision for short DECIMAL FLOAT will be 7 (not 6 as it is for hexadecimal float).

Character host-variable arrays

The following diagram shows the syntax for declaring character host-variable arrays other than CLOBs.

Read syntax diagramSkip visual syntax diagramDECLAREDCLvariable-name(,variable-name)(dimension)(,variable-name(dimension1)) CHARACTERCHAR(length)VARYINGVAR Alignment and/or Scope and/or Storage
Notes:
  • 1 dimension must be an integer constant in the range 1–32767.

Example

The following example shows the declarations needed to retrieve 10 rows of the department number and name from the department table: 
DCL DEPTNO(10)    CHAR(3);                     /* Array of ten CHAR(3) variables */
DCL DEPTNAME(10)  CHAR(29) VAR;            /* Array of ten VARCHAR(29) variables */

Graphic host-variable arrays

The following diagram shows the syntax for declaring graphic host-variable arrays other than DBCLOBs.

Read syntax diagramSkip visual syntax diagramDECLAREDCLvariable-name(,variable-name)(dimension)(,variable-name(dimension1)) GRAPHIC(length)VARYINGVAR Alignment and/or Scope and/or Storage
Notes:
  • 1 dimension must be an integer constant in the range 1–32767.

Binary host-variable arrays

The following diagram shows the syntax for declaring binary variable arrays.

Read syntax diagramSkip visual syntax diagramDCLDECLAREvariable-name(,variable-name)(dimension)(,variable-name(dimension))SQL TYPE IS BINARYVARBINARY

LOB, locator, and file reference variable arrays

The following diagram shows the syntax for declaring BLOB, CLOB, and DBCLOB host variable, locator, and file reference variable arrays.

Read syntax diagramSkip visual syntax diagramDCL DECLARE variable-name(,variable-name)(dimension)(,variable-name(dimension1))SQL TYPE ISBINARY LARGE OBJECTBLOBCHARACTER LARGE OBJECTCHAR LARGE OBJECTCLOBDBCLOB(lengthKMG)BLOB_LOCATORCLOB_LOCATORDBCLOB_LOCATORBLOB_FILECLOB_FILEDBCLOB_FILE
Notes:
  • 1 dimension must be an integer constant in the range 1–32767.

XML host and file reference variable arrays

The following diagram shows the syntax for declaring BLOB, CLOB, and DBCLOB host-variable arrays and file reference variable arrays for XML data types.

Read syntax diagramSkip visual syntax diagramDCL DECLARE variable-name(,variable-name)(dimension)(,variable-name(dimension1))SQL TYPE IS XML ASBINARY LARGE OBJECTBLOBCHARACTER LARGE OBJECTCHAR LARGE OBJECTCLOBDBCLOB(lengthKMG)BLOB_FILECLOB_FILEDBCLOB_FILE
Notes:
  • 1 dimension must be an integer constant in the range 1–32767.

ROWID variable arrays

The following diagram shows the syntax for declaring ROWID variable arrays.

Read syntax diagramSkip visual syntax diagramDCLDECLAREvariable-name(,variable-name)(dimension)(,variable-name(dimension1))SQL TYPE ISROWID
Notes:
  • 1 dimension must be an integer constant in the range 1–32767.