Expanded nickname column when the code point size is incompatible

Code point size is the number of bytes used to encode abstract characters in a specific encoding. When the code point size differs between a federated database and a remote data source, you can get truncated data returned or inserted, as well as update failures. Whether these results occur depends on the encoding method used on the remote server.

Code point size explained

In Federated Db2 environments, columns in a remote data source need to have the same code point size as their corresponding columns in the Federated database. If these values do not match, data can be truncated and errors can be generated.

For example, double-byte character set (DBCS) is an encoding method where all characters or graphic characters are encoded in 2 bytes. If a remote character string is encoded using DBCS and an associated federated database uses UTF-8 encoding, each 2-byte character is represented in UTF-8 as a sequence of up to 4 bytes on the federated database. The nickname column in the Federated database is the same length as the remote table column, so the nickname column can not hold the complete string that is fetched from the remote data source. The result is a truncated data string.

Automatic expanded nickname column size

In Db2® 11.5.7 and later, you can set the COLUMN_LENGTH_AUTO_VARIATION option to control whether the nickname column length changes automatically. By default, if COLUMN_LENGTH_AUTO_VARIATION is not set, the nickname length is automatically expanded according to the conversion rules . For more information, see Setting the COLUMN_LENGTH_AUTO_VARIATION option for Federated databases.

Data sources that return or insert truncated data when code point sizes are different:

Query returns for the following data sources return truncated results, unreadable results, or fail if the code point sizes are not compatible:

BigSQL
SAP Sybase
Teradata
Informix
MSSQL
ODBC
JDBC

If the truncated data ends in a dangling character, blanks fill the remaining bytes.

Note: In some cases, manually changing the nickname column length can avoid result set truncation.

Data sources that automatically resize when code point sizes are different:

Truncated results are not returned for the following data sources, when the remote server code point size is different from the federated database.

DRDA
Oracle

Instead, the Nickname column automatically grows larger to receive the completed column data from the remote data source. The increased column length might exceed the actual length (in bytes) of the character result, so spaces fill the excess bytes. For more information, see Setting the COLUMN_LENGTH_AUTO_VARIATION option for Federated databases.

Examples

The following example shows the column length for a table in a remote Oracle Server with a code set of GBK2312.

Name                                    Nullable? Type
----------------------------------------- -------- ----------------------------
NAME						               CHAR(12)
ADDRESS					                   VARCHAR2(12)
HOBBY					                     NCHAR(12)
SKILL					                     NVARCHAR2(12)

The federated database code page uses UTF-8 encoding, so the column length of CHAR and VARCHAR data types from the Oracle table is multiplied by three (12 x 3=36):

Column name            schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
NAME                            SYSIBM    CHARACTER                   36     0 Yes
ADDRESS                         SYSIBM    VARCHAR                     36     0 Yes
HOBBY                           SYSIBM    GRAPHIC                     12     0 Yes
SKILL                           SYSIBM    VARGRAPHIC                  12     0 Yes

The following example shows the command syntax and output from querying the extended nickname CHAR column name in the Federated database. Note that blank characters are appended to the end of the result:

db2 "select hex(name) from nk1"
2
------------------------------------------------------------------------
E5BCA0E4B889E69D8EE59B9BE78E8BE4BA94202020202020202020202020202020202020

The following example shows the command syntax and output from querying the extended nickname VARCHAR column address in the Federated database. Note that no additional spaces are appended:

db2 "select hex(address) from nk1"
2
------------------------------------------------------------------------
E4B8ADE585B3E69D91E58D81E58FB7E9878C

Usage Notes

The extended column size does not impact INSERT/UPDATE statements. The inserted data or the appended blanks are not truncated but inserted into the CHAR column with appending blanks. The blanks are extended when queried from the nickname.
A passthru query returns the complete result set without being truncated.
A three-part name query returns the complete result set without being truncated. It works in the same way as querying from a nickname.
Importing or inserting data from a nickname that exceeds the acceptable length of the remote data source results in an error.

Restrictions

An export-to-nickname operation gets additional blanks, and the string functions parameter is expanded accordingly.
For example: If the remote column length is 12, and the column size is expanded by three times when creating the nickname, the length parameter in the RPAD function is expanded accordingly:
```
(RPAD(Q1.ADDRESS, 36, ' ') = ' ') 
```
Adjusting the nickname column size to prevent the appending blanks can result in an unexpected truncation.
In Db2 11.5.7 and later, insertions or updates fail if the character string conversion results in a larger number of bytes than the size of the remote data source column. In some cases, the code point size of the Federated database is smaller than the remote data source. For example, if the remote data source is UTF8, one character occupies 3 bytes: If the federated database is DBCS, one character occupies 2 bytes, so 10 characters take up 20 bytes in the Federated database and 30 bytes in the remote data source. Since the maximum column length of the remote table is the same as the nickname, which is 20 bytes, errors such as SQL1822N are reported.