Severe Problem using JDBC PreparedStatement with IBM DB2 Universal JDBC Driver as Type 4

[Dani]

Hi everyone

Description of the problem:
Using a PreparedStatement to write down an integer (int) plus a
timestamp for testing purposes. When read out again the integer looks
very different. We found that it was shifted three Bytes to the left,
i.e. 4 becomes hex 4000000 which is 67108864 in decimal base. This
means that the value written and the value read sometimes do not
match, which is of course inacceptable for all real world
applications.

The problem only arises when we use PreparedStatement in JDBC and when
used as Type 4 JDBC driver. It never happened as Type 2 driver.
The problem happened irregularly, with absolutely no recognizable
schema.

This is considered a severe flaw and prevents us from rolling this new
JDBC driver out.

Any help, especially from IBM, would be greatly appreciated.

Regards,
Dani

Code Snippet:

// SQL Stmts
final String DELETE_STMT = "DELETE FROM " + TEST_TABLE_NAME + "
WHERE NR = " + KEY;
final String INSERT_STMT = "INSERT INTO " + TEST_TABLE_NAME
+ " (NR, F_TIMESTAMP) VALUES(?, ?)";
final String SELECT_STMT = "SELECT F_TIMESTAMP FROM " +
TEST_TABLE_NAME
+ " WHERE NR = " + KEY;

Statement statement = null;
PreparedStatement ps = null;
try {
statement = conn.createStatement();

// delete and insert the data
statement.executeUpdate(DELETE_STMT);
ps = conn.prepareStatement(INSERT_STMT);
ps.setInt(1, KEY);
ps.setTimestamp(2, now);
ps.execute();

// re-read the data
ResultSet resultSet = statement.executeQuery(SELECT_STMT);
if (resultSet.next() != true) {
res.setFailure("Select returned no data.");
} else {
Configuration:

We are using DB2 Connect to connect to the DB on the z/OS.

[ibm][db2][jcc] Driver: IBM DB2 JDBC Universal Driver Architecture
1.2.117
[ibm][db2][jcc] Compatible JRE versions: { 1.3, 1.4 }
[ibm][db2][jcc] Target server licensing restrictions: { z/OS: enabled;
SQLDS: enabled; iSeries: enabled; DB2 for Unix/Windows: enabled;
Cloudscape: enabled }
[ibm][db2][jcc] Java Runtime Environment version 1.4.1_05
[ibm][db2][jcc] Java Runtime Environment vendor = Sun Microsystems
Inc.
[ibm][db2][jcc] Java Virtual Machine implementation version =
1.4.1_05-b01

[ibm][db2][jcc][Connection@e3b895] Successfully connected to server
jdbc:db2://gateway:50000/DB2U
[ibm][db2][jcc][Connection@e3b895] Database product name: DB2
[ibm][db2][jcc][Connection@e3b895] Database product version: DSN07011
[ibm][db2][jcc][Connection@e3b895] Driver name: IBM DB2 JDBC Universal
Driver Architecture
[ibm][db2][jcc][Connection@e3b895] Driver version: 1.2.117
[ibm][db2][jcc][Connection@e3b895] DB2 Correlator:
GA2444A0.GB40.00FABEC8075D

Trace:

[ibm][db2][jcc][Thread:main][Connection@e3b895] prepareStatement
(INSERT INTO SAIUUSR.SA_I_JDBCTEST (NR, F_TIMESTAMP) VALUES(?, ?))
called
[ibm][db2][jcc][Thread:main][Connection@e3b895] prepareStatement ()
returned PreparedStatement@1ce2dd4
[ibm][db2][jcc][Thread:main][PreparedStatement@1ce2dd4] setInt (1, 4)
called
[ibm][db2][jcc][Thread:main][PreparedStatement@1ce2dd4] setTimestamp
(2, 2004-02-16 15:43:25.218) called
[ibm][db2][jcc][Thread:main][PreparedStatement@1ce2dd4] execute ()
called
[ibm][db2][jcc][t4] Request.flush() called at 2004-2-16 15:43:25
Thread: main Tracepoint: 1
[ibm][db2][jcc][t4] SEND BUFFER: PRPSQLSTT (ASCII)
(EBCDIC)
[ibm][db2][jcc][t4] 0 1 2 3 4 5 6 7 8 9 A B C D E F
0123456789ABCDEF 0123456789ABCDEF
[ibm][db2][jcc][t4] 0000 0053D0510001004D 200D00442113C4C2
..S.Q...M ..D!... ..}....(......DB
[ibm][db2][jcc][t4] 0010 F2E4404040404040 4040404040404040
...@@@@@@@@@@@@@@ 2U
[ibm][db2][jcc][t4] 0020 D5E4D3D3C9C44040 4040404040404040
.......@@@@@@@@@@ NULLID
[ibm][db2][jcc][t4] 0030 4040E2E8E2D3C8F2 F0F0404040404040
@@........@@@@@@ SYSLH200
[ibm][db2][jcc][t4] 0040 404040405359534C 564C303100010005
@@@@SYSLVL01.... ...<.<......
[ibm][db2][jcc][t4] 0050 2116F1004ED04300 0100482414004049
!...N.C...H$..@I ..1.+}........ .
[ibm][db2][jcc][t4] 0060 4E5345525420494E 544F205341495555 NSERT
INTO SAIUU +......+.|......
[ibm][db2][jcc][t4] 0070 53522E53415F495F 4A44424354455354
SR.SA_I_JDBCTEST ................
[ibm][db2][jcc][t4] 0080 20284E522C20465F 54494D455354414D (NR,
F_TIMESTAM ..+.......(....(
[ibm][db2][jcc][t4] 0090 50292056414C5545 53283F2C203F2900 P)
VALUES(?, ?). &....<..........
[ibm][db2][jcc][t4] 00A0 000053D041000200 4D200800442113C4
...S.A...M ..D!.. ...}....(......D
[ibm][db2][jcc][t4] 00B0 C2F2E44040404040 4040404040404040
....@@@@@@@@@@@@@ B2U
[ibm][db2][jcc][t4] 00C0 40D5E4D3D3C9C440 4040404040404040
@......@@@@@@@@@ NULLID
[ibm][db2][jcc][t4] 00D0 404040E2E8E2D3C8 F2F0F04040404040
@@@........@@@@@ SYSLH200
[ibm][db2][jcc][t4] 00E0 4040404040535953 4C564C3031000100
@@@@@SYSLVL01... ...<.<.....
[ibm][db2][jcc][t4] 00F0 052146010053D051 0003004D200B0044
..!F..S.Q...M ..D ......}....(....
[ibm][db2][jcc][t4] 0100 2113C4C2F2E44040 4040404040404040
!.....@@@@@@@@@@ ..DB2U
[ibm][db2][jcc][t4] 0110 40404040D5E4D3D3 C9C4404040404040
@@@@......@@@@@@ NULLID
[ibm][db2][jcc][t4] 0120 404040404040E2E8 E2D3C8F2F0F04040
@@@@@@........@@ SYSLH200
[ibm][db2][jcc][t4] 0130 4040404040404040 5359534C564C3031
@@@@@@@@SYSLVL01 ...<.<..
[ibm][db2][jcc][t4] 0140 000100052105F100 42D0430003003C24
.....!...B.C...<$ ......1..}......
[ibm][db2][jcc][t4] 0150 12001300100976D0 03000425001A0671
.......v....%...q .......}........
[ibm][db2][jcc][t4] 0160 E4D000010025147A 0000000000040032
......%.z.......2 U}.....:........
[ibm][db2][jcc][t4] 0170 3030342D30322D31 362D31352E34332E
004-02-16-15.43. ................
[ibm][db2][jcc][t4] 0180 32352E3231383030 30000AD001000400
25.218000....... ...........}....
[ibm][db2][jcc][t4] 0190 04200E . .
...
[ibm][db2][jcc][t4]
[ibm][db2][jcc][t4] Reply.fill() called at 2004-2-16 15:43:25 Thread:
main Tracepoint: 2
[ibm][db2][jcc][t4] RECEIVE BUFFER: SQLCARD (ASCII)
(EBCDIC)
[ibm][db2][jcc][t4] 0 1 2 3 4 5 6 7 8 9 A B C D E F
0123456789ABCDEF 0123456789ABCDEF
[ibm][db2][jcc][t4] 0000 0056D04300010050 2408000000000030
..V.C...P$......0 ..}....&........
[ibm][db2][jcc][t4] 0010 3030303044534E20 2020202000444232 0000DSN
.DB2 ......+.........
[ibm][db2][jcc][t4] 0020 5520202020202020 2020202020202000 U
. ................
[ibm][db2][jcc][t4] 0030 0000000000000001 000000FFFFFFFF00
................. ................
[ibm][db2][jcc][t4] 0040 0000000000000020 2020202020202020 .......
................
[ibm][db2][jcc][t4] 0050 202000000000001C D053000200160035
........S.....5 ........}.......
[ibm][db2][jcc][t4] 0060 0006119C01F40006 119D00000006119E
................. .....4..........
[ibm][db2][jcc][t4] 0070 01F40047D0430002 00412411FF020000
....G.C...A$..... .4..}...........
[ibm][db2][jcc][t4] 0080 0000000400000000 000000F001000000
................. ...........0....
[ibm][db2][jcc][t4] 0090 0000000000000000 000000FF00000000
................. ................
[ibm][db2][jcc][t4] 00A0 1A00000000000000 890101F400000000
................. ........i..4....
[ibm][db2][jcc][t4] 00B0 0000000000000000 FF0026D052000300
...........&.R... ...........}....
[ibm][db2][jcc][t4] 00C0 2022180006114900 0000162110C4C2F2
"....I....!.... .............DB2
[ibm][db2][jcc][t4] 00D0 E440404040404040 4040404040404000
..@@@@@@@@@@@@@@. U .
[ibm][db2][jcc][t4] 00E0 56D0430003005024 0800000000003030
V.C...P$......00 .}....&.........
[ibm][db2][jcc][t4] 00F0 30303044534E2020 2020200044423255 000DSN
.DB2U .....+..........
[ibm][db2][jcc][t4] 0100 2020202020202020 2020202020200000
.. ................
[ibm][db2][jcc][t4] 0110 0000000000000100 0000FFFFFFFF0000
................. ................
[ibm][db2][jcc][t4] 0120 0000000000002020 2020202020202020 ......
................
[ibm][db2][jcc][t4] 0130 2000000000002BD0 5200040025220C00
......+.R...%".. .......}........
[ibm][db2][jcc][t4] 0140 0611490004000521 150100162110C4C2
...I....!....!... ..............DB
[ibm][db2][jcc][t4] 0150 F2E4404040404040 4040404040404040
...@@@@@@@@@@@@@@ 2U
[ibm][db2][jcc][t4] 0160 000BD00300040005 2408FF
.........$.. ..}........
[ibm][db2][jcc][t4]
[ibm][db2][jcc][ParameterMetaData@12f0999] BEGIN
TRACE_PARAMETER_META_DATA
[ibm][db2][jcc][ParameterMetaData@12f0999] Parameter meta data for
statement Statement@1ce2dd4
[ibm][db2][jcc][ParameterMetaData@12f0999] Number of parameter
columns: 2
isDescribed=true[ibm][db2][jcc][ParameterMetaData@12f0999] Column 1: {
label=, name=, type name=INTEGER, type=4, nullable=0, precision=10,
scale=0, schema name=, table name=, writable=true, sqlPrecision=0,
sqlScale=0, sqlLength=4, sqlType=496, sqlCcsid=0, sqlName=null,
sqlLabel=null, sqlUnnamed=<null>, sqlComment=<null>,
sqludtxType=<null>, sqludtRdb=<null>, sqludtSchema=<null>,
sqludtName=<null>, sqlxKeymem=<null>, sqlxGenerated=<null>,
sqlxParmmode=1, sqlxCorname=<null>, sqlxName=<null>,
sqlxBasename=<null>, sqlxUpdatable=<null>, sqlxSchema=<null>,
sqlxRdbnam=<null>, internal type=4, is locator parameter=false }
[ibm][db2][jcc][ParameterMetaData@12f0999] Column 2: { label=, name=,
type name=TIMESTAMP, type=93, nullable=1, precision=26, scale=6,
schema name=, table name=, writable=true, sqlPrecision=0, sqlScale=0,
sqlLength=26, sqlType=393, sqlCcsid=500, sqlName=null, sqlLabel=null,
sqlUnnamed=<null>, sqlComment=<null>, sqludtxType=<null>,
sqludtRdb=<null>, sqludtSchema=<null>, sqludtName=<null>,
sqlxKeymem=<null>, sqlxGenerated=<null>, sqlxParmmode=1,
sqlxCorname=<null>, sqlxName=<null>, sqlxBasename=<null>,
sqlxUpdatable=<null>, sqlxSchema=<null>, sqlxRdbnam=<null>, internal
type=93, is locator parameter=false }
[ibm][db2][jcc][ParameterMetaData@12f0999] { sqldHold=0, sqldReturn=0,
sqldScroll=0, sqldSensitive=0, sqldFcode=0, sqldKeytype=0,
sqldRdbnam=null, sqldSchema=null }
[ibm][db2][jcc][ParameterMetaData@12f0999] END
TRACE_PARAMETER_META_DATA
[ibm][db2][jcc][Thread:main][PreparedStatement@1ce2dd4] execute ()
returned false

[Dani]

> The problem only arises when we use PreparedStatement in JDBC and when

used as Type 4 JDBC driver. It never happened as Type 2 driver.
The problem happened irregularly, with absolutely no recognizable
schema.

Someone pointed me to the question whether this flaw happens while
writing to the database or while reading from the database.

I have direct access to the database without going through JDBC and
thus can definitely say that the problem comes from writing to the
database.

Possibly this information can help the cracks to get the right
direction for the quest.

Thanks for any help.

Regards,
Dani

[joerg]

Hi,
you are using an older revision of the JDBC-Driver. The actual one is
of Version 1.5.54 DB2 V8.1.4.
It seems to me that this is quite similiar to one of the last APARS on
DB2, so please have a look at the IBM Patch-List for DB2.

Joerg

[Dani]

> you are using an older revision of the JDBC-Driver. The actual one is

of Version 1.5.54 DB2 V8.1.4.
It seems to me that this is quite similiar to one of the last APARS on
DB2, so please have a look at the IBM Patch-List for DB2.

Thanks for the hint.

I got the brand-new driver from FixPak 5 which is version 1.9.23 and
tried several VMs (JDK 1.4.1_03, JDK 1.4.1_05, JDK 1.4.2_03, all from
Sun, and BEA JRockit SP2 1.4.1_05) with several settings to make sure
that the behavior is not related to VM problems.

The result was the same.

One thing is remarkable, though. Somehow the problem seems to be able
to "survive" VM restarts. Very often in my tests - which consist of a
VM start, doing one test, writing to disk, and stopping the VM - the
problem suddenly showed up and then stayed until the end of the test
session. But of course, there were occasions when it changed back to
the healthy state again. But every time the problem state or the
healthy state stayed for several consecutive VM restarts.

Very strange.

The only explanation I have for this is that the problem must be
originated not on the client side, but on the gateway server (DB2
Connect) that we are using. I will investigate this further.

Any hints are still very much appreciated.

Regards,
Dani

[DB2]

Might be worth testing another JDBC driver that doesn't require a
gateway (ie StarSQL, from StarQuest).

Bob

da***********@email.ch (Dani) wrote in message news:<bf**************************@posting.google. com>...

you are using an older revision of the JDBC-Driver. The actual one is
of Version 1.5.54 DB2 V8.1.4.
It seems to me that this is quite similiar to one of the last APARS on
DB2, so please have a look at the IBM Patch-List for DB2.

Thanks for the hint.

I got the brand-new driver from FixPak 5 which is version 1.9.23 and
tried several VMs (JDK 1.4.1_03, JDK 1.4.1_05, JDK 1.4.2_03, all from
Sun, and BEA JRockit SP2 1.4.1_05) with several settings to make sure
that the behavior is not related to VM problems.

The result was the same.

One thing is remarkable, though. Somehow the problem seems to be able
to "survive" VM restarts. Very often in my tests - which consist of a
VM start, doing one test, writing to disk, and stopping the VM - the
problem suddenly showed up and then stayed until the end of the test
session. But of course, there were occasions when it changed back to
the healthy state again. But every time the problem state or the
healthy state stayed for several consecutive VM restarts.

Very strange.

The only explanation I have for this is that the problem must be
originated not on the client side, but on the gateway server (DB2
Connect) that we are using. I will investigate this further.

Any hints are still very much appreciated.

Regards,
Dani