Using host variables in SQL statements

Use host variables in embedded SQL statements to represent a single value. Host variables are useful for storing retrieved data or for passing values that are to be assigned or used for comparisons.

When you use host variables, adhere to the following requirements:
  • You must declare the name of the host variable in the host program before you use it. Host variables follow the naming conventions of the host language.
  • You can use a host variable to represent a data value, but you cannot use it to represent a table, view, or column name. You can specify table, view, or column names at run time by using dynamic SQL.
  • To use a host variable in an SQL statement, you can specify any valid host variable name that is declared according to the rules of the host language.
  • A colon (:) must precede host variables that are used in SQL statements so that DB2® can distinguish a variable name from a column name. When host variables are used outside of SQL statements, do not precede them with a colon. PL/I programs have the following exceptions: If the SQL statement meets any of the following conditions, do not precede a host variable or host variable array in that statement with a colon:
    • The SQL statement is in a program that also contains a DECLARE VARIABLE statement.
    • The host variable is part of a string expression, but the host variable is not the only component of the string expression.
  • To optimize performance, make sure that the host language declaration maps as closely as possible to the data type of the associated data in the database.
  • For assignments and comparisons between a DB2 column and a host variable of a different data type or length, expect conversions to occur.