75 ways to demystify DB2: #7 : Tech Tip: How to disable multirowinsert support in DB2 JCC application?

Body

In DB2 JDBC applications, when you execute INSERT or MERGE statements that use parameter markers in a batch, if the data server supports multi-row INSERT, the IBM Data Server Driver for JDBC and SQLJ can transform the batch INSERT or MERGE operations into multi-row INSERT statements by default.

If INSERT FROM SELECT statements are executed in a batch, the driver will throw a BatchUpdateException, by default and its a restriction.

To avoid the exception, JCC datasource property enableMultiRowInsertSupport must be set to false.

The property can be set to false as below:

In application code: ((com.ibm.db2.jcc.DB2BaseDataSource)ds).setEnableMultirowInsertSupport(false);

(or)

In application server datasource properties, set enableMultiRowInsertSupport=false for the specific datasource.

(or)

you can set the JVM property:  -Ddb2.jcc.override.enableMultirowInsertSupport=false  in the configuration files. For example in Tomcat, tomcat.ini file.

Refer:

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_r0052040.html

Thank you for reading!