Incorrect Results When Comparing Char Columns With Empty Literals in a Select From DB2 Using Gateway For DRDA (Doc ID 951792.1)

Last updated on JANUARY 19, 2017

Applies to:

Oracle Database Gateway for DRDA - Version 10.1.0.4 to 11.2.0.1 [Release 10.1 to 11.2]
Information in this document applies to any platform.

Symptoms

When using Transparent Gateway for DRDA (TG4DRDA) 10.1 or10.2 or  Database Gateway for DRDA (DG4DRDA) 11.1 and 11.2 to select from DB2 using a 10.1, 10.2, 11.1 or 11.2.0.1 RDBMS then queries that use empty literals in a where clause give incorrect results when selecting from CHAR columns.

If a where clause for a column is passed as -

where <col_name> = ''


- that is 2 quotes with no space then this is passed to DB2 as -

where <col_name> is null

 

If the where is passed as -

where <col_name> = ' ';


- that is with spaces between the quotes then this passed to DB2 as -

where <col_name> = ' '


with spaces and empty rows are returned.

When using a 9.2 RDBMS with the same gateways then the correct results are returned.

For example -

Table is created in DB2 as -

create table test_null (col1 char(5) not null, col2 char(5)) 

insert into test_null values ('Bill', 'row 1')
insert into test_null values (' ', '2 blnk')
insert into test_null values ('', 'null')
select * from test_null
COL1 COL2
Bill row 1
     2blnk
     null

select * from test_null where col1 is null
- no rows returned

select length(col1) from test_null

LENGTH ( COL1 )
           5
           5
           5

DB2 behaves differently from Oracle. An insert of a value defined as '' - that is with no spaces actually inserts spaces up to the column length defined for the column.

The same insert of  ''  in Oracle gives error -
ORA-01400: cannot insert NULL into table.column.name

RDBMS 9.2.0.8 and TG4DRDA 10.2 -

SQL> desc tg1020.test_null@gtw_1020
Name                                      Null?    Type
----------------------------------------- -------- ---------
COL1                                      NOT NULL CHAR(5)
COL2                                               CHAR(5)

SQL> select * from tg1020.test_null@gtw_1020 ;

COL1 COL2
----- -----
Bill row 1
     2blnk
     null

SQL> select * from tg1020.test_null@gtw_1020 where col1 ='' ;

no rows selected

SQL> select * from tg1020.test_null@gtw_1020 where col1 is null ;

no rows selected

SQL>

but using RDBMS 10.2 or above and TG4DRDA 10.2 -

SQL> desc tg1020.test_null@dejavu2
Name Null? Type
----------------------------------------- -------- ----------
COL1                                      NOT NULL CHAR(5)
COL2                                               CHAR(5)

SQL> select * from tg1020.test_null@dejavu2 ;

COL1  COL2
----- -----
Bill  row 1
      2blnk
      null

SQL> select * from tg1020.test_null@dejavu2 where col1 = '' ;

COL1 COL2
----- -----
      2blnk
      null

SQL> select * from tg1020.test_null@dejavu2 where col1 is null ;

no rows selected

SQL>




Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms