All DB2 programmers should know that host variables used in SQL statements in a program should be preceded by a
. So, if a host variable is named
it should be coded in the SQL statement as
. However, most programmers do not know that through V5, DB2 programs
host variables that are not preceded by a colon. DB2 will spit out a warning message, but will process the SQL containing the offending host variable. This "feature" is no longer supported as of DB2 V6.
The reason IBM eliminated this feature is the rising complexity of SQL. It is getting too difficult for DB2 to differentiate host variables from SQL when it parses the SQL to be prepared for execution. With all of the new features being added to DB2, the
complexity of the SQL language will continue unabated. As such, for DB2 V6 and onward, all host variables must be prefixed with a colon, or the statement will fail to execute.
This change should not impact many programs because most organizations have DB2 standards that
all host variables must begin with a colon. However, because DB2 has tolerated host variables without a colon for many
(through DB2 V5), you should inspect all DB2 SQL statements in application programs to ensure compliance prior to migrating to DB2 V6.
This is the most difficult problem to find and fix as a result of moving to DB2 Version 6. If you do not fix the problem prior to migrating to V6, any programs containing offending host variables will fail the
time they are rebound.