PreparedStatement addBatch - JNI local ref creation exceeded capacity

From:

"Robert Mark Bram" <robertmarkbram@gmail.com>

Newsgroups:

comp.lang.java.programmer

Date:

25 Jul 2006 20:12:26 -0700

Message-ID:

<1153883546.345513.64330@p79g2000cwp.googlegroups.com>

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
:)