ERRORCODE = -4471 occurs in Java application using Db2.

environment

The main software versions used in writing this article are as follows.

• javac 11.0.4
• openjdk version "11.0.4" 2019-07-16
• IBM DB2 Developer-C Edition 11.5 (Docker/wsl2)
• IBM Data Server Driver for JDBC and SQLJ 4.26.14

Event

The code below gets all ʻEMPNOs from a table called ʻEMP and outputs them to standard output.

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

public class Main {
    public static void main(String[] args) {
        String url = "jdbc:db2://<hostname>:<port>/<dbname>";
        String user = "<user>";
        String password = "<password>";

        try (Connection con = DriverManager.getConnection(url, user, password)) {
            con.setAutoCommit(false);

            PreparedStatement ps = con.prepareStatement("SELECT * FROM EMP");
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                System.out.println(rs.getString("EMPNO"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

When I compile and run this source code, after printing all ʻEMPNO` to standard output, I get the following exception:

com.ibm.db2.jcc.am.SqlException: [jcc][t4][10251][10308][4.26.14]Java while transaction in progress on connection.sql.Connection.close()Was requested.
The transaction remains active and the connection cannot be closed. ERRORCODE=-4471, SQLSTATE=null
	at com.ibm.db2.jcc.am.b7.a(b7.java:794)
	at com.ibm.db2.jcc.am.b7.a(b7.java:66)
	at com.ibm.db2.jcc.am.b7.a(b7.java:133)
	at com.ibm.db2.jcc.am.Connection.checkForTransactionInProgress(Connection.java:1484)
	at com.ibm.db2.jcc.t4.b.checkForTransactionInProgress(b.java:7581)
	at com.ibm.db2.jcc.am.Connection.closeResourcesX(Connection.java:1507)
	at com.ibm.db2.jcc.am.Connection.closeX(Connection.java:1493)
	at com.ibm.db2.jcc.am.Connection.close(Connection.java:1470)
	at Main.main(Main.java:22)

__java.sql.Connection.close () can be understood because try-catch-with-resource calls it without permission, but in the first place," transaction remains active and connection cannot be closed ". What a mess ??? __.

Cause

In Db2, you need to commit the transaction with COMMIT or ROLLBACK before closing the DB connection. COMMIT and ROLLBACK are done only when you make changes to the DB data such as ʻINSERTandDELETE, and COMMITandROLLBACK are used for SELECTthat only refers to the data. I'm sure there are many people who don't pay attention to-, but at least in Db2, you need to be aware of transactions even in the case ofSELECT`.

In the source code above, autocommit was turned off (con.setAutoCommit (false)). If auto-commit = true, the jdbc driver will automatically commit when you call Connection :: close, but if auto-commit = false, the programmer will explicitly Connection :: commit or . If you do not call Connection :: rollback, the transaction will not be committed. In other words, in the source code above, we turned off autocommit, so we tried to close the connection without confirming the transaction, resulting in ʻERRORCODE = -4471`.

Countermeasures

--Call Connection :: commit or Connection :: rollback to explicitly complete the transaction --Set auto-commit = true and throw the transaction completion to the JDBC Driver.

Is this the countermeasure (´ ・ ω ・ ｀)