asked on

SQL IMPORT command from Java results in SQLCODE: -104, SQLSTATE: 42601 errror

We've an DB2 on AIX. I'm programming the stand alone application, that has to do different preprocessing stuff, then to import certain data in a table and afterwards some after-processing stuff. As the whole SQL script is rather big and I have to run it from Java, I'll parce the script into the separate command and run them from the statment like:

statement.executeUpdate(sCommand);

Everything is fine, all the commands are running, besides of IMPORT command. When I'm trying to run the IMPORT this way, I get the SQLCODE: -104, SQLSTATE: 42601 error, that is, as far as I've found, the invalid symbol (or symbols) in the command.
I've tried to make a different versions of the command, with single quote instead of quote, with double slash insteand of single slash etc. Till then all versions run into this error.
If I try to run the same command from the control center, it runs smoothly and does the job.

So the questions are:

A. What symbol is causing the problem? Is it possible to replace it the 'safe' variant somehow?
B. If I do not manage to run it this way, what alternatives do I have to run this part of script on this machine?

Thanx in advance...

------------------- these are some command versions, that I've tried to run: -----------------------
1.
IMPORT FROM "d:\_Dateneinpflege\haft\AH112010.csv" OF DEL MODIFIED BY COLDEL; DECPT, METHOD P
(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18) COMMITCOUNT 1000 SKIPCOUNT 1
MESSAGES "d:\_Dateneinpflege\haft\AH112010.log"
INSERT INTO ISIAMS.haft_roh (VERSNR, K_ANREDE, K_TITEL, K_NAME, K_VORNAME, K_STRASSE, K_PLZ, K_ORT, ABLAUFDATUM,
ZAHLUNGSWEISE, BEITRAG, GJNB, DSP, DAUERNACHLASS, OEFFENTLICHER_DIENST, FDL, ADEID, DECKUNG);

2.
IMPORT FROM 'd:\_Dateneinpflege\haft\AH112010.csv' OF DEL MODIFIED BY COLDEL; DECPT, METHOD P
(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18) COMMITCOUNT 1000 SKIPCOUNT 1
MESSAGES 'd:\_Dateneinpflege\haft\AH112010.log'
INSERT INTO ISIAMS.haft_roh (VERSNR, K_ANREDE, K_TITEL, K_NAME, K_VORNAME, K_STRASSE, K_PLZ, K_ORT, ABLAUFDATUM,
ZAHLUNGSWEISE, BEITRAG, GJNB, DSP, DAUERNACHLASS, OEFFENTLICHER_DIENST, FDL, ADEID, DECKUNG);

3.
IMPORT FROM 'd:\\_Dateneinpflege\\haft\\Haft_import.csv' OF DEL MODIFIED BY COLDEL; DECPT, METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18) COMMITCOUNT 1000 SKIPCOUNT 1 MESSAGES 'd:\\_Dateneinpflege\\haft\\haft_import.log' INSERT INTO ISIAMS.haft_roh (VERSNR, K_ANREDE, K_TITEL, K_NAME, K_VORNAME, K_STRASSE, K_PLZ, K_ORT, ABLAUFDATUM, ZAHLUNGSWEISE, BEITRAG, GJNB, DSP, DAUERNACHLASS, OEFFENTLICHER_DIENST, FDL, ADEID, DECKUNG)

---------error message in all cases: -------------------------
com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: IMPORT FROM 'd:\\_Dateneinpflege\\haft\\Ha;BEGIN-OF-STATEMENT;<values>

CEHJ

>>As the whole SQL script is rather big and I have to run it from Java

It's probably going to be easier, faster and less error-prone to Runtime.exec the script against DB2

gmarino

Try chaning the filename so that it does not start with ' _'.

If you still have the error, start breaking down the command - run a simple version of the command first and then add components. When doing this, use an EMPTY file so that there is no real data added to the table.

For example, start with :
IMPORT FROM "d:\_Dateneinpflege\haft\empty.csv" OF DEL INSERT INTO ISIAMS.haft_roh

If that works, add ONE more option and rerun. For example.
IMPORT FROM "d:\_Dateneinpflege\haft\empty.csv" OF DEL MODIFIED BY COLDEL; INSERT INTO ISIAMS.haft_roh

Keep doing this until you get to the full command you desire. Once you have the full command working with an EMPTY file, add ONE record to the csv file and test. Then add multiple records and retest.

This method will pinpoint for you exactly where the problem lies.

Greg

incerc

Pls try this :

IMPORT FROM d:\\_Dateneinpflege\\haft\\Haft_import.csv OF DEL MODIFIED BY COLDEL; DECPT, METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18) COMMITCOUNT 1000  SKIPCOUNT 1  MESSAGES d:\\_Dateneinpflege\\haft\\haft_import.log INSERT INTO ISIAMS.haft_roh (VERSNR, K_ANREDE, K_TITEL, K_NAME, K_VORNAME, K_STRASSE, K_PLZ, K_ORT, ABLAUFDATUM, ZAHLUNGSWEISE, BEITRAG, GJNB, DSP, DAUERNACHLASS, OEFFENTLICHER_DIENST, FDL, ADEID, DECKUNG)

Open in new window

No quotes (" or ') needed for file names.
Not sure about path delimiters : for testing purposes of the syntax, you can copy your file(s) into the working folder and refer to it with the file name only.

Some IMPORT examples can be found here :

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

ASKER CERTIFIED SOLUTION

mustaccio

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

inversojvo

ASKER

wow, many of possible solutions. I'll try them tomorrow and give to know then.
Thanx, guys, you are great!!

inversojvo

ASKER

So, I've tried various variants. It looks really so, that I can't send the administrative command like IMPORT with Statement.executeUpdate(sql_expression) or similar.

I've tried then an advice of mustaccio to run it with ADMIN_CMD. Below are the results of some runs with different formats of this IMPORT-line from log, so it complains mostly of the format of MESSAGES part of the command, if I understood right. If I take the MESSAGES parameter away, I'm getting the error message even with no SQLSTATE number (the last log entry):

CALL ADMIN_CMD('import from "d:\\HaftImport.csv" OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18) COMMITCOUNT 1000 SKIPCOUNT 1 MESSAGES "d:\\HaftImport.log" INSERT INTO ISIAMS.haft_roh (VERSNR, K_ANREDE, K_TITEL, K_NAME, K_VORNAME, K_STRASSE, K_PLZ, K_ORT, ABLAUFDATUM, ZAHLUNGSWEISE, BEITRAG, GJNB, DSP, DAUERNACHLASS, OEFFENTLICHER_DIENST, FDL, ADEID, DECKUNG)')
com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: "d:\\HaftImport.log";MESSAGES;ON

CALL ADMIN_CMD('import from "d:\\HaftImport.csv" OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18) COMMITCOUNT 1000 SKIPCOUNT 1 MESSAGES "..\\HaftImport.log" INSERT INTO ISIAMS.haft_roh (VERSNR, K_ANREDE, K_TITEL, K_NAME, K_VORNAME, K_STRASSE, K_PLZ, K_ORT, ABLAUFDATUM, ZAHLUNGSWEISE, BEITRAG, GJNB, DSP, DAUERNACHLASS, OEFFENTLICHER_DIENST, FDL, ADEID, DECKUNG)')
com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: "..\\HaftImport.log";MESSAGES;ON

CALL ADMIN_CMD('import from "d:\\HaftImport.csv" OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18) COMMITCOUNT 1000 SKIPCOUNT 1 MESSAGES ".\HaftImport.log" INSERT INTO ISIAMS.haft_roh (VERSNR, K_ANREDE, K_TITEL, K_NAME, K_VORNAME, K_STRASSE, K_PLZ, K_ORT, ABLAUFDATUM, ZAHLUNGSWEISE, BEITRAG, GJNB, DSP, DAUERNACHLASS, OEFFENTLICHER_DIENST, FDL, ADEID, DECKUNG)')
com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: ".\HaftImport.log";MESSAGES;ON

CALL ADMIN_CMD('import from d:\HaftImport.csv OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18) COMMITCOUNT 1000 SKIPCOUNT 1 MESSAGES .\HaftImport.log INSERT INTO ISIAMS.haft_roh (VERSNR, K_ANREDE, K_TITEL, K_NAME, K_VORNAME, K_STRASSE, K_PLZ, K_ORT, ABLAUFDATUM, ZAHLUNGSWEISE, BEITRAG, GJNB, DSP, DAUERNACHLASS, OEFFENTLICHER_DIENST, FDL, ADEID, DECKUNG)
')
com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: .\HaftImport.log;MESSAGES;ON

CALL ADMIN_CMD('import from d:\HaftImport.csv OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18)
COMMITCOUNT 1000 SKIPCOUNT 1 INSERT INTO ISIAMS.haft_roh (VERSNR, K_ANREDE, K_TITEL, K_NAME, K_VORNAME, K_STRASSE, K_PLZ, K_ORT, ABLAUFDATUM, ZAHLUNGSWEISE, BEITRAG, GJNB, DSP, DAUERNACHLASS, OEFFENTLICHER_DIENST, FDL, ADEID, DECKUNG)
')
com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -3038, SQLSTATE: , SQLERRMC: null

I'll think and try some more variants of this call, and write then, if it did the job or not. But I'm not quite sure, that I'll get it run...

2 CEHJ: how can I run the Runtime.exec the script against DB2 from Java? Do you mean to call the db2 with .dll file as parameter?

mustaccio

I gave you a link where it explains the allowed syntax for IMPORT when used with ADMIN_CMD. In particular, it says that MESSAGES cannot be written to a file, obviously, because the actual import runs on the server. Here's that link again: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0023575.html

Note that the same condition applies to the input file - it must reside on the server, not on the client.

SQL3038N (SQLCODE -3038) means that you have invalid characters in your command. Make sure you don't have extra carriage return or line feed symbols or stuff like that.

SOLUTION

CEHJ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

inversojvo

ASKER

2 mustaccio: yep, you are right, I've missed this point while reading :(. I've reread again, did the necessary changes at finally it run smoothly.

Thank you 1000 times guys, especially mustaccio and CEHJ !!

I'll split the points as 375 to 125 between mustaccio and CEHJ. I hope it's OK for you.

Have a nice weekend, everybody!

CEHJ

:-)