CODEWITHSUNDEEP
javadb2ibm-midrange
Radenixlol
Radenixlol

Reputation: 11

JTOpen Bulk configuration

I wanted to ask something related to the Java AS400 connection driver, that is, JTOpen, and it is about whether it has a URL parameter similar to that of PostgreSQL:

jdbc:postgresql://localhost:5432/db?reWriteBatchedInserts=true

That allows doing SQL inserts like these:

INSERT INTO table (name, value) values ​​("name1", "value1"), ("name2", "value2"), ("name3", "value3")

instead of doing them like this:

INSERT INTO table (name, value) values ​​("name1", "value1")
INSERT INTO table (name, value) values ​​("name2", "value2")
INSERT INTO table (name, value) values ​​("name3", "value3")

I am looking for an answer regarding this, because I am looking to optimize inserts in a database, I just want to know if there is a parameter that I can provide to make this possible, or if There is no parameter or where I could ask to get an answer, I have been searching for days and I can't find anything.

Upvotes: 1

Views: 62

Answers (2)

Charles
Charles

Reputation: 23813

The JTOpen driver (used to?) have a setting

"use block update" - This property allows the toolbox JDBC driver to use a block update mode when updating blocks of data into the database. This is an optimized version of the batch update. Default - false

But it doesn't show in the latest version of the documentation. A quick search of the code shows it's still an acceptable parameter, but I don't see where it's actually used.

In any event,

INSERT INTO table (name, value) values ​​("name1", "value1"), ("name2", "value2"), ("name3", "value3")

Is a perfectly acceptable statement, and has been for a long time without needing to modify the connection properties.

Additionally, JTOpen supports the standard JDBC AddBatch() method and it's supporting methods as shown in this answer.

Upvotes: 0

AndrewL
AndrewL

Reputation: 3455

It is 20 years since I used an AS400, so I don't know what is possible in its dialect of SQL and its JDBC Driver, so this is guesswork:

1. The multiple insert sytnax

If that syntax of supporting multiple record insert is supported in the AS400 SQL Shell, then I don't see why a Java driver cannot handle it. The JDBC driver would just see this as a very long statement.

2. Batch support

I would think it likely that your JDBC driver will support the Batch method of inserting. You prepare a statement ready for just one set of column values, then supply a collection of data that are sent in batches to the database. This is more aligned to how a java program would send data and may give equally better performance than discrete inserts. Read a tutorial here: https://www.baeldung.com/spring-jdbc-batch-inserts

Upvotes: 0

Related Questions