ERRORCODE = -4471 occurs in Java application using Db2.

environment

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

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 (´ ・ ω ・ `)

Recommended Posts

ERRORCODE = -4471 occurs in Java application using Db2.
Try using RocksDB in Java
Run Java application in Azure Batch
HTTPS connection using tls1.2 in Java 6
I tried using JWT in Java
[Java] Get data from DB using singleton service in Spring (Boot)
Automatically deploy a Web application developed in Java using Jenkins [Preparation]
Automatically deploy Web applications developed in Java using Jenkins [Tomcat application]
Try using Java framework Nablarch [Web application]
I tried using Elasticsearch API in Java
Map without using an array in java
Try using JSON format API in Java
Generate DB documentation using SchemaSpy in Gradle
Read Felica using RC-S380 (PaSoRi) in Java
Status monitoring of java application using Elasticsearch
Role of JSP in Web application [Java]
Java application development environment created in VM environment
Automatically deploy a web application developed in Java using Jenkins [Spring Boot application]
I tried to make a talk application in Java using AI "A3RT"
About the phenomenon that StackOverflowError occurs in processing using Java regular expressions
Create authentication function in Rails application using devise
ChatWork4j for using the ChatWork API in Java
Partization in Java
[Java] API creation using Jerjey (Jax-rs) in eclipse
Java 9+ application status
Send email using Amazon SES SMTP in Java
Send push notifications using Notification Hubs in Java
Changes in Java 11
[Java] Convert DB code to code value using enum
Rock-paper-scissors in Java
Try using GCP's Cloud Vision API in Java
Try using Sourcetrail (macOS version) in Java code
Match IP addresses using regular expressions in Java
Display "Hello World" in the browser using Java
Display "Hello World" in the browser using Java
Pi in Java
Try using the COTOHA API parsing in Java
NLP4J [001b] Morphological analysis in Java (using kuromoji)
When using a list in Java, java.awt.List comes out and an error occurs
FizzBuzz in Java
Convert JSON and YAML in Java (using Jackson and SnakeYAML)
Delete All from Java SDK in Azure Cosmos DB
[Java] Explains ConcurrentModificationException that occurs in java.util.ArrayList for newcomers
How to convert A to a and a to A using AND and OR in Java
I tried using an extended for statement in Java
Try global hooking in Java using the JNativeHook library
twitter4j java Store the searched tweets in DB (MySQL).
Differences in code when using the length system in Java
Read JSON in Java
Sorting using java comparator
Interpreter implementation in Java
Make Blackjack in Java
Constraint programming in Java
Put java8 in centos7
JAVA DB connection method
NVL-ish guy in Java
Combine arrays in Java
"Hello World" in Java
Callable Interface in Java
Comments in Java source
Azure functions in java