ibm-db2 >> JDBC incorrectly retrieves date

by Kenevel » Fri, 16 Feb 2007 05:24:58 GMT

Hi everyone,

Has anyone come across a problem where on Linux using DB2 9.1 Express-
C with the packaged jcc-JDBC driver that it fails correctly to parse a
returned date value? I'm simply calling

resultSet.getDate(paramIndex)

and it's giving me a date that's way off.

The reason that I find this bizarre is that I can see the correct date
value being transferred over the wire (localhost) using the Wireshark
packet-sniffer; also because it parses timestamp values correctly from
the same statement.

This is exposed in a unit-test which is run with a classpath that
includes the following files:

DB2_HOME/java/db2jcc.jar
DB2_HOME/java/db2jcc_licence_cu.jar

The result-set is returned as a cursor from a stored procedure which
reads from a temporary table. The value is read from a column in that
table declared as type DATE.

I'm sure I'm missing something pretty obvious so I thought I'd ask
first before getting to involved in rooting out the problem!

Cheers

Michael


ibm-db2 >> JDBC incorrectly retrieves date

by Kenevel » Fri, 16 Feb 2007 07:15:09 GMT


Hi,

I thought I'd post an update to this as I've fixed the problem. It
seems that it was to do with the CLI CFG settings you can retrieve by
keying in

db2 get cli cfg

The setting in question is "DateTimeStringFormat". I set this to ISO
value in the [common] section following the instructions here: http://
publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/
com.ibm.db2.udb.admin.doc/doc/r0002071.htm

A value wasn't previously set. I had previously set the
DB2_SQLROUTINE_PREPOPTS to "DATETIME ISO".

One db2stop and db2start later and I was away.

Perhaps if someone has some more information about what is going on
under the covers that'd be great.

Cheers

Michael

ibm-db2 >> JDBC incorrectly retrieves date

by Knut Stolze » Fri, 16 Feb 2007 15:56:26 GMT


It would be helpful if you provide some sample output of the date in DB2 and
what your query actually returned.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

ibm-db2 >> JDBC incorrectly retrieves date

by Kenevel » Fri, 16 Feb 2007 19:04:15 GMT


Hi Knut,

Thanks for taking the time to have a look at this. The date was being
returned in US format, so MM/DD/YYYY and I guess the JDBC Driver http://9573f00jrksyaq7x-fvlpkrrco.hop.clickbank.net/?tid=DATABASEFORUM " target="_top" rel="nofollow">[Driver Robot fixes your driver problems] was
expecting it in a different format. Hence the ISO date 1973-03-29
(expressed in yyyy-MM-dd) was being transferred as 29/03/1973 and
being converted into a Java date with the value 1975-05-03 - I guess
26-or-so months ahead of the actual date. This mis-conversion can be
demonstrated with the following code:

import java.text.SimpleDateFormat;

public class DateTest {

public static void main(String[] args) throws Exception {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
System.out.println(format.parse("1973-29-03"));
}
}

I guess this doesn't actually answer your request for more
information, Knut, but I believe it clears up the problem. Setting the
DateTimeStringFormat to ISO on my linux box made the difference and it
now works. Interestingly I had assumed that DB2 would transfer date
values as a millisecond value from 1970, but I guess this isn't
flexible enough for all possible dates.

Cheers

Michael

ibm-db2 >> JDBC incorrectly retrieves date

by Knut Stolze » Fri, 16 Feb 2007 23:51:53 GMT


Makes sense to me, although I would have expected an exception being thrown.
After all, your date does not match with the indicated format.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

ibm-db2 >> JDBC incorrectly retrieves date

by Kenevel » Sat, 17 Feb 2007 00:28:21 GMT


True, although the JDBC Driver http://9573f00jrksyaq7x-fvlpkrrco.hop.clickbank.net/?tid=DATABASEFORUM " target="_top" rel="nofollow">[Driver Robot fixes your driver problems] may have been expecting DD/MM/YYYY
instead of the provided MM/DD/YYYY.

ibm-db2 >> JDBC incorrectly retrieves date

by Knut Stolze » Sat, 17 Feb 2007 00:56:32 GMT


I don't see what the JDBC driver has to do with that. Your sample code
shows an independent little Java program.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

ibm-db2 >> JDBC incorrectly retrieves date

by Kenevel » Wed, 21 Feb 2007 02:18:28 GMT


DB2 transmits its date values as string values over the wire, at least
that was what showed up when using Wireshark to sniff the local
connection. Unless the DB2 JDBC driver implements its deserialisation
code in a native binary, it will use a similar method to the one
outlined in the sample Java app - hence no exception and also an
explanation for why the dates were off.

ibm-db2 >> JDBC incorrectly retrieves date

by Knut Stolze » Wed, 21 Feb 2007 04:09:03 GMT


Yes, that's right.


Then the client would have to know how the date string was encoded at the
server. And because many people still insist on not using a sane
formatting for dates, i.e. the ISO format, there is often not a unique way
to get the right results.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

ibm-db2 >> JDBC incorrectly retrieves date

by weirdwoolly » Wed, 28 Feb 2007 21:31:10 GMT


We had a similar issue with dates in java when we migrated from v7 to
v8 at the end of 2005 and raised PMR 34350 with IBM for a resolution.

We demonstrated the problem through returning CURRENT DATE from a
stored procedure both as a result set value and an out parameter.

The out parameter worked fine, however the result set behaved in the
same way as yours i.e. 2005-09-21 became 2006-09-09

We resolved the issues ourselves by making various code changes but
also binding the stored procedures with DATETIME JIS (we found ISO to
be less compatible than JIS for our application)

The PMR was closed with no changes to be made to the v8 documentation
to reflect our issue but an offer was made to update the v9
documentation and a permanent restriction was put in place for v8
although I am not sure what that is to be honest.

See http://www-1.ibm.com/support/docview.wss?rs=71&context=SSEPGG&q1=IY79762&uid=swg1IY79762&loc=en_US&cs=utf-8&lang=en
for some of the details and if you have access to PMR's it is 34350.

Regards,
Paul.

Similar Threads

1. Using JDBC to retrieve and update AS400 files with a timestamp - AS/400

2. Strange JDBC error when retrieving data

Can anyone help me.
I'm trying to retrieve data from DB2/AIX v8.1 using JDBC connectivity
(Universal Driver - Type 4 - v1.1.67) . I get a recurrent error (after
connection, apparently) :

[ibm][db2][jcc] BEGIN TRACE_DIAGNOSTICS
[ibm][db2][jcc][SQLException@2a4843] java.sql.SQLException
[ibm][db2][jcc][SQLException@2a4843] SQL state = null
[ibm][db2][jcc][SQLException@2a4843] Error code = -99999
[ibm][db2][jcc][SQLException@2a4843] Message = Unicode string can't convert
to Ebcdic string
[ibm][db2][jcc][SQLException@2a4843] Stack trace follows
com.ibm.db2.jcc.c.SQLException: Unicode string can't convert to Ebcdic
string
at com.ibm.db2.jcc.a.o.a(o.java:130)
at com.ibm.db2.jcc.a.bg.a(bg.java:930)
at com.ibm.db2.jcc.a.bh.g(bh.java:527)
at com.ibm.db2.jcc.a.bh.b(bh.java:307)
at com.ibm.db2.jcc.a.bh.a(bh.java:73)
at com.ibm.db2.jcc.a.b.a(b.java:916)
at com.ibm.db2.jcc.a.b.a(b.java:450)
at com.ibm.db2.jcc.a.b.a(b.java:293)
at com.ibm.db2.jcc.a.b.<init>(b.java:207)
at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:244)

This error is undocumented. Did anyone had this problem before ?
Thanks
FX


3. retrieving write/last change date of flat-file dataset - AS/400

4. SQL : retrieve the system date

Hi all,

Little questions for you, specialists of AS400
is there any way to get the system date in SQL (not embedded) ?
the point would be to get something like
Select * from MyFile Where Mydate = SystemDate

Furthur i was wondering how the system deals with the system date.
Does it calculate it everyday using a dtaara, or something like this,
or uses it a system file ?

Thank you by advance for your answers.
Good day

5. jdbc date format error

6. Can you install a Power Supply Incorrectly?

I don't know if this will do any good but my 5 year old Pentium III suddenly 
died (click the button, nothing) so I thought I'd try replacing the Power 
Supply (250W ATX type), since they're not very expensive. I bought a 300W 
ATX (it said 2.03 compliant while the old one said 2.01 but I didn't think 
much of it), installed it and ran the 20 receptacle keyed plug to the mobo 
connector and hooked up my disk drives with the proper size connector. 
Turned things on and it wouldn't boot from the hard drive (Maxtor 20GB), 
then a burning smell. Now of course it won't boot at all; I've tried putting 
the Maxtor in another computer and it seems to be, for want of a better 
word, fried (i.e. computer can't see or boot from it).

Of course I don't know what caused the original PS to fail but it WAS almost 
six years old. What I'm asking is if there was some way I could be at fault 
for this or if the new PS (I know, I bought a cheap one, about $30) would 
most likely be the problem.

This will teach me, but WHAT? Buy only expensive PSs? How could one check? 
This happened AS SOON AS I switched the power back on.

Any information to help me avoid a repeat of this debacle would be 
appreciated. The hard disk had some stuff that I really need, although I DO 
have fairly recent backups. The problem is that it had a DOS partition set 
up with a fairly complicated Clarion/LPM configuration that I KNOW will be 
almost impossible to duplicate.

Thanks.

=NLK=


7. Governor incorrectly forces processes

8. COM.ibm.db2.jdbc.app.DB2Driver JDBC version compatibility

I am using DB2 version 7.2 on Windows 2000. I just installed Fixpack
12 as well.

This driver is not working with a third-party software. The vendor
claims that this driver is too old. As a proof of this, the following
call is failing:

Connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR
_READ_ONLY); with driver version: [07.02.0009]
major version = 7
minor version = 1
Exception in thread "main" java.lang.AbstractMethodError:
COM.ibm.db2.jdbc.app.D
B2Connection.createStatement(II)Ljava/sql/Statement;
        at TestDB2.test(TestDB2.java:22)
        at TestDB2.main(TestDB2.java:7)

My questions are:

. What does this imply? What version of JDBC does the above driver
implement?
. What will it take for me to get a driver which will work with
version 7.2 and provide above functionality? I am assuming that I'd
need JDBC 2 functionality.
. How can I get/upgrade to such a driver?
. Or do I have to use DB2 version 8 to get this functionality?

Thanks