DB2 UTF-8 data export and import

[?? ?]

Our database server is DB2 8.1 on AIX 5.2. I have catalogued the database on my Windows XP workstation which is running a 8.1 client.

I have created a database with codepage set as 1208 (codset is UTF-8). What i'm trying to do is export data from our production system and populate that into our test system using the db2 export command:

EXPORT TO d:\table.ixf OF IXF MODIFIED BY codepage=1208 MESSAGES d:\export.messages SELECT * FROM SCHEMA.TABLE

Definition of SCHEMA.TABLE = (id integer, text varchar(255))

I now use the same file and try to import it into our test systems using the command:
IMPORT FROM d:\table.ixf OF IXF MODIFIED BY codepage=1208 MESSAGES d:\table.import INSERT INTO SCHEMA.TABLE;

The import works, however the data is corrupt. I see "->->->" where i should be seeing DBCS data. Any ideas what i'm doing wrong here?

[Mark A]

Post by ?? ?
Our database server is DB2 8.1 on AIX 5.2. I have catalogued the database
on my Windows XP workstation which is running a 8.1 client.
I have created a database with codepage set as 1208 (codset is UTF-8).
What i'm trying to do is export data from our production system and
EXPORT TO d:\table.ixf OF IXF MODIFIED BY codepage=1208 MESSAGES
d:\export.messages SELECT * FROM SCHEMA.TABLE
Definition of SCHEMA.TABLE = (id integer, text varchar(255))
IMPORT FROM d:\table.ixf OF IXF MODIFIED BY codepage=1208 MESSAGES
d:\table.import INSERT INTO SCHEMA.TABLE;
The import works, however the data is corrupt. I see "->->->" where i
should be seeing DBCS data. Any ideas what i'm doing wrong here?

I would try it without the "MODIFIED BY codepage=1208" syntax on the export
and import. DB2 may be able to do the code page conversion for you at load
time.

[?? ?]

Tried this approach, and that didnt work either. Any other ideas?

[Mark A]

Post by ?? ?
Tried this approach, and that didnt work either. Any other ideas?

Try db2look for export and import. If you don't want to load back all the
tables that got exported, the edit the db2move.lst file to remove those you
don't want.

[Kenneth Stephen]

Post by ?? ?
Our database server is DB2 8.1 on AIX 5.2. I have catalogued the database on my Windows XP workstation which is running a 8.1 client.
EXPORT TO d:\table.ixf OF IXF MODIFIED BY codepage=1208 MESSAGES d:\export.messages SELECT * FROM SCHEMA.TABLE
Definition of SCHEMA.TABLE = (id integer, text varchar(255))
IMPORT FROM d:\table.ixf OF IXF MODIFIED BY codepage=1208 MESSAGES d:\table.import INSERT INTO SCHEMA.TABLE;
The import works, however the data is corrupt. I see "->->->" where i should be seeing DBCS data. Any ideas what i'm doing wrong here?

Hi,

You probably should read this before you do anything further:

http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0506chong/index.html

Regards,
Kenneth

[vijay rathor]

hi
what is the codepage for AIX db / and windows DB ( export and import must work without any code page conversion ) because export is design to work even when you export data in ASCI format. i.e exporting to taxt file

do export on AIX machine move the file to windows machine and then import , dont run export againes database on AIX catalogue on windows.

vijay singh

[Kenneth Stephen]

Post by vijay rathor
hi
what is the codepage for AIX db / and windows DB ( export and import must work without any code page conversion ) because export is design to work even when you export data in ASCI format. i.e exporting to taxt file
do export on AIX machine move the file to windows machine and then import , dont run export againes database on AIX catalogue on windows.
vijay singh

Vijay,

I dont think you are right. Export will convert from the codepage of
the server into the codepage of the client. Import will convert the
other way. When exporting or importing, if there is a codepage mismatch,
the tools will display an informational or warning message.

Technically, ASCII covers only the first 128 characters and so all
codepages have ASCII in them (unless they are DBCS). If your data is
exclusively ASCII, codepages are really irrelevant. There are codepages
like US-ASCII and (uk? gb?) ASCII which define what the upper 128
characters are.

On operating systems like AIX, the codepage can be defined by a variety
of mechanisms. The best way to go about it is by using the LC*
variables. That way, everything on the OS knows about the codepage your
data is in and you can use standard Unix tools (unless your data is
unicode data - in which case you need to use unicode aware tools). AIX
even has locale definitions (which is what the LC* variables define) for
UTF-8. You have to have the appropriate locale filesets installed or the
LC* variables will define functionality that you havent installed (these
filesets arent installed by default - typically only the locale that you
picked for the install gets loaded by default).

I'm not certain what your options are on Windows. Some of the windows
tools like notepad are utf-8 aware (but beware: you may be able to read
unicode data in notepad, but you may have problems writing it back out
(i.e. saving) since (on Window2000 atleast) I've seen notepad mangle
UTF-8 data). If you cant figure it out, there is always the DB2CODEPAGE
db2 registry variable.

Regards,
Kenneth

[vijay rathor]

hi Kenneth
you are right . but in the current scenerio of discussion is he really need to get in to that much detail.
he is duplication the production db on windows and he jsut need to create the database on windos with same codepage and run export on aix and import on windows.
Thanks .

vijay singh