VARCHAR2 data type in federated systems

To tune your query processing, your access plan must account for blank padding with VARCHAR2 data.

You use the VARCHAR2_COMPAT server option to enable support for VARCHAR2 compatible data sources.

The VARCHAR2 compatibility semantics determine how your federated server and data source handle the VARCHAR2 data type depending on whether the federated server, the data source, or both are VARCHAR2 compatible. The systems have these possible configurations:

Your federated server is VARCHAR2 compatible but connects to a data source that is not VARCHAR2 compatible.
Your federated server is not VARCHAR2 compatible but connects to a data source that is VARCHAR2 compatible.
Your federated server and data source are both VARCHAR2 compatible.

When your federated server or Db2® data source is VARCHAR2 compatible, the VARCHAR2 data type is handled as a synonym for the VARCHAR data type.

Tip: To ensure improved performance, in systems that only connect to VARCHAR2-compatible data sources, your federated server should also be VARCHAR2 compatible.

In the following scenarios, the federated server handles your empty string values and pushes down the operations to the data source based on the VARCHAR2 compatibility semantics.

Scenario 1

The federated server is not VARCHAR2 compatible but connects to a data source that is VARCHAR2 compatible. The federated server allows empty strings, but the data source does not. Thus, all empty strings that are pushed down to the data source are converted to NULL values.

The default behavior of the federated server is to use blank-padding semantics on the CHAR data type before pushing the result down to the data source. But, the federated server does not use blank padding in operations that only specify empty strings.

Examples

The following INSERT and UPDATE statements only include empty strings are not blank padded. The statements push down empty string values that are converted to NULL values by the data source.
For example, the following statements are not blank padded by the federated server:
```
INSERT INTO n1 (c1) VALUES (‘')
```
```
UPDATE n1 SET c1=''
```
```
INSERT INTO n1 (c1) VALUES (‘'), (‘')
```
The following INSERT statement sends 10 blanks to the data source instead of the empty string:
```
INSERT INTO n1 (col_char) VALUES (‘'), (‘ibm')
```

Scenario 2

The federated server is VARCHAR2 compatible but connects to a data source that is not VARCHAR2 compatible. The VARCHAR2 compatibility semantics of the federated server does not allow empty strings nor does it use blank-padding semantics. But, the semantics of the data source allows empty strings and uses blank-padding semantics. Thus, the federated server restricts operations that involve empty strings from being pushed down, unless the federated server semantics and data consistency are preserved.

Examples

The following INSERT statement is pushed down to the data source, because the empty string is converted to a NULL value and cannot be handled locally:
```
INSERT INTO n1 (c1) VALUES (‘')
```
The following INSERT statement is pushed down to the data source if you split the statement into two separate statements:
```
INSERT INTO n1 SELECT * FROM n2
```
You are required to use two statements when the target or source tables are from a data source that is not VARCHAR2 compatible, for example:
```
SELECT * FROM n2
INSERT INTO n1 VALUES (:H0)
```
Functions that are nested within expressions are run locally in the federated server, for example:
```
SELECT LENGTH(TRIM(c1)) FROM n1
```
All comparison operations are run locally in the federated server because inconsistent results are possible when comparison operations are run by the data source.

Scenario 3

The federated server and data source are both VARCHAR2 compatible. Neither the federated server nor the data source allows empty strings or use blank-padding semantics. Thus, all operations that involve empty strings are pushed down to the data source, because the semantics are preserved.