With the IBM Data Server Driver for JDBC and SQLJ
(JCC driver) property useCachedCursor=true (the default value),
the next() method of ResultSet might get an SqlException with
ERRORCODE=-4228 after a table being queried has been altered.
You can reproduce the error using the following Java program:
import java.sql.*;
public class Repro
{
public static void main(String[] args)
{
if (args.length != 3)
{
System.out.println("Usage: java Repro
<connectionUrl> <username> <password>");
System.exit(1);
}
final String connectionUrl = args[0];
final String username = args[1];
final String password = args[2];
Connection jdbcConnection = null;
try
{
Class.forName("com.ibm.db2.jcc.DB2Driver");
jdbcConnection =
DriverManager.getConnection(connectionUrl,
username, password);
PreparedStatement stmt =
jdbcConnection.prepareStatement(
"select col1 from tab1");
ResultSet set = stmt.executeQuery();
while (set.next())
{
System.out.println("Col1: " + set.getInt(1));
}
System.out.println("START OF NOT NULL CHANGE");
Thread.sleep(20000);
System.out.println("END OF NOT NULL CHANGE");
set = stmt.executeQuery();
while (set.next())
{
System.out.println("Location: " +
set.getInt(1));
}
stmt.close();
}
catch (Exception e)
{
if (e.getMessage() != null)
{
System.out.println(e.getMessage());
}
e.printStackTrace();
}
finally
{
if (jdbcConnection != null)
{
try
{
jdbcConnection.close();
}
catch (Exception e) {}
}
}
}
}
To reproduce the error you can do the following steps.
Connect to your database and create a table as follows:
db2 "create table tab1(col1 int)"
db2 "insert into tab1 values (0), (1), (2)"
Then in another window run the Java program
(except replacing <hostname>, <portnumber>, <database>,
<username> and <password> with appropriate values)
as follows:
java Repro
"jdbc:db2://<hostname>:<portnumber>/<database>:
useCachedCursor=true;" <username> <password>
It outputs the following:
Col1: 0
Col1: 1
Col1: 2
START OF NOT NULL CHANGE
Then in the first window alter the table as follows:
db2 "alter table tab1 alter column col1 set not null"
After that, when the Java program runs to completion its output
includes the following:
END OF NOT NULL CHANGE
Location: 255
[jcc][t4][XXXX][XXXXX][X.XX.XXX] Query processing has been
terminated due to error on the server. ERRORCODE=-4228,
SQLSTATE=null
com.ibm.db2.jcc.am.SqlException:
[jcc][t4][XXXX][XXXXX][X.XX.XXX] Query processing has been
terminated due to error on the server. ERRORCODE=-4228,
SQLSTATE=null
[...]
at
com.ibm.db2.jcc.am.ResultSet.next(ResultSet.java:XXX)
at Repro.main(Repro.java:XXX)
This APAR makes the following changes if the database server is
DB2 for Linux, UNIX and Windows:
1. The default value of the JCC property useCachedCursor is
false.
2. If the JCC property deferPrepares is true then the JCC driver
always behaves as if the JCC property useCachedCursor is
false.
This APAR makes no change if the database server is other than
DB2 for Linux, UNIX and Windows. |
