Host variables in PL/I

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

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 variable might result in the message UNDECLARED HOST VARIABLE.
  • The alignment, scope, and storage attributes of host variables 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 variables 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.
  • PL/I supports some data types with no SQL equivalent (COMPLEX and BIT variables, for example). In most cases, you can use PL/I statements to convert between the unsupported PL/I data types and the data types that SQL supports.
  • You can not use locators as column types.
    The following locator data types are PL/I data types as well as SQL data types:
    • Result set locator
    • Table locator
    • LOB locators
  • The precompiler does not support PL/I scoping rules.
Recommendations:
  • Be careful of overflow. For example, if you retrieve an INTEGER column value into a BIN FIXED(15) host variable and the column value is larger than 32767 or smaller than -32768, you get an overflow warning or an error, depending on whether you provided an indicator variable.
  • Be careful of truncation. For example, if you retrieve an 80-character CHAR column value into a CHAR(70) host variable, the rightmost ten characters of the retrieved string are truncated. Retrieving a double-precision floating-point or decimal column value into a BIN FIXED(31) host variable removes any fractional part of the value. Similarly, retrieving a column value with a DECIMAL data type into a PL/I decimal variable with a lower precision might truncate the value.

Numeric host variables

You can specify the following forms of numeric host variables:

  • Floating-point numbers (Hexadecimal and Decimal)
  • Integers and small integers
  • Decimal numbers

The following diagram shows the syntax for declaring numeric host variables.

Read syntax diagramSkip visual syntax diagramDECLAREDCLvariable-name(,variable-name)BINARYBINDECIMALDECFIXED(precision, scale1)FLOAT(precision)23 Alignment and/or Scope and/or Storage
Notes:
  • 1 You can specify a scale only for DECIMAL FIXED.
  • 2 You can specify host variable 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.
  • 3 The UNSIGNED attribute must not be specified with BINARY and FIXED for a numeric host variable declaration.

For binary floating-point or hexadecimal floating-point data types, use the FLOAT SQL processing option to specify whether the host variable is in IEEE binary floating-point or z/Architecture® hexadecimal floating-point format. Db2 does not check if the format of the host variable contents match the format that you specified with the FLOAT SQL processing option. Therefore, you need to ensure that your floating-point host variable contents match the format that you specified with the FLOAT SQL processing option. Db2 converts all floating-point input data to z/Architecture hexadecimal floating-point format before storing it.

If the PL/I compiler that you are using does not support a decimal data type with a precision greater than 15, use one of the following variable types for decimal data:
  • Decimal variables with precision less than or equal to 15, if the actual data values fit. If you retrieve a decimal value into a decimal variable with a scale that is less than the source column in the database, the fractional part of the value might truncate.
  • An integer or a floating-point variable, which converts the value. If you use an integer variable, you lose the fractional part of the number. If the decimal number can exceed the maximum value for an integer or you want to preserve a fractional value, use a floating-point variable. Floating-point numbers are approximations of real numbers. Therefore, when you assign a decimal number to a floating-point variable, the result might be different from the original number.
  • A character string host variable. Use the CHAR function to retrieve a decimal value into it.

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 variables

You can specify the following forms of character host variables:

  • Fixed-length strings
  • Varying-length strings
  • CLOBs

The following diagram shows the syntax for declaring character host variables, other than CLOBs.

Read syntax diagramSkip visual syntax diagramDECLAREDCLvariable-name(,variable-name) CHARACTERCHAR (length) VARYINGVAR Alignment and/or Scope and/or Storage

Graphic host variables

You can specify the following forms of character host variables:

  • Fixed-length strings
  • Varying-length strings
  • DBCLOBs

The following diagram shows the syntax for declaring graphic host variables other than DBCLOBs.

Read syntax diagramSkip visual syntax diagramDECLAREDCLvariable-name(,variable-name) GRAPHICWIDECHAR1 (length) VARYINGVAR Alignment and/or Scope and/or Storage
Notes:
  • 1 Use WIDECHAR only for UNICODE UTF-16 data. WIDECHAR is supported only by the Db2 coprocessor.

Binary host variables

You can specify the following forms of binary host variables:

  • Fixed-length strings
  • Varying-length strings
  • BLOBs

The following diagram shows the syntax for declaring BINARY host variables.

Read syntax diagramSkip visual syntax diagramDECLAREDCLvariable-name,(variable-name)SQL TYPE IS BINARYVARBINARYBINARY VARYING ( length1 )  ; 
Notes:
  • 1 For BINARY host variables, the length must be in the range 1–255. For VARBINARY host variables, the length must be in the range 1–32704.

PL/I does not have variables that correspond to the SQL binary data types BINARY and VARBINARY. To create host variables that can be used with these data types, use the SQL TYPE IS clause.

When you reference a BINARY or VARBINARY host variable in an SQL statement, you must use the variable that you specify in the SQL TYPE declaration. When you reference the host variable in a host language statement, you must use the variable that Db2 generates.

Examples of binary variable declarations

The following table shows examples of variables that Db2 generates when you declare binary host variables.
Table 1. Examples of BINARY and VARBINARY variable declarations for PL/I
Variable declaration that you include in your PL/I program Corresponding variable that Db2 generates in the output source member 
DCL BIN_VAR SQL TYPE IS BINARY(10);
 
DCL BIN_VAR CHAR(10);
 
DCL VBIN_VAR SQL TYPE IS VARBINARY(10);
 
DCL VBIN_VAR CHAR(10) VAR;

Result set locators

The following diagram shows the syntax for declaring result set locators.

Read syntax diagramSkip visual syntax diagramDECLAREDCLvariable-name(,variable-name)SQL TYPE ISRESULT_SET_LOCATORVARYING Alignment and/or Scope and/or Storage

Table locators

The following diagram shows the syntax for declaring table locators.

Read syntax diagramSkip visual syntax diagramDCLDECLAREvariable-name(,variable-name)SQL TYPE ISTABLE LIKEtable-name AS LOCATOR

LOB variables, locators, and file reference variables

The following diagram shows the syntax for declaring BLOB, CLOB, and DBCLOB host variables, locators, and file reference variables.

Read syntax diagramSkip visual syntax diagramDCL DECLARE 1variable-name (,variable-name)SQL TYPE ISBINARY LARGE OBJECTBLOBCHARACTER LARGE OBJECTCHAR LARGE OBJECTCLOBDBCLOB(lengthKMG)BLOB_LOCATORCLOB_LOCATORDBCLOB_LOCATORBLOB_FILECLOB_FILEDBCLOB_FILE2
Notes:
  • 1 A single PL/I declaration that contains a LOB variable declaration is limited to no more than 1000 lines of source code.
  • 2 Variable attributes such as STATIC and AUTOMATIC are ignored if specified on a LOB variable declaration.
Note: Variable attributes such as STATIC and AUTOMATIC are ignored if specified on a LOB variable declaration.

XML data host and file reference variables

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

Read syntax diagramSkip visual syntax diagramDCL DECLARE variable-name (,variable-name)SQL TYPE IS XML ASBINARY LARGE OBJECTBLOBCHARACTER LARGE OBJECTCHAR LARGE OBJECTCLOBDBCLOB(lengthKMG)BLOB_FILECLOB_FILEDBCLOB_FILE

ROWID host variables

The following diagram shows the syntax for declaring ROWID host variables.

Read syntax diagramSkip visual syntax diagramDCLDECLAREvariable-name(,variable-name)SQL TYPE ISROWID