Solved

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

Posted on 2010-11-22
10
3,162 Views
Last Modified: 2012-05-10
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>
0
Comment
Question by:inversojvo
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 34188246
>>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
0
 
LVL 4

Expert Comment

by:gmarino
ID: 34188407
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
0
 
LVL 4

Expert Comment

by:incerc
ID: 34188546
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
0
 
LVL 8

Accepted Solution

by:
mustaccio earned 375 total points
ID: 34191773
You can only run SQL statements via JDBC; IMPORT is not an SQL statement. It is a command for the DB2 command line processor.

There is a stored procedure interface, ADMIN_CMD() to some of such commands, including IMPORT. You will find details, along with examples, in the manual:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0012547.html

You can call that stored procedure via JDBC. Note that the supported features differ between DB2 versions, so make sure you read the manual for your particular version.

Alternatively, as CEHJ suggested, you can run the command by invoking the DB2 command line processor in the system shell.
0
 

Author Comment

by:inversojvo
ID: 34192120
wow, many  of possible solutions. I'll try them tomorrow and give to know then.
Thanx, guys, you are great!!
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:inversojvo
ID: 34213235
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?
0
 
LVL 8

Expert Comment

by:mustaccio
ID: 34213422
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.
0
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 125 total points
ID: 34213667
>>2 CEHJ: how can I run the Runtime.exec the script against DB2 from Java?

Imagine you're running it from the command line - it should be the same command
0
 

Author Comment

by:inversojvo
ID: 34217816
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!
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34218620
:-)
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now