Inaccurate query results for Netezza remote data sources in Data Virtualization

If you run queries with some join queries against Netezza remote data sources that use the Netezza connection type, these queries might return inaccurate results.

Symptoms

You run some queries with join queries against Netezza remote data sources that use the Netezza connection type. You get results of 0 records for these queries.

The join queries must satisfy the following conditions:

The join type is inner join, full join, or Cartesian join.
The join predicate has equal predicates.
The data type of the columns in the join predicate is numeric.

For example

select n1.c1, n2.c2 from n1, n2 where n1.c1=n2.c1
select n1.c1, n2.c2 from n1 inner join n2 on n1.c1=n2.c1
select n1.c1, n2.c2 from n1 full join n2 on n1.c1=n2.c1

Causes

Optimized pushdown is not working correctly for queries against Netezza remote data sources that use the Netezza connection type.

Resolving the problem

To solve this issue, run the following commands to set the required Db2 registry variable. A Platform administrator must run these commands from the c-dv2u-cv-db2u-0 pod.

These commands get the current setting of the DB2_DV_OVERRIDES variable, then add the NO_SSPD(NETEZ) setting to the variable, and then ensure SQL statements are flushed from the package cache.

Log in to the Data Virtualization head pod.
```
oc rsh c-db2u-dv-db2u-0 bash
```
Switch to the Data Virtualization database instance owner db2inst1.
```
su - db2inst1
```
Obtain the current settings of the DB2_DV_OVERRIDES variable.
```
db2set | grep DB2_DV_OVERRIDES
```
For example, the output of the previous command might be similar to the following example.
```
DB2_DV_OVERRIDES=EN_OJMQT,EN_JOINFILTER,EN_GAIANQUERY_JOINFILTER
```
Using the value from step 3, update the variable setting. The new value for the variable is formed by taking the prior value and appending ,NO_SSPD(NETEZ). In the previous example, the new value would be similar to the following example.
```
EN_OJMQT,EN_JOINFILTER,EN_GAIANQUERY_JOINFILTER,NO_SSPD(NETEZ)
```
In the following command, use the value from step 4 to provide new_variable_value.
```
db2set | grep DB2_DV_OVERRIDES;
 db2set -im DB2_DV_OVERRIDES="new_variable_value";
 db2 flush package cache dynamic;
```
- To update the variable only for a single query, append the following directive to the query text.
```
/*<OPTGUIDELINES><REGISTRY>  <OPTION NAME='DB2_DV_OVERRIDES' VALUE='new_variable_value'/>*/
```
  For more information, see Optimization profiles and guidelines.
- To update the variable globally, use the following method.
```
 db2set -im DB2_DV_OVERRIDES="new variable value";db2 flush package cache dynamic;
```