Question
· Dec 30, 2021

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
Discussion (10)1
Log in or sign up to continue

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 wink

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

  • Replace "\N" which is a MySQL specific token with "" (i.e. the empty string). Due to specific interactions with InterSystem's JDBC clients with IRIS, "" is interpreted as NULL when inserted as a string. 
  • Replace "\\'" (the escaped apostrophe) with "'" (just the single apostrophe) as InterSystem's JDBC clients do not require the escape character (which would otherwise be required in SQL).

This will allow all 6162 rows be inserted.

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.