February 11, 2014

LIKE Predicate Enhanced in DB2 11

Users of DB2 Version 10 and earlier releases may find inconsistent behavior when using the LIKE predicate, particularly if the column contains trailing blanks and is of variable length.

However, DB2 11 brings a new subsystem parameter: LIKE_BLANK_INSIGNIFICANT. This enhances the LIKE predicate by instituting a behavior that ignores the blanks at the end of fixed-length strings. Now, when installing or migrating to a DB2 11 system, the default setting is LIKE_BLANK_SIGNIFICANT. As its name implies, this treats the blanks at the end of fixed-length strings as significant (i.e., not ignored). But once you've loaded your new system or completed your migration, you should manually change the setting to take advantage of LIKE_BLANK_INSIGNIFICANT.

There is one note of caution with this new subsystem parameter. Existing check constraints containing the LIKE predicate may not conform to the table check constraint. Consider running the CHECK DATA utility on all affected tables to find the records that don't conform to the table check constraint.