How can I export the data with del but don't wanna put "" ouside the character data?

[zhif]

Hello everybody,

I am trying to export the data from DB2 database to MySQL.

The command I used is

db2 "export to sys_state.del of del modified coldel; select * from sys_state"

But, I tried to modify the chardel to be blank, by default chardel for del is "". I tried to move it out.

So, I ever tried to use below command later on

db2 -x "select * from sys_state" > sys_state.out

However, what I got is no coldel, which means there is blank between the values.

Even though, I tried to use those blank to be the chardel when I load into MySQL.

Unfortunately, I found there are blank before the first value for all rows exported from DB2, so it will cause the first column null if I use the blank to be the coldel.

So, do you guys have another method that I can try to export data from DB2 that I can use ';' to be the coldel and remove "" outside the character data

For example, by default, the export data is below,

1,"Peter",123 45

Now, how to change it to

1;Peter;12345

Thanks so much!!

Cheers,
zhif

[Shyckymn]

What if you use "sed" command?

sed -e 's/"//g' bad_file.out > good_file.out

[docdiesel]

Hi,

... coldel0x3b chardel0x20 ...

could help you. 0x3b is the hex code for ";", 0x20 is a blank.

Why don't you export the data as INSERT statement?

EXPORT
...
SELECT
  'insert into mysql.table (mystr1,mystr2) values (''' ||
  db2str1 ||
  ''',''' ||
  db2str2 ||
  ''');'
;

This results in

insert into mysql.table (mystr1,mystr2) values ('text1','text2');

That way you'd get a ready to go sql script which inserts your data into the mysql table. (I guess you'd have to convert integers to varchar for concatenation but I'm not sure right now.)

Regards,

Bernd

[6bowen]

modified by nochardel

Column data will not be surrounded by character delimiters. This option should not be specified if the data is intended to be imported or loaded using DB2. It is provided to support vendor data files that do not have character delimiters. Improper usage might result in data loss or corruption

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0008304.htm