Incorrect query results for Db2 remote data sources in Data Virtualization

Last updated: Nov 26, 2024

If you run queries that compare string data types against Db2® remote databases, which use the Db2 connection type that is not in VARCHAR2 compatibility mode, these queries might return incorrect results.

Symptoms

You run some queries that compare string data types (CHAR or VARCHAR) against Db2 remote databases that are not in VARCHAR2 compatibility mode and use the Db2 connection type. You get results that are different from what you get in a local database because the queries pushdown and follow the remote Db2 comparison rules.

For example,

select col_char from testable1 union select col_varchar from testable2;
select col_char from testable1 except select col_varchar from testable2;
select col_char from testable1 intersect select col_varchar from testable2;

You can check the configuration of the remote Db2 database by running the db2 get db cfg command. In the following example, the database is not in VARCHAR2 compatibility mode.

$ db2 get db cfg for testdb |grep "Varchar2 compatibility"
Varchar2 compatibility                                  = OFF

Resolving the problem

To work around this issue, modify the collating sequence server option.

Run the following statement to identify the internal Connection ID (CID) for your data source.
```
select * from DVSYS.LISTRDBC;
```
This query lists all of the connections in the cluster. Find the row in the output for the connection or connections that you want to change.
Run the following command to modify the COLLATING_SEQUENCE parameter of this connection to 'I' for each connection that you want to change.
```
ALTER SERVER QPLEX OPTIONS (add <CID>@collating_sequence 'I');
```
For example,
```
ALTER SERVER QPLEX OPTIONS (add DB210000@collating_sequence 'I');
```