Trailing white space is being ignored by DB2 when doing WHERE comparisons

rick_swiss · #1 (**permalink**) 04-27-10, 03:38

Below is a clear example of the problem (see second SELECT on oracle versus DB2). There must be a db2 database parameter somewhere to control this but I can't seem to find it out on the web. I need DB2 to react like Oracle below, but now DB2 appears to automatically trim trailing white space when doing WHERE comparisons. (ie. I know about RTRIM and LTRIM functions but that really isn't a solution for us). Does anyone know of any DB2 database parameter or way to make this act like oracle?

Thanks!

Consider the following behaviour with oracle:

SQL> create table bla(RECID VARCHAR(255));

Table created.

SQL> insert into bla values('abc ');

1 row created.

SQL> select recid from bla where recid = 'abc';

no rows selected

SQL> select recid from bla where recid = 'abc ';

RECID
--------------------------------------------------------------------------------
abc

SQL> select LENGTH(recid) from bla where recid = 'abc ';

LENGTH(RECID)
-------------
4

Now with DB2:

db2 => create table bla(RECID VARCHAR(255))
DB20000I The SQL command completed successfully.
db2 => insert into bla values('abc ')
DB20000I The SQL command completed successfully.
db2 => select recid from bla where recid = 'abc'

RECID
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
abc

1 record(s) selected.

db2 => select recid from bla where recid = 'abc '

RECID
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
abc

1 record(s) selected.

db2 => select LENGTH(recid) from bla where recid = 'abc '

1
-----------
4

1 record(s) selected.

db2 =>

Marcus_A · #2 (**permalink**) 04-27-10, 04:58

As you noticed, DB2 does not work the same as Oracle in this regard. DB2 treats 'XX' the same as 'XX ' in a compare. DB2 makes life for everyone much easier because of this, unless you are doing a conversion from Oracle to DB2, or vise versa.

I am not sure about the Oracle compatibility mode when using DB2 9.7.1 as to whether it works the way you want it to. If you can't find this in the DB2 doc, I would contact IBM (PMR).

One other thing. In DB2, = '' is not a valid test for NULL. This is the same as = ' ' in DB2. But not sure about Oracle compatibility mode.

If you are using DB2 Express-C, I don't recall if all the Oracle compatibility mode features are available to you.

rick_swiss · #3 (**permalink**) 04-27-10, 05:33

Thanks for the reply...The oracle compatibility option seems promising...According to the docs this should work for DB2 v 9.7.x but seems not to work (my version is shown below). Any idea on what version I have to be on?

db2@chdsk-tafjdev1:~$ db2start
SQL1063N DB2START processing was successful.
db2@chdsk-tafjdev1:~$ db2set DB2_COMPATIBILITY_VECTOR=ORA

DBI1301E Invalid value.

Explanation:

The value specified for the registry variable is invalid.

User response:

Refer to the DB2 Information Center to determine the valid values for
the registry variable.

db2@chdsk-tafjdev1:~$ db2level
DB21085I Instance "db2" uses "32" bits and DB2 code release "SQL09070" with
level identifier "08010107".
Informational tokens are "DB2 v9.7.0.0", "s090521", "LINUXIA3297", and Fix Pack
"0".
Product is installed at "/home/db2/sqllib".

n_i · #4 (**permalink**) 04-27-10, 08:30

Oracle compatibility is limited in DB2 Express-C. You need at least Workgroup Server Edition to get the full range of compatibility features.

DB2Plus · #5 (**permalink**) 04-27-10, 08:31

Quote:

Originally Posted by rick_swiss

Below is a clear example of the problem (see second SELECT on oracle versus DB2). There must be a db2 database parameter somewhere to control this but I can't seem to find it out on the web. I need DB2 to react like Oracle below, but now DB2 appears to automatically trim trailing white space when doing WHERE comparisons. (ie. I know about RTRIM and LTRIM functions but that really isn't a solution for us). Does anyone know of any DB2 database parameter or way to make this act like oracle?

Thanks!

Consider the following behaviour with oracle:

SQL> create table bla(RECID VARCHAR(255));

Table created.

SQL> insert into bla values('abc ');

1 row created.

SQL> select recid from bla where recid = 'abc';

no rows selected

SQL> select recid from bla where recid = 'abc ';

RECID
--------------------------------------------------------------------------------
abc

SQL> select LENGTH(recid) from bla where recid = 'abc ';

LENGTH(RECID)
-------------
4

Now with DB2:

db2 => create table bla(RECID VARCHAR(255))
DB20000I The SQL command completed successfully.
db2 => insert into bla values('abc ')
DB20000I The SQL command completed successfully.
db2 => select recid from bla where recid = 'abc'

RECID
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
abc

1 record(s) selected.

db2 => select recid from bla where recid = 'abc '

RECID
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
abc

1 record(s) selected.

db2 => select LENGTH(recid) from bla where recid = 'abc '

1
-----------
4

1 record(s) selected.

db2 =>

Spaces did not trim from trail of the righ side of equaty, but DB2 added spaces on the left side of conditition (if length of the left is not equal length of the right), how I know.

Kara

Lenny77 · #6 (**permalink**) 04-27-10, 10:46

You can solve your problem very easy, as usual:

Instead of:

Code:

select recid from bla where recid = 'abc '

You have to use:

Code:

select recid from bla where recid || '*' = 'abc ' || '*'

Lenny

Lenny77 · #7 (**permalink**) 04-27-10, 12:30

Thinking about performance, you can use following query:

Code:

select recid from bla 
where recid = 'abc' and length(recid) = length('abc');

Lenny

Marcus_A · #8 (**permalink**) 04-27-10, 15:22

Quote:

Originally Posted by rick_swiss

Thanks for the reply...The oracle compatibility option seems promising...According to the docs this should work for DB2 v 9.7.x but seems not to work (my version is shown below). Any idea on what version I have to be on?

db2@chdsk-tafjdev1:~$ db2level
DB21085I Instance "db2" uses "32" bits and DB2 code release "SQL09070" with
level identifier "08010107".
Informational tokens are "DB2 v9.7.0.0", "s090521", "LINUXIA3297", and Fix Pack
"0".
Product is installed at "/home/db2/sqllib".

You should definitely upgrade to fixpack 1, since Oracle compatibility was enhanced in that fixpack. However, if you are using DB2 Express-C, not sure if that is available.

Fixpack 2 will be out shortly, and would adivse anyone using 9.7 to apply it.

rick_swiss · #9 (**permalink**) 04-29-10, 08:14

Lenny, it isn't that simple for a variety of reasons that have to do with our internal software. Nonetheless we might have to resort to that if the Oracle Compatibility features don't do the job.

rick_swiss · #10 (**permalink**) 04-29-10, 09:31

Just confirmed that using Oracle Compatibility mode does solve this issue with version 9.7.0 Workgroup Server Edition...The steps are:

db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop
db2start

create your database...Seems that after creating the database it stays in this mode even after you do a db2set DB2_COMPATIBILITY_VECTOR= . So that's nice...More info here:

IBM DB2 9.7 for Linux, UNIX and Windows Information Center

Marcus_A · #11 (**permalink**) 04-29-10, 09:47

If you are still on 9.7.0 (fixpack 0), you definitely need to upgrade to fixpack 1 which has improved Oracle compatibility, plus some major bug fixes.

Lenny77 · #12 (**permalink**) 04-29-10, 10:01

Quote:

Originally Posted by rick_swiss

Lenny, it isn't that simple for a variety of reasons that have to do with our internal software. Nonetheless we might have to resort to that if the Oracle Compatibility features don't do the job.

I don't know about Oracle's problem, but the same problem exists in DB2 and I shown to you ways to solve this problem.

Lenny

Marcus_A · #13 (**permalink**) 04-29-10, 10:39

Quote:

Originally Posted by Lenny77

I don't know about Oracle's problem, but the same problem exists in DB2 and I shown to you ways to solve this problem

I don't think that either Oracle or DB2 have a "problem," they just work differently. I definitely prefer the way DB2 handles trailing spaces, which I believe is the ANSI standard (Oracle gave up on ANSI standards long ago).

Lenny77 · #14 (**permalink**) 04-29-10, 11:02

Quote:

Originally Posted by Marcus_A

I don't think that either Oracle or DB2 have a "problem," they just work differently. I definitely prefer the way DB2 handles trailing spaces, which I believe is the ANSI standard (Oracle gave up on ANSI standards long ago).

No, sir ! There is the same problem in DB2 exist. I think, this is common problem for all data bases.

Lenny