Cannot push down string functions with string units on Db2 remote data source in Data Virtualization

Last updated: Nov 26, 2024

You can use string functions with string units from a Db2 remote data source; however, the string function is not pushed down on the Db2 remote data source.

Symptoms

You use string functions with string units in a query and the string function is not pushed down on the Db2 remote data source.

For example

select SUBSTRING (col13_char, 1, 2,OCTETS) from n1

Resolving the problem

To solve this issue, you must set the SAME_CODESET and STRING_UNITS options. The SAME_CODESET option provides a way to set the SAME_CODESET server attribute externally. The STRING_UNITS option provides a way to set the STRING_UNITS server attribute externally. It is the setting of string_units for Db2 remote data source.

Get the cid from the dvsys.rdb_connections table.
```
select cid, url from dvsys.rdb_connections;
```
Check whether the codeset between the Data Virtualization local database and Db2 remote data source is the same.
Run the following command in both the Data Virtualization local database and the Db2 remote data source to get the codeset setting.
```
db2 get db cfg for <dbname> | grep "Database code set"
```
Where <dbname> is the database name and "Database code set" = UTF-8.

If the setting is the same, you can set SAME_CODESET to Y.
Run the following command in Db2 remote data source to find the string units setting.
```
db2 get db cfg for <dbname> | grep "STRING_UNITS"
```
Where <dbname> is the database name and the default string unit ("STRING_UNITS") is set to SYSTEM.

If the setting is SYSTEM, set STRING_UNITS to S. If the setting is CODEUNITS32, set STRING_UNITS to C.
Run the following commands to specify the SAME_CODESET and STRING_UNITS option. Use the <cid> value that you obtained from step 1 and the value for SAME_CODESET that you determined in step 2 and the value for STRING_UNITS that you determined in step 3.
```
alter server qplex options(set <cid>@same_codeset 'Y')
```
```
alter server qplex options(set <cid>@string_units 'S')
```
Or
```
 alter server qplex options(set <cid>@same_codeset 'N')
```
```
 alter server qplex options(set <cid>@string_units 'C')
```

For more information, see Character strings in Db2 documentation.