LOAD DATA with [SQLCODE: <-400>:<Fatal error occurred>]... any idea?

Question

Question

Andreas Schneider · Dec 30, 2021

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

Benjamin De Boe · Jan 3, 2022

You can configure your Java Gateway's JVM arguments through the [Gateways] section in the CPF file or, preferably, through the API documented here

1 0

Kevin Chan · Dec 30, 2021

The %qparsets issue that you're seeing has been resolved internally and should be shipped as part of IRIS 2022.1.0

3 0

score 1 · Answer 1 · 2021-12-30T07:51:48-05:00

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.

score 0 · Answer 2 · 2021-12-30T12:16:27-05:00

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

score 2 · Answer 3 · 2021-12-30T11:22:49-05:00

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.

score 0 · Answer 4 · 2021-12-30T12:08:01-05:00

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

score 0 · Answer 5 · 2021-12-30T12:09:48-05:00

Yeah, the JVM encoding problem has been discovered internally and we're actively trying to correct it.

score 0 · Answer 6 · 2022-01-02T13:59:05-05:00

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

score 1 · Answer 7 · 2022-01-03T04:56:34-05:00

Thanks @Benjamin De Boe! It works like a charm ;-)

set srv = $system.external.getServer("%Java Server")
set srv.JVMArgs = "-Dfile.encoding=UTF-8"
set modifiedserver = $system.external.modifyServer(srv)

score 1 · Answer 8 · 2022-01-03T02:13:57-05:00

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.