db2 import ,empty string issue.

MayumiYuu · #1 (**permalink**) 12-25-09, 04:23

Hello expert.

I have some problem about Migrate Informix to DB2.
I export the data from Informix table with unload command to test.txt.

Code:

unload to '/tmp/test.txt' delimiter '|' select * from test

I've notice that the empty string field are converted to '\' .
for example table test has a data like this.

Code:

-------------------------------
   ID     |   name    |  address 
-------------------------------
    0     |    Tom    |   city1
    1     |           |   city2
    2     |   (null)  |   
-------------------------------

so the file test.txt we have unload is like this.

Code:

0|Tom|city1
1|\|city2
2||\

I think the Informix unload command convert empty string to backslash(\).
When I import test.txt into DB2 using the command below.

Code:

import from test.txt of del modified by chardel? coldel| codepage=874 commitcount 10 insert into new_test

DB2 see the \ like a character.

I wondering that how DB2 know \ as empty string?
Or this is an Informix issues?
I tried to serch the solution but can not found any idea.

Any suggesion?
Thanks in advance.

-Chai-

tonkuma · #2 (**permalink**) 12-25-09, 23:07

Although, I don't know Informix, I think that is an Informix issues.

Export of DB2 delimits character strings by double quotes.

Test data:

Code:

------------------------------ Commands Entered ------------------------------
SELECT * FROM test;
------------------------------------------------------------------------------
SELECT * FROM test

ID          NAME       ADDRESS   
----------- ---------- ----------
          0 Tom        city1     
          1            city2     
          2 -                    
          4 "          '         

  4 record(s) selected.

Command:
EXPORT TO export_test.del OF del SELECT * FROM test;

produces.....

Code:

0,"Tom","city1"
1,"","city2"
2,,""
4,"""","'"

NULL(nothing between commas) is distinguished from empty string("").

If modified by nochardel, empty strings are same as NULLs. Like this:
EXPORT TO export_test_nochardel.del OF del MODIFIED BY nochardel SELECT * FROM test;

produces.....

Code:

0,Tom,city1
1,,city2
2,,
4,",'

Internet Services

Web Development

Digital Marketing

Consumer Tech