Co-worker Bob and I were surprised to find today that DB2 9.7 accepts SQLs like the following:

select count(*) from syscat.tables where tbspaceid = ‘0’

In all prior DB2 versions, comparing an integer column with a string constant (or anything similar) would yield the error:

SQL0401N  The data types of the operands for the operation <operator> are not compatible or comparable.

The source of this new flexibility is DB2 9.7’s implicit casting, yet another feature added in the quest for greater Oracle compatibility.  The DB2 documentation clumsily introduces it:

Version 9.7 introduces support for implicit casting. Implicit casting is the automatic conversion of data of one data type to data of another data type based on an implied set of conversion rules.

Automatic type conversions (yea, duck typing even) are a good thing, but require care when arriving late to an installed base.  We developers are now on DB2 9.7, but have to support DB2 versions as far back as 9.1.  It’s now too easy to write SQL that will “work on my machine” but not on the older versions of DB2 that our customers run.

Once everyone is at DB2 9.7 and higher, this will be a non-issue.  IBM’s usual policy with breaking DB2 compatibility is to provide registry settings or other means of disabling the change, often leaving it disabled by default for some time.  But that doesn’t seem to be the case here.  I couldn’t find a mechanism to turn off implicit casting for now; if you know of one, please pass it on.