[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3441
  • Last Modified:

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>
0
inversojvo
Asked:
inversojvo
  • 3
  • 3
  • 2
  • +2
2 Solutions
 
CEHJCommented:
>>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
 
gmarinoCommented:
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
 
incercCommented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
mustaccioCommented:
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
 
inversojvoAuthor Commented:
wow, many  of possible solutions. I'll try them tomorrow and give to know then.
Thanx, guys, you are great!!
0
 
inversojvoAuthor Commented:
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
 
mustaccioCommented:
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
 
CEHJCommented:
>>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
 
inversojvoAuthor Commented:
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
 
CEHJCommented:
:-)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now