Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2010
    Posts
    5

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

    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 =>

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2010
    Posts
    5
    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".

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Oracle compatibility is limited in DB2 Express-C. You need at least Workgroup Server Edition to get the full range of compatibility features.

  5. #5
    Join Date
    Jul 2009
    Posts
    150

    Lightbulb

    Quote Originally Posted by rick_swiss View Post
    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

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Wink DB2 made easy, again

    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

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation Performance

    Thinking about performance, you can use following query:

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

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by rick_swiss View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Apr 2010
    Posts
    5
    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.

  10. #10
    Join Date
    Apr 2010
    Posts
    5
    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

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb DB2 has the same problem

    Quote Originally Posted by rick_swiss View Post
    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

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Lenny77 View Post
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by Marcus_A View Post
    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

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Lenny77 View Post
    No, sir ! There is the same problem in DB2 exist. I think, this is common problem for all data bases.

    Lenny
    What problem are you talking about?

    'ABC ' = 'ABC' sounds good to me!!! And it sounds good to every programmer I ever met.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •