Host variables

Host variables are defined directly by statements of the host language or indirectly by SQL extensions. A host-variable in an SQL statement must identify a host variable that is described in the program according to the rules for declaring host variables. Host variables cannot be referenced in dynamic SQL statements; parameter markers must be used instead.

A host variable is either of these items that is referred to in an SQL statement:

  • A variable in a host language such as a PL/I variable, C variable, Fortran variable, REXX variable, Java™ variable, COBOL data item, or Assembler language storage area
  • A host language construct that was generated by an SQL precompiler from a variable declared using SQL extensions

Host variables are defined directly by statements in the host language or indirectly by SQL extensions as described in Db2 object relational extensions. Host variables cannot be referenced in dynamic SQL statements; parameter markers must be used instead. For more information about parameter markers, see Variables in dynamic SQL.

A host-variable in an SQL statement must identify a host variable that is described in the program according to the rules for declaring host variables.

In PL/I, C, and COBOL, host variables can be referred to in ways that do not apply to Fortran and Assembler language. This is explained in Host structures in PL/I, C, and COBOL. The following applies to all host languages.

The term host-variable, as used in the syntax diagrams, shows a reference to a host variable. In a SET host variable statement and the INTO clause of a FETCH, SELECT INTO, or VALUES INTO statement, a host variable is an output variable to which a value is assigned by Db2. In a CALL statement, a host variable can be an output argument that is assigned a value after execution of the procedure, an input argument that provides an input value for the procedure, or both an input and output argument. In all other contexts, a host variable is an input variable which provides a value to Db2.

Non-Java variable references
The general form of a host variable reference in all languages other than Java is:
Read syntax diagramSkip visual syntax diagram: host-identifierINDICATOR: host-identifier

Start of changeEach host identifier must be declared in the source program, except in a program written in REXX. The first host-identifier designates the main variable, and the second host-identifier designates the associated indicator variable. An indicator variable must be a small integer. Depending on the operation, the main variable either provides a value to the database manager or is provided a value from the database manager. An input host variable provides a value in the runtime application code page. An output host variable is provided a value that, if necessary, is converted to the runtime application code page when the data is copied to the output application variable. A given host variable can serve as both an input and an output variable in the same program.End of change

The purposes of an indicator variable are to:

  • Specify a non-null value. A 0 (zero), or positive value of the indicator variable specifies that the associated, first host-identifier provides the value of this host variable reference.
  • Specify the null value. A negative value of the indicator variable specifies the null value.

In addition, on output, the purposes of an indicator variable are to:

  • Indicate that a numeric conversion error (such as a divide by 0 or overflow) has occurred. A value of -2 for the indicator variable indicates a null result because of either numeric truncation or arithmetic warnings.
  • Indicate that a character could not be converted. A value of -2 for the indicator variable indicates a null result because of character string conversion warnings.
  • Indicate that no value was returned. A value of -3 for the indicator variable indicates a null result because the current row of the cursor is on a hole that is detected during a multiple row FETCH.
  • Report the original length of a truncated string, if the string is not a LOB.
  • Report the seconds portion of a time if the time is truncated on assignment to a host variable.

If the second host-identifier is omitted, the host variable does not have an indicator variable. The value specified by the host variable V1 is always the value of V1, and null values cannot be assigned to the variable. Thus, do not use this form on output unless the corresponding result column cannot contain null values. If this form is used for an output host variable, and the returned value is null, Db2 returns an error at run time.

Start of changeAn indicator variable for an input host variable in an INSERT, MERGE, or UPDATE statement can also be set to some special values to indicate that a target column is to be set to the default value, or to indicate that a value should not be assigned to a target column. These special values are only allowed when extended indicators are enabled. Extended indicators are enabled when the EXTENDEDINDICATOR(YES) bind option is used, or when the WITH EXTENDED INDICATORS prepare attribute is specified for the statement.End of change

Start of changeWhen extended indicators are enabled, an input indicator variable for an assignment in an INSERT, MERGE, or UPDATE statement specifies that the target column for the associated host variable is assigned one of the following types of values:End of change

  • A non-null value: A 0 (zero), or positive value specifies that the associated host-identifier provides the value of this host variable reference.
  • NULL: A -1, -2, -3, -4, or -6 value specifies the null value.
  • DEFAULT: A -5 value specifies the default value of the target column.
  • UNASSIGNED: A -7 value specifies that this host variable is treated as if a value was not specified for assignment to the target column in the statement.

Start of changeThese indicator values can also be used in indicator structures with host structures. When extended indicators are enabled, indicator values other than positive values, zero, and the negative values listed previously must not be used. The DEFAULT and UNASSIGNED extended indicator values must only be used in contexts where they are supported (INSERT, MERGE, and UPDATE statements). The DEFAULT and UNASSIGNED extended indicator values can only be used for an expression containing a single host parameter or a CAST of a single host parameter that is being assigned to a column. Output indicators are never set to extended indicator values. End of change

Start of changeWhen extended indicators are enabled, rules for data type validation in assignment and comparison are loosened for a host variable for which the associated indicator value is negative. Data type assignment and comparison validation rules are not enforced for a host variable for which the associated indicator value is NULL, DEFAULT, or ASSIGNED. End of change

Start of changeAn SQL statement that references host variables must be within the scope of the declaration of those host variables. For host variables referenced in the SELECT statement of a cursor, that rule applies to the OPEN statement rather than to the DECLARE CURSOR statement.End of change

Start of changeThe CCSID of a string host variable is one of the following values:End of change

Start of change
  • If a DECLARE VARIABLE statement with a CCSID clause for the host variable is specified, the CCSID of the host variable is the CCSID in the DECLARE VARIABLE statement.
  • If a DECLARE VARIABLE statement with a CCSID clause is not specified for the host variable, the host variable CCSID is the default CCSID of the application requester at the time that the SQL statement that contains the host variable is executed, unless the CCSID is for a foreign encoding scheme. In that case, the host variable is converted to the default CCSID of the current server.
End of change

All references to host variables must be preceded by a colon. If an SQL statement references a host variable without a preceding colon, the precompiler issues an error for the missing colon or interprets the host variable as an unqualified column name, which might lead to unintended results. The interpretation of a host variable without a colon as a column name occurs when the host variable is referenced in a context in which a column name can also be referenced.

Java variable references
The general form of a host variable reference in Java is:
Read syntax diagramSkip visual syntax diagram:INOUTINOUTJava-identifier( Java-expression)INDICATOR:Java-identifier

Each Java-identifier must be declared in the source program. The variable designated by the second Java-identifier is called an indicator variable and must be a short.

In Java, indicator variables are not always needed. Instead, instances of a Java class can be set to a null value. Variables defined as Java primitive types can not be set to a null value. When using an extended indicator variable, or when using a Java primitive type in assigning a null value or where the Java primitive type might be assigned null on output, indicator variables must be used.

If IN, OUT, or INOUT is not specified, the default depends on the context in which the variable is used. If the Java variable is used in an INTO clause, OUT is the default. Otherwise, IN is the default.