Results 1 to 6 of 6
  1. #1
    Join Date
    May 2005
    Posts
    25

    Unanswered: Weird problem in load utility

    I got a flat file like

    Code:
    3001~"sdsdsdsd'""~200
    3002~"abcd"~100
    My table emp structure is

    Code:
    empno integer,
    ename varchar(20),
    sal integer
    If I try executing the following load command

    Code:
    db2 -x "load from '/export/fring/sample' of del modified by coldel~ replace into emp"
    Code:
    EMPNO ENAME               SAL
    3002    aslamah              100
    3001 "sdsdsdsd'"~200      (null)
    I am just wondering what went wrong here?

    Can somebody please help me.

    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There is no closing double quote in this line:

    Code:
    3001~"sdsdsdsd'""~200
    When the quote is doubled it loses its special meaning as a delimiter and becomes a part of the string. Try the nochardel modifier.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2005
    Posts
    25
    Thanks so much for this.

    but, I can see the table's field content as

    Code:
    3001~"sdsdsdsd""'~200
    instead of

    Code:
    3001~"sdsdsdsd'""~200
    position of single quote has been changed.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    nochardel will work as Nick stated.
    But, quotes and double quotes will be included in loaded data. like this:

    load from 'd:\dm_tech\dbforums\load_sample.txt' of del modified by coldel~ nochardel replace into emp

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM emp;
    ------------------------------------------------------------------------------
    
    EMPNO       ENAME                SAL        
    ----------- -------------------- -----------
           3001 "sdsdsdsd'""                 200
           3002 "abcd"                       100
    
      2 record(s) selected.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want to remove quotes and double quotes, you can update the column after loading.
    Code:
    ------------------------------ Commands Entered ------------------------------
    UPDATE emp
    SET ename = REPLACE(REPLACE(ename, '''', ''), '"', '')
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM emp
    ;
    ------------------------------------------------------------------------------
    SELECT * FROM emp
    
    EMPNO       ENAME                SAL        
    ----------- -------------------- -----------
           3001 sdsdsdsd                     200
           3002 abcd                         100
    
      2 record(s) selected.

  6. #6
    Join Date
    May 2005
    Posts
    25
    Thanks so much, it worked fine.

    Is is possible to preserve spaces in a particular field?

    1002~ ~100
    1003~shahnaz ~200

    First record should insert with 3 space for the second field and trailing spaces for second record.

    Thanks.

Posting Permissions

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