PreparedStatement addBatch - JNI local ref creation exceeded capacity

[Robert Mark Bram]

Hi All,

I have got some code that is using one PreparedStatement instance, but
calling addBatch() on that instance thousands of times (2775) before
calling executeBatch. This has worked ok but this morning I found a
"JNI local ref creation exceeded capacity" error which crashed the
server it was running on.

I am wondering if there is a safer way of using PreparedStatement and
addBatch(), or should I switch to using individual execute() calls...
which were rather slower on the whole.

protected void insertIntoBatch(
final ImportBuffer detailRecord,
final ReferenceDataBean refDataBean)
throws Exception {
// Related reference data consists of payscale area, group and level
String personnelArea = formatForSql(detailRecord.nextToken());
String area = formatForSql(detailRecord.nextToken());
String group = formatForSql(detailRecord.nextToken());
String level = formatForSql(detailRecord.nextToken());
String description = formatForSql(detailRecord.nextToken());

// Determine what type of reference data this is, depending on the
// values of the component parts.
String type = "";
if (StringUtils.isNotBlank(area)
&& StringUtils.isBlank(group)
&& StringUtils.isBlank(level)) {
type = refDataBean.getType();
} else if (StringUtils.isNotBlank(area)
&& StringUtils.isNotBlank(group)
&& StringUtils.isBlank(level)) {
type = PAYSCALE_GROUP_TYPE;
} else if (StringUtils.isNotBlank(area)
&& StringUtils.isNotBlank(group)
&& StringUtils.isNotBlank(level)) {
type = PAYSCALE_LEVEL_TYPE;
}
final int third = 3;
final int fourth = 4;
final int fifth = 5;
final int sixth = 6;
insertStatement.setString(1, type);
insertStatement.setString(2, personnelArea);
insertStatement.setString(third, area);
insertStatement.setString(fourth, group);
insertStatement.setString(fifth, level);
insertStatement.setString(sixth, description);
insertStatement.addBatch();
}

protected void executeBatchInsert() throws SQLException {
insertStatement.executeBatch();
}


This has been working fine for quite a while, but just this morning I
saw the following error pop up, which crashed our whole server.

***ALERT: JNI local ref creation exceeded capacity (creating: 2775,
limit: 18).
at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.SQLBindStringArray(Native
Method)
at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeBatch(DB2PreparedStatement.java(Compiled
Code))
at
com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.pmiExecuteBatch(WSJdbcStatement.java:866)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeBatch(WSJdbcStatement.java:341)
at
com.cml.etech.eForms.bus.referenceData.importer.RelatedReferenceDataImporter.executeBatchInsert(RelatedReferenceDataImporter.java:133)
at
com.cml.etech.eForms.bus.referenceData.importer.BaseReferenceDataImporter.processFile(BaseReferenceDataImporter.java(Compiled
Code))
at
com.cml.etech.eForms.bus.referenceData.importer.BaseReferenceDataImporter.importReferenceDataFromFile(BaseReferenceDataImporter.java:64)
at
com.cml.etech.eForms.bus.referenceData.ImportReferenceDataCommandBean.importRefData(ImportReferenceDataCommandBean.java:622)
at
com.cml.etech.eForms.bus.referenceData.ImportReferenceDataCommandBean.importReferenceData(ImportReferenceDataCommandBean.java:284)
at
com.cml.etech.eForms.bus.referenceData.ImportReferenceDataCommandBean.doProcess(ImportReferenceDataCommandBean.java:167)
... (more frames not shown)

Any advice would be most appreciated!

Rob

 

[Frank Langelage]

This has been working fine for quite a while, but just this morning I

saw the following error pop up, which crashed our whole server.

***ALERT: JNI local ref creation exceeded capacity (creating: 2775,
limit: 18).

From this message I would say: add up to 18 inserts to the prepared
statement and execute before putting the next into it.

 

[Chris Uppal]

***ALERT: JNI local ref creation exceeded capacity (creating: 2775,
limit: 18).
at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.SQLBindStringArray(Native
Method)

I can't comment on your use of JDBC, but the error message sounds like some
dodgy JNI code in the IBM JDBC driver. Unless you have to clout to get IBM to
fix it ;-) it's probably best to work around the bug. E.g. put a limit (say
100) on the size of the batch -- that should gain you most of the performance
advantage of batching without triggering the bug.

(BTW, the limit of 18 mentioned in the error message is a soft limit. JNI will
automatically increase the number of local refs beyond the currently configured
maximum up to some internally-defined limit -- in this case, apparently, 2774)

-- chris

 

[Robert Mark Bram]

Hi All,

I got some feedback from IBM regarding this. It is rather confusing
though - can I turn off this warning? There is a suggestion that JNI
calls are available - can these be called from Java?

"The "**ALERT: JNI local ref creation exceeded capacity" messages do
not indcate an error. It is warning from the JVM that your application
has more local references than can be contained in the storage that you
first allocated for them. The message suggests that you might want to
check your JNI code to see why you have many outstanding local
references, and decide whether it would be better if you managed them
yourself. Normally, it is assumed that a function or method will not
hold many references at the same time. If, however, you have designed
your code to hold many references, you can ignore the message. The JVM
does not stop storing local references when this message appears; it
extends the storage capacity, as necessary. The execution of your
application is not affected in any way by this message, except for a
small processing overhead. If your application is designed this way and
the message becomes annoying, or if you are not willing to accept the
overhead of recreating stack frames, JNI calls are available that
enable you to increase the capacity of the local reference storage.

The JNI specification does not set the local reference capacity of a
JVM, nor does it, require (or deny) use of this message. Therefore,
this message might or might not appear. If it does, it might appear at
different times for different JVMs.

You can control the storage capacity and freeing of local references,
but you cannot control whether they are created or not. "