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.

  • Define Host-variable name before you declare the cursor.
  • The value passed into Host-Variable should not cross 255 Bytes.
  • Proper declaration of Host-Vatiable is needed before using it in 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.