LOAD DATA with [SQLCODE: <-400>:<Fatal error occurred>]... any idea?
Hi all,
I try to build a little project for the dataset contest, you can find it here on github: Openflights dataset
My plan is to use just plain SQL. So I've made a few experiments with the new feature LOAD DATA.
Unfortunately all loads produce errors like:
Error: [SQLCODE: <-400>:<Fatal error occurred>]
[Error: <<UNDEFINED>zExecute+83^%sqlcq.OPENFLIGHTS.cls4.1 *%qparsets>]
[Location: <ServerLoop>]
The statements I use looks like this:
This happend with the preview release "IRIS Version 2021.2.0.617". While this error occurs, most rows are written to the database, but some rows are missing. This happend with all 9 datafiles in my project... all statements I've used are here.
I'm connected to IRIS via JDBC
Any ideas? Thanks in advance!
Andreas
Product version: IRIS 2021.2
Execute the same query in ObjectScript process and check %objlasterror - maybe there would be additional info.
For a given table does this happen on the same row? Same rownumber? Random row? If it's the same row - which one?
Calling @Benjamin De Boe.
Thank you for the suggestions!
My problem seemed to consist of at least two parts
1. There are characters in the files that LOAD DATA can not process
2. LOAD DATA is currently terminated with an error even in case of success.
I think it would be a helpful enhancement for the LOAD DATA feature if erroneous lines e.g. would be redirected to a textfile for later analysis.
Maybe an option for 2022.2
To resolve the issues with airlines.dat, do the following
Add to the JVM arguments of %Java Sever, "-Dfile.encoding=UTF-8" without the quotes
Edit airlines.dat and make the following changes
This will allow all 6162 rows be inserted.
Thanks! I have made the changes you suggested and I could load all rows. My encoding problem is also gone with the JVM Param
The LOAD DATA statement still finish with error SQLCODE: <-400>:<Fatal error occurred>] ... *%qparsets>] but the data are there, but you have pointed out that this problem will be solved in version 2022.1.0
Yeah, the JVM encoding problem has been discovered internally and we're actively trying to correct it.
Kevin, can you give me tip please how I can set this JVM Argument via console? I want to integrate this setting in my docker image.
Thanks!
Andreas
You can configure your Java Gateway's JVM arguments through the [Gateways] section in the CPF file or, preferably, through the API documented here
Thanks @Benjamin De Boe! It works like a charm ;-)
set srv.JVMArgs = "-Dfile.encoding=UTF-8"
set modifiedserver = $system.external.modifyServer(srv)
The %qparsets issue that you're seeing has been resolved internally and should be shipped as part of IRIS 2022.1.0
Thanks @Kevin Chan for helping out (he's the author of the parser piece of
LOAD DATA
;-) )In addition to his comments on the encoding issues, you can also take a look at the
%SQL_Diag.Result
table which will likely have more detailed error info than what we're able to return through SQL semantics (though we're still working on ways to make it clearer). There's also a%SQL_Diag.Message
table with row-level errors, which can help you figure out the reason for individual row failures.Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue