Column delimiter with CLPPlus

Question

I am trying to import a file with the DB2 CLPPlus tool like the following :

cat outfile
1;"a";"b"
2;"c";"d"

clpplus -nw
SQL> SET COLSEP ';';
SQL> CONNECT MYUSER/MYPWD@IP:PORT/BLUDB;
SQL> IMPORT FROM '/home/i1058/outfile' INSERT INTO USER1.TABLE3;
[jcc][1091][10404][3.69.66] Invalid data conversion: Parameter instance 1;a;b is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815

In the DB2 official documentation for the CLPPlus tool at the IMPORT command (https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.swg.im.dbclient.clpplus.doc/doc/r0059535.html), we can see the following :

Data can be imported only from a delimited file. The delimited file can be of any file type, such as: .del, .ixf, or .txt. The , character is the default delimiter. You can set the delimiter to another character by using the SET CLPPlus command.

In bold there is a sentence which match exactly with what I want, but unfortunately, it doesn't work, or maybe I missed something. Can someone please help ?

Thanks and regards, Stephane

CREATE TABLE USER1.TABLE3 (col1 int, col2 varchar(20), col3 varchar(20)) — Steph, Jan 19 '17 at 13:48

score 0 · Accepted Answer · answered Jan 19 '17 at 17:21

0

You need to set the delimiter option for CLPPlus, not colsep:

SQL> show delimiter
Delimiter ,
SQL> SET delimiter ";"
SQL> IMPORT FROM '/home/i1058/outfile' INSERT INTO USER1.TABLE3;
Number of Rows Inserted:2

answered Jan 19 '17 at 17:21

Ian Bjorhovde

10,916
1
28
25

Hello, I don't know why this option does not appear on the official documentation, but anyway thank you very much ;) – Steph Jan 20 '17 at 08:38

Column delimiter with CLPPlus

1 Answers1