Weird problem in load utility

shahnazurs · #1 (**permalink**) 07-16-09, 06:58

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.

n_i · #2 (**permalink**) 07-16-09, 07:21

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.

shahnazurs · #3 (**permalink**) 07-16-09, 08:33

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.

tonkuma · #4 (**permalink**) 07-16-09, 10:09

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.

tonkuma · #5 (**permalink**) 07-16-09, 10:14

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.

shahnazurs · #6 (**permalink**) 07-20-09, 06:11

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech