Oracle compatibility in DB2: Some strings attached

One of the great features that came with DB2 9.7 is the so-called SQL compatibility. That is, DB2 supports a bigger part Oracle's SQL syntax and the related semantics. This is great when you want to "break free", i.e., move your applications from Oracle to DB2. Many DB2 developers have also looked into what of the compatibility features to use for their systems.

In this post, I want to point out the differences in string semantics depending on whether you are in Oracle or DB2 mode. Let's take a look at the regular first:

db2 => create table foo(id varchar(20) unique not null, num int)
DB20000I  The SQL command completed successfully.
db2 => insert into foo values('abc',1)
DB20000I  The SQL command completed successfully.
db2 => insert into foo values('abc  ',2)
DB20000I  The SQL command completed successfully.

When you switch to the Oracle mode by setting the DB2_COMPATIBILITY_VECTOR, you implicitly also switch to VARCHAR2 semantics. By that different semantics are applied to strings, including removal of trailing blanks for comparisons and empty strings converted to NULLs. How does that look like for our example (after setting the vector, restarting DB2, creating a different database)?

db2 => create table foo(id varchar(20) unique not null, num int)
DB20000I  The SQL command completed successfully.
db2 => insert into foo values('abc',1)
DB20000I  The SQL command completed successfully.
db2 => insert into foo values('abc  ',2)
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0803N  One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "HLOESER.FOO" from having duplicate values for the index key. 
SQLSTATE=23505

Quite some different behavior than before and something to consider before enabling some of the compatibility features. For the XML crowd I want to point out, that XQuery string semantics are not impacted by the switch - they are different from both...

One day DB2 pureScale workshops in Ehningen (Stuttgart) and Paris coming up

Workshops covering DB2 pureScale are coming up on March 17th in Ehningen (Stuttgart area, Germany) and in Paris (France) on May 6th. They include presentations in the morning and hands-on training in the afternoon. For more information and to register, visit the DB2 pureScale workshop website.