Cannot push down join views on remote data sources in Data Virtualization

Last updated: Nov 26, 2024

You create a join view of two tables from the same data source that have two columns with the VARCHAR data type. However, the join view is not pushed down on the remote data source.

Symptoms

You have two tables that have two columns with the VARCHAR data type on your remote data source. You run a query to create a join view of these tables. However, the join view is not created or pushed down on the remote data source due to the two columns with the VARCHAR data type.

Resolving the problem

To solve this issue, follow these steps:

Get the cid from the dvsys.rdb_connections table.
```
select cid, url from dvsys.rdb_connections;
```
Run the following command to specify the VARCHAR2_COMPAT option, replacing <cid> with the value obtained from step 1.
```
alter server qplex options(set <cid>@varchar2_compat 'Y')
```
If you receive an SQL1828N error message when you run step 2, run the following command, replacing <cid> with the value obtained from step 1.
```
alter server qplex options(add <cid>@varchar2_compat 'Y')
```

The VARCHAR2_COMPAT option provides a way to set the VARCHAR2_COMPAT server attribute externally, but it cannot be used to solve all the push-down problems that you might encounter when you try to join tables. The VARCHAR2_COMPAT option might be helpful in some cases only.

If join predicates are primarily using string comparisons, you must set the VARCHAR2_COMPAT option to Y to push down join views on remote data sources.
For comparing NULL values and empty strings, you must set the VARCHAR2_COMPAT option to N.
If your remote data source is Big SQL, Oracle or Db2®, which is set to ORA mode, you can set the VARCHAR2_COMPAT option to Y for trailing blanks.