DB2-Undefined Unusable Host Variable

Recently, in DB2  (an excellent Book-you can buy on-line here),  I got an error in pre-compilation as ” Undefined or Unusable host variable”.

Many a times, while working with SQL, in Select or Declare cursor, especially in WHERE clause, the HOST VARIABLE data types should match the rules, else you will get an error (i.e DB2 will recognise, if host variable data-types are as per DCLGEN). Mostly used languages are COBOL/PLI.

DB2 tough interview questions e-book

DB2 Tough Interview Questions an e-book

DB2 is most popular database. You need a quick refresher to revise your DB2 knowledge before you attend an interview. This e-book helps you to refresh DB2 quickly.


In DB2, there are many rules to define a Host variable. I found some rules giving here for your ready reference.

  • If you use the STDSQL(YES) option for the precompilation, make sure that all of the host variable declarations are declared within BEGIN/END DECLARE SECTIONs.
  • If the cross-reference listing does not contain the host variable name, the declaration is missing. If the source for a declaration is present, a missing terminator from a comment or from a string might cause this type of error. The margins could also cause this type of error.
  • If the name is present, but the data type is not listed, the host variable is not usable; it does not follow the rules outlined for valid host variable definitions.
  • If several definitions exist for the name, the first reference might occur before the definition. The use of the TWOPASS option helps by processing DECLARE statements before the other types of statements. However, host variable declarations and cursor declarations are processed in the same pass. The host variables referenced in cursor declarations must be defined before the cursor is defined in the application program.
  • A parameter marker is expected rather than a host variable in an EXECUTE IMMEDIATE statement.
  • The data type of a host variable passed in a CALL statement is not character or varying character.
  • The length attribute of a character or varying character host variable passed in a CALL statement exceeds 255



Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.