使用DB2的JDBC语句池没有显著的时间差

Question

我使用的是JDBC db2驱动程序。JT400连接到Application System/400上的db2服务器，这是一个中档计算机系统。

我的目标是在中插入来自IBM大型机外部的三个表，它们将是云实例（例如，Amazon WS）。

为了使演出更好

public class AS400JDBCManagedConnectionPoolDataSource extends AS400JDBCManagedDataSource implements ConnectionPoolDataSource, Referenceable, Serializable {
}

public class AS400JDBCManagedDataSource extends ToolboxWrapper implements DataSource, Referenceable, Serializable, Cloneable {
}

public class CustomerOrderEventHandler extends MultiEventHandler {

    private static Logger logger = LogManager.getLogger(CustomerOrderEventHandler.class);

    //private BasicDataSource establishedConnections = new BasicDataSource();

    //private DB2SimpleDataSource nativeEstablishedConnections = new DB2SimpleDataSource();

    private AS400JDBCManagedConnectionPoolDataSource dynamicEstablishedConnections =
            new AS400JDBCManagedConnectionPoolDataSource();

    private State3 orderState3;
    private State2 orderState2;
    private State1 orderState1;

    public CustomerOrderEventHandler() throws SQLException {
        dynamicEstablishedConnections.setServerName(State.server);
        dynamicEstablishedConnections.setDatabaseName(State.DATABASE);
        dynamicEstablishedConnections.setUser(State.user);
        dynamicEstablishedConnections.setPassword(State.password);
        dynamicEstablishedConnections.setSavePasswordWhenSerialized(true);
        dynamicEstablishedConnections.setPrompt(false);
        dynamicEstablishedConnections.setMinPoolSize(3);
        dynamicEstablishedConnections.setInitialPoolSize(5);
        dynamicEstablishedConnections.setMaxPoolSize(50);
        dynamicEstablishedConnections.setExtendedDynamic(true);
        Connection connection = dynamicEstablishedConnections.getConnection();
        connection.close();
    }

    public void onEvent(CustomerOrder orderEvent){
        long start =  System.currentTimeMillis();
        Connection dbConnection = null;
        try {
            dbConnection = dynamicEstablishedConnections.getConnection();
            long connectionSetupTime = System.currentTimeMillis() - start;

            state3 = new State3(dbConnection);
            state2 = new State2(dbConnection);
            state1 = new State1(dbConnection);

            long initialisation = System.currentTimeMillis() - start - connectionSetupTime;

            int[] state3Result = state3.apply(orderEvent);
            int[] state2Result = state2.apply(orderEvent);
            long state1Result = state1.apply(orderEvent);

            dbConnection.commit();

            logger.info("eventId="+ getEventId(orderEvent) +
                    ",connectionSetupTime=" + connectionSetupTime +
                    ",queryPreCompilation=" + initialisation +
                    ",insertionOnlyTimeTaken=" +
                    (System.currentTimeMillis() - (start + connectionSetupTime + initialisation)) +
                    ",insertionTotalTimeTaken=" + (System.currentTimeMillis() - start));
        } catch (SQLException e) {
            logger.error("Error updating the order states.", e);
            if(dbConnection != null) {
                try {
                    dbConnection.rollback();
                } catch (SQLException e1) {
                    logger.error("Error rolling back the state.", e1);
                }
            }
            throw new CustomerOrderEventHandlerRuntimeException("Error updating the customer order states.", e);
        }
    }

    private Long getEventId(CustomerOrder order) {
        return Long.valueOf(order.getMessageHeader().getCorrelationId());
    }
}

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class State2 extends State {

    private static Logger logger = LogManager.getLogger(DetailState.class);

    Connection connection;
    PreparedStatement preparedStatement;

    String detailsCompiledQuery = "INSERT INTO " + DATABASE + "." + getStateName() +
            "(" + DetailState.EVENT_ID + ", " +
            State2.ORDER_NUMBER + ", " +
            State2.SKU_ID + ", " +
            State2.SKU_ORDERED_QTY + ") VALUES(?, ?, ?, ?)";

    public State2(Connection connection) throws SQLException {
        this.connection = connection;
        this.preparedStatement = this.connection.prepareStatement(detailsCompiledQuery); // this is taking ~200ms each time
        this.preparedStatement.setPoolable(true); //might not be required, not sure
    }

    public int[] apply(CustomerOrder event) throws StateException {

        event.getMessageBody().getDetails().forEach(detail -> {
            try {
                preparedStatement.setLong(1, getEventId(event));
                preparedStatement.setString(2, getOrderNo(event));
                preparedStatement.setInt(3, detail.getSkuId());
                preparedStatement.setInt(4, detail.getQty());
                preparedStatement.addBatch();
            } catch (SQLException e) {
                logger.error(e);
                throw new StateException("Error setting up data", e);
            }
        });

        long startedTime = System.currentTimeMillis();
        int[] inserted = new int[0];
        try {
            inserted = preparedStatement.executeBatch();
        } catch (SQLException e) {
            throw new StateException("Error updating allocations data", e);
        }
        logger.info("eventId="+ getEventId(event) +
                ",state=details,insertionTimeTaken=" + (System.currentTimeMillis() - startedTime));
        return inserted;
    }

    @Override
    protected String getStateName() {
        return properties.getProperty("state.order.details.name");
    }
}

计时的度量如下所示，

对于第一个事件，为3个表创建PreparedStatement需要580ms。

{"timeMillis":1489982655836,"thread":"ScalaTest-run-running-CustomerOrderEventHandlerSpecs","level":"INFO","loggerName":"com.xyz.customerorder.events.handler.CustomerOrderEventHandler",
"message":"eventId=1489982654314,connectionSetupTime=1,queryPreCompilation=580,insertionOnlyTimeTaken=938,insertionTotalTimeTaken=1519","endOfBatch":false,"loggerFqcn":"org.apache.logging.log4j.spi.AbstractLogger","threadId":1,"threadPriority":5}

对于第二个事件，需要470ms来准备3个表的语句，这比第一个事件少，但只有<100ms，我认为它要少得多，因为它甚至不应该进行编译。

{"timeMillis":1489982667243,"thread":"ScalaTest-run-running-PurchaseOrderEventHandlerSpecs","level":"INFO","loggerName":"com.xyz.customerorder.events.handler.CustomerOrderEventHandler",
"message":"eventId=1489982665456,connectionSetupTime=0,queryPreCompilation=417,insertionOnlyTimeTaken=1363,insertionTotalTimeTaken=1780","endOfBatch":false,"loggerFqcn":"org.apache.logging.log4j.spi.AbstractLogger","threadId":1,"threadPriority":5}

Q1)DB2 JDBC驱动程序在多个连接之间缓存语句吗？因为我在准备语句时没有看到太大的差异。（请参见示例，第一个使用~600ms，第二个使用~500ms)

ODP=开放数据路径

SQL包

用于JDBC和SQLJ语句缓存的IBM数据服务器驱动程序

Answer 1

关于语句缓存需要注意的几个重要事项：

• 因为语句对象是给定连接的子对象，所以一旦连接关闭，所有子对象（例如所有语句对象）也必须关闭。
• 不能将一个连接中的语句与另一个连接相关联。
• 语句池可以由给定的JDBC驱动程序完成，也可以不完成。语句池也可以由连接管理层（即应用程序服务器）执行
• 根据JDBC规范，statement.ispoolable()==false和preparedstatement.ispoolable()==true的默认值，但是这个标志只是对JDBC驱动程序的提示。规范不能保证会发生语句池。

首先，我不确定JT400驱动程序是否进行语句缓存。您在问题评论中引用的文档“从Java和WebSphere优化对DB2 for i5/OS的访问”专门针对在WebSphere application server中使用JT400 JDBC驱动程序，在幻灯片#3中，它指出语句缓存来自WebSphere连接管理层，而不是本地JDBC驱动层。鉴于此，我将假设JT400 JDBC驱动程序本身不支持语句缓存。

<dataSource jndiName="jdbc/myDS" statementCacheSize="10">
    <jdbcDriver libraryRef="DB2iToolboxLib"/>
    <properties.db2.i.toolbox databaseName="YOURDB" serverName="localhost"/>
</dataSource>

<library id="DB2iToolboxLib">
    <fileset dir="/path/to/jdbc/driver/dir" includes="jt400.jar"/>
</library>