0

I have a new problem with CLPPlus and IMPORT command.

I try to import data into a table which contain a space in its name, but it seems to fail:

SQL> IMPORT FROM '/home/i1058/outfile' INSERT INTO USER1."TABLE 1"; 
Invalid Syntax Error
SQL> IMPORT FROM '/home/i1058/outfile' INSERT INTO USER1.'TABLE 1';
Invalid Syntax Error

I have tried many things but it always fails.

Of course I have also tried a 'classic' LOAD with CLP and it works perfectly :

db2 'LOAD FROM "outfile" OF DEL MODIFIED BY CODEPAGE=1208 NOCHARDEL INSERT INTO "USER1"."TABLE 1"'
...
Number of rows read         = 3
Number of rows skipped      = 0
Number of rows loaded       = 3
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 3

Any ideas ?

Thanks and regards

Steph
  • 47
  • 1
  • 8
  • I think CLPPlus uses the JLine library to handle console input. Try escaping the quotes: `... INSERT INTO USER1.\"TABLE 1\"` – mustaccio Feb 01 '17 at 13:19
  • Thanks for your help, but it seems to fail : `SQL> IMPORT FROM '/home/i1058/outfile' INSERT INTO USER1.\"TABLE 1\"; Invalid Syntax Error` – Steph Feb 02 '17 at 08:37
  • Actually, this has nothing to do with the table name; you are missing the input file type identifier (`OF DEL` presumably), which you for some reason included in the successful `LOAD` command invocation. – mustaccio Feb 02 '17 at 13:12
  • The options for the CLPPlus `IMPORT` command are not the same as the ones for the CLP `LOAD` command. As a result, the `OF DEL` option does not exist with the `IMPORT` command. – Steph Feb 02 '17 at 14:42
  • Well, it looks like a documentation error to me. I'll post an answer to demonstrate how it works. – mustaccio Feb 02 '17 at 14:56

1 Answers1

0

Here's how it works on my 10.5 system:

SQL> create table "TEST TBL" (f1 int);

DB250000I: The command completed successfully.

SQL> IMPORT FROM '/tmp/dat' of del insert into "TEST TBL";

Total number of rows read : 6
Total number of rows skipped : 0
Total number of rows inserted : 6
Total number of rows updated : 0
Total number of rows rejected : 0
Total number of rows committed : 6
DB250000I: The command completed successfully.
SQL> IMPORT FROM '/tmp/dat'  insert into "TEST TBL";
Invalid Syntax Error

Looks like documentation has an error in that it does not show the file type option for the CLPPlus import command.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
  • Indeed the documentation seems to be incomplete, good to know! But the `OF DEL` option introduces a new problem, in fact I'm connected to a remote database. In your case, I guess all things are local (local data file and connected to a local database, on same computer), but im my case, I have a data file locally on my computer and a remote database on another computer (connected as `SQL> CONNECT usr/'pwd'@ip:port/BLUDB;`). – Steph Feb 03 '17 at 09:55
  • If I do `SQL> IMPORT FROM '/home/i1058/outfile' INSERT INTO USER1."TABLE 1"; Invalid Syntax Error` When there is no space in tablename it works perfectly, now if I run `SQL> IMPORT FROM '/home/i1058/outfile' OF DEL INSERT INTO USER1."TABLE 1"; SQL3196N The input file was not found.` because the data file seems to be not physically on the remote computer!! And indeed after copied data file into the remote computer it works : `SQL> IMPORT FROMM '/tmp/outfile' OF DEL INSERT INTO USER1."TABLE 1"; ... DB250000I: The command completed successfully.` So is it a bug or something like that??^^ – Steph Feb 03 '17 at 09:56