IBM Support

IC77689: INCORRECT EXPORTED DATA LENGTH FOR THE EXPORT COMMAND SPECIFYING MODIFIED BY CODEPAGE=954

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error 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
    

Local fix

  • Use DB2CODEPAGE instead of "MODIFIED BY".
    
    export DB2CODEPAGE=954
    
    It should be done before connecting to the database.
    

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.                                  *
    ****************************************************************
    

Problem conclusion

  • The issue was fixed in V9.7 Fix Pack 5.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC77689

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-07-25

  • Closed date

    2011-12-08

  • Last modified date

    2011-12-08

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IC77874

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
08 December 2011