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 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 recognize 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.

  • Define the Host-variable name before you declare the cursor.
  • The value passed into Host-Variable should not cross 255 Bytes.
  • A proper declaration of Host-Variable is needed before using it in the SQL query.
  • The Data type of Host-variable passed CALL statement is not a CHAR or VARCHAR.
  • When you use STDSQL(YES) option for pre-compilation, you must declare Host-Variables in BEGIN/END sections.

Related Posts

Author: Srini

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