|
Problem IC77689
|
Status: Closed |
INCORRECT EXPORTED DATA LENGTH FOR THE EXPORT COMMAND SPECIFYING MODIFIED BY CODEPAGE=954 |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
The exported data length may be incorrect when "MODIFIED BY
codepage=954" is specified to the export command.
Note that the issue may happen when the system codepage is UTF-8
for Japanese language (ex. JA_JP.UTF-8 for AIX) but it does not
happen for Ja_JP (SJIS) or ja_JP (EUC JP).
The following is an example of reproduction scenarios on AIX.
Though the correct data length should be 15 bytes, the exported
data is expanded with trailing x'20' characters.
$ export LANG=JA_JP.UTF-8
$ db2start
07/12/2011 16:23:36 0 0 SQL1063N DB2START processing
was successful.
SQL1063N DB2START processing was successful.
$ db2 CREATE DB test USING CODESET EUC-JP TERRITORY JP
DB20000I The CREATE DATABASE command completed successfully.
$ db2 connect to test
Database Connection Information
Database server = DB2/AIX64 9.7.3
SQL authorization ID = E97Q3AF
Local database alias = TEST
$ db2 "create table test (ip char(15) not null default
'127.0.0.1')"
DB20000I The SQL command completed successfully.
$ db2 "insert into test values ('1.1.1.1')"
DB20000I The SQL command completed successfully.
$ db2 select ip from test
IP
---------------
1.1.1.1
1 record(s) selected.
$ db2 select "HEX(ip) from test"
1
------------------------------
312E312E312E312020202020202020
1 record(s) selected.
$ db2 "EXPORT TO test1.csv OF DEL SELECT * FROM test"
SQL3104N The Export utility is beginning to export data to file
"test1.csv".
SQL3105N The Export utility has finished exporting "1" rows.
Number of rows exported: 1
$ od -x test1.csv
0000000 2231 2e31 2e31 2e31 2020 2020 2020 2020
0000020 220a
0000022
$ db2 "EXPORT TO test2.csv OF DEL MODIFIED BY
codepage=954 SELECT * FROM test"
SQL3104N The Export utility is beginning to export data to file
"test2.csv".
SQL3105N The Export utility has finished exporting "1" rows.
Number of rows exported: 1
$ od -x test2.csv
0000000 2231 2e31 2e31 2e31 2020 2020 2020 2020
0000020 2020 2020 2020 2020 2020 2020 2020 2020
0000040 2020 2020 2020 2020 2020 2020 2020 220a
0000060 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users using export command in Japanese language environment. *
****************************************************************
* PROBLEM DESCRIPTION: *
* The exported data length may be incorrect when "MODIFIED BY *
* codepage=954" is specified to the export command. *
* *
* Note that the issue may happen when the system codepage is *
* UTF-8 *
* for Japanese language (ex. JA_JP.UTF-8 for AIX) but it does *
* not *
* happen for Ja_JP (SJIS) or ja_JP (EUC JP). *
* *
* The following is an example of reproduction scenarios on *
* AIX. *
* Though the correct data length should be 15 bytes, the *
* exported *
* data is expanded with trailing x'20' characters. *
* *
* $ export LANG=JA_JP.UTF-8 *
* $ db2start *
* 07/12/2011 16:23:36 0 0 SQL1063N DB2START processing *
* was successful. *
* SQL1063N DB2START processing was successful. *
* $ db2 CREATE DB test USING CODESET EUC-JP TERRITORY JP *
* DB20000I The CREATE DATABASE command completed *
* successfully. *
* $ db2 connect to test *
* *
* Database Connection Information *
* *
* Database server = DB2/AIX64 9.7.3 *
* SQL authorization ID = E97Q3AF *
* Local database alias = TEST *
* *
* $ db2 "create table test (ip char(15) not null default *
* '127.0.0.1')" *
* DB20000I The SQL command completed successfully. *
* $ db2 "insert into test values ('1.1.1.1')" *
* DB20000I The SQL command completed successfully. *
* $ db2 select ip from test *
* *
* IP *
* --------------- *
* 1.1.1.1 *
* *
* 1 record(s) selected. *
* *
* $ db2 select "HEX(ip) from test" *
* *
* 1 *
* ------------------------------ *
* 312E312E312E312020202020202020 *
* *
* 1 record(s) selected. *
* *
* $ db2 "EXPORT TO test1.csv OF DEL SELECT * FROM test" *
* SQL3104N The Export utility is beginning to export data to *
* file *
* "test1.csv". *
* *
* SQL3105N The Export utility has finished exporting "1" *
* rows. *
* *
* *
* Number of rows exported: 1 *
* *
* $ od -x test1.csv *
* 0000000 2231 2e31 2e31 2e31 2020 2020 2020 2020 *
* 0000020 220a *
* *
* 0000022 *
* $ db2 "EXPORT TO test2.csv OF DEL MODIFIED BY *
* codepage=954 SELECT * FROM test" *
* SQL3104N The Export utility is beginning to export data to *
* file *
* "test2.csv". *
* *
* SQL3105N The Export utility has finished exporting "1" *
* rows. *
* *
* *
* Number of rows exported: 1 *
* *
* $ od -x test2.csv *
* 0000000 2231 2e31 2e31 2e31 2020 2020 2020 2020 *
* 0000020 2020 2020 2020 2020 2020 2020 2020 2020 *
* 0000040 2020 2020 2020 2020 2020 2020 2020 220a *
* *
* 0000060 *
****************************************************************
* RECOMMENDATION: *
* Upgrade to V9.7 Fix Pack 5. *
**************************************************************** |
| Local Fix: |
Use DB2CODEPAGE instead of "MODIFIED BY".
export DB2CODEPAGE=954
It should be done before connecting to the database. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
The issue was fixed in V9.7 Fix Pack 5. |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC77874
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 25.07.2011
08.12.2011
08.12.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
