Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2009
    Posts
    1

    Unanswered: db2 import ,empty string issue.

    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-

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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,",'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •