What is Db2 federation CODEPAGE server option?

Answer

The CODEPAGE server option is only used in the Federation ODBC and JDBC wrappers. For other wrappers, such as NET8 and DRDA, the CODEPAGE is an invalid server option that might cause error.

1. What is the CODEPAGE server option?

The CODEPAGE option is used to specify the client code page by issue a CREATE SERVER statement with the CODEPAGE label. The ALTER SERVER cannot change the CODEPAGE option.

CREATE SERVER SERVER1 TYPE MSSQLSERVER VERSION 2016 WRAPPER MSSQLODBC3 OPTIONS(NODE 'SAMPLE', DBNAME 'TESTDB', CODEPAGE '1208');

CREATE SERVER SERVER2 TYPE mysql VERSION 5.7 WRAPPER odbc_wrapper OPTIONS (NODE 'NODE1', DBNAME 'testdb', CODEPAGE '1254')

2. Why need to set the CODEPAGE option?

When the character set or code page of remote data source is different from Db2 local database, we need the CODEPAGE option to tell federation the remote character set or codepage.

3. When to set the CODEPAGE option?

We need to specify the client code page when it is different from the federation database code page.

4. How to find the corresponding codepage of remote database?

Some data sources use Character Sets same as code set of Db2. For example, the corresponding codepage of CHARACTER SET latin5 COLLATE latin5_turkish_ci in MySQL is 1254. For more information about codepage and character set, refer to Supported territory codes and code pages, MySQL Character Sets, Collations, Unicode.

5. What is the valid CODEPAGE option value?

The CREATE SERVER statement does not check the validity of CODEPAGE, so you can enter any numeric value, but invalid numeric values might cause errors in subsequent statements.

6. What will happen when the CODEPAGE option is incorrect?

If the code page between the federated database and the remote data source is different, and the CODEPAGE setting is incorrect, you might get unreadable characters or even failure message.

Case 1. Create nickname failed due to lack of CODEPAGE option

CREATE NICKNAME NK_HASAR_NOTLAR FOR SERVER1.HASAR_NOTLAR

SQL1822N Unexpected error code "HY000" received from data source"SERVER1". Associated text and tokens are "Table 'H.H' doesn't exist".SQLSTATE=560BD

Solution:

As remote data source is MySQL and character set is latin5_turkish_ci, the mapped CODEPAGE in Db2 is turkish.
So we need to add server option CODEPAGE '1254' into the create server statement.

CREATE SERVER SERVER1 TYPE mysql VERSION 5.7 OPTIONS (HOST 'test1.fyre.ibm.com',DBNAME 'testdb', CODEPAGE '1254')

create server server_JDBC_db2 type db2 version 11.1.3 wrapper JDBC authorization "DB2INST2" password "xxxxxxxx" options (DRIVER_PACKAGE '/home/liuyaq/sqllib/java/db2jcc4.jar', DRIVER_CLASS 'com.ibm.db2.jcc.DB2Driver', URL 'jdbc:db2://indicate1.fyre.ibm.com:25011/test819')
DB20000I  The SQL command completed successfully.

set passthru server_JDBC_db2
DB20000I  The SQL command completed successfully.

create table FEDCDPG_TESTCHAR(c1 char(6),c2 varchar(6))
DB20000I  The SQL command completed successfully.

insert into FEDCDPG_TESTCHAR values('æçèéêë','ÅÆÇÈÉÊ')
DB20000I  The SQL command completed successfully.

select c1,c2 from FEDCDPG_TESTCHAR

C1     C2
------ ------
æçè    ÅÆÇ

  1 record(s) selected.

set passthru reset
DB20000I  The SQL command completed successfully.

create nickname nk2 for server_JDBC_db2."FEDCDPG_TESTCHAR"
DB20000I  The SQL command completed successfully.

select c1,c2 from nk2

C1     C2
------ ------
æçè    ÅÆÇ

  1 record(s) selected.

create server server_JDBC_db2 type db2 version 11.1.3 wrapper JDBC authorization "DB2INST2" password "xxxxxxxx" options (DRIVER_PACKAGE '/home/liuyaq/sqllib/java/db2jcc4.jar', DRIVER_CLASS 'com.ibm.db2.jcc.DB2Driver', URL 'jdbc:db2://indicate1.fyre.ibm.com:25011/test819', codepage '819')
DB20000I  The SQL command completed successfully.