JDBD: time format

[Mark]

IBM supplies a db2 driver and an as400 driver for using jdbc. We found
out that the db2 gives (much) better performance for us.

But we have a problem with time fields. The db2 driver insists in
giving us time formatted as hh.mm.ss instead of hh:mm:ss.

We have tried the driver parameter "time format" to solve this problem.
We even tried format "hms" in combination with the "time separator"
parameter, but whatever we do the time keeps getting formatted as
hh.mm.ss.

When using the as400 driver we have no problems what so ever. Time is
getting correctly formatted.

So the issue seems to be with the db2 driver. Can anyone confirm this
behavour and/or has some recommendations on how to get the correct
format ?

Using the as400 driver is no option, becaue of the performance issue.

Thanks,
Mark

[walker.l2]

Mark,
What platform are you querying DB2/400 from? There are a number of JDBC
drivers you can use to access DB2/400, depending on where your JVM is
running:

AS/400 Toolbox driver which can be used in Java applications running on
any platform.

AS/400 Native driver which can only be used in Java applications
running on an AS/400.

Generic DB2 driver, I think this can be used in Java applications on
any platform, but I can't say for certain as I have never used it.

I think you will find the AS/400 Native driver is the best performing,
but whether you will be able to use it depends on where your Java
Application is running.

Also, are you using Connection Pooling, and Prepared Statement caches?
These can have a big impact on performance.

Walker.

[Mark]

The application is webbrowser based and running through jsp's. The
jsp's and all related java classes are handled by a tomcat server. This
is not the standard tomcat server delivered by IBM. As I understand it,
there were problems configuring the native tomcat, so they just
installed another tomcat implementation downloaded from apache
(currently on version 5.0.19).

Tomcat, and thus all requests (jsp's and classes), are running in a JVM
on the AS400.

The driver that is giving me problems with the time format is
com.ibm.db2.jdbc.app.DB2Driver. I believe this is the native one (?).

The driver that works is com.ibm.as400.access.AS400JDBCDriver. With
this driver I can set the parameter "driver" to "native". If I do that,
then once again the time format is false.

So it's got to be the native driver that is having problems with the
time format.

Querying is done through stored procedures that give back a resultset.
This is done so we can use the sp's also by our native AS400
application from which this web app is a spin-off.

[Rodney A. Johnson]

If I had to guess, I would say that there is a prestart job servicing
your request and that prestart job has a job description that indicates
the time separator to be used is '.'.

You probably need to change the job description for those prestart jobs,
and then you would need to end and restart those prestart jobs to get
the job description changes to take affect.

A DB expert would probably know what prestart jobs are used to services
JDBC connections. Unfortunately my experience with DB ended before JDBC
came about.

[Mark]

Thanks for the heads up, but the pj's are running with a time separator
defined as ":".

[walker.l2]

The com.ibm.db2.jdbc.app.DB2Driver is the Native driver, and you
shouldn't have any problems using that since your Tomcat installation
is running on an AS/400. The 'time format' and 'time separator'
parameters do have to be used in conjuntion with one another, but I
can't see why your settings aren't being honoured.

Are you retrieving the time fields as java.util.Date objects, or
java.sql.Date objects, or something else? How are you displaying the
time fields - are you using a java.textl.DateFormat?

The prestart jobs for the Native driver are the QSQSRVR jobs in the
QSYSWRK subsystem.
The prestart jobs for the Toolbox driver are the QZDASOINIT jobs in the
QUSRWRK subsystem.
On our box, the time separator for both types of prestart jobs is set
to a colon.

[Mark]

>Are you retrieving the time fields as java.util.Date objects, or
>java.sql.Date objects, or something else? How are you displaying the
>time fields - are you using a java.textl.DateFormat?

All fields in the resultset that the stored procedure is returning, are
converted to Strings by using the ResultSet.getString method. And no
extra formatting is being done.

As far as the prestart jobs go, both variants have the semicolon.

[walker.l2]

How is the time stored in your database? (What SQL / DDS datatype is
the column?)

What might be happening is that if the Stored Procedure is returning
your time as an SQL Date or SQL Time datatype, the ResultSet
implementation might change your getString() call to a
getDate().toString() call or a getTime().toString() call behind the
scenes. The toString() methods of a java.sql.Date and java.sql.Time
will always return a String using a : as the separator.

It would be nice to get to the bottom of this difference between the
two IBM drivers, but two work arounds suggest themselves:

1) Use String's replace() method to convert the :s to .s (not very
elegant)
2) Use getDate or getTime to retrieve the data, then use a
java.text.DateFormat to convert to a String (but this assumes your
Stored Procedure is returning one of these datatypes, and not just
text)

Walker..

[Mark]

>How is the time stored in your database? (What SQL / DDS datatype is
>the column?)

It's build through DDS as *ISO format. When running query or sql on the
box I get my colons on screen.

>What might be happening is that if the Stored Procedure is returning
>your time as an SQL Date or SQL Time datatype, the ResultSet
>implementation might change your getString() call to a
>getDate().toString() call or a getTime().toString() call behind the
>scenes. The toString() methods of a java.sql.Date and java.sql.Time
>will always return a String using a : as the separator.

>1) Use String's replace() method to convert the :s to .s (not very
>elegant)

>>But we have a problem with time fields. The db2 driver insists in

>>giving us time formatted as hh.mm.ss instead of hh:mm:ss.

Well, it's the other way around. I want the colon but always get a
period, despite of job settings and iso format of the databasefield.
That's the strange part. Somewhere somehow the colon is replaced by a
period.

I just confirmed by using a debug session in Eclipse on a PC that the
toolbox driver in its resultset gives back the time separator as part
of the returned data. It gives me the colon. When I use the toolbox
driver with the parameter "driver=native", the time separator as given
back in the resultfield is a period !

So the toolbox driver in toolbox mode is ok. The toolbox driver in
native mode or the native driver itself are returning a wrong separator.

[walker.l2]

Sorry, my mistake about which separator you wanted.

I'm confused that you say it is a DDS column type *ISO.

DDS datatypes are P, S, B, F, A, L, T, Z, H, J, E, O, and G.
SQL datatypes are INTEGER, SMALLINT, BIGINT, FLOAT, NUMERIC, DECIMAL,
CHARACTER, VARCHAR, LONG VARCHAR, DATE, TIME, TIMESTAMP, GRAPHIC,
VARGRAPHIC, LONG VARGRAPHIC, REAL, DOUBLE, CLOB, BLOB, DBCLOB, and
DATALINK.

How is your field / column declared in the file / database?
And how is it declared in the Stored Procedure?

Walker.

[Kent Milligan]

So you're saying that if you call a stored procedure that returns a time
column...
If the procedure is called w/Toolbox JDBC the Time format returned is
HH:MM:SS
If the procedure is called w/Native JDBC the Time format returned is
HH.MM.SS

Did I get that right?

Mark wrote:

--
Kent Milligan, DB2 & BI team
PartnerWorld for Developers, iSeries
km...@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
>>> www.iseries.ibm.com/db2
(opinions stated are not necessarily those of my employer)

[Mark]

>How is your field / column declared in the file / database?

DDS as type T. I checked with DSPFFD and did see type Time and
timenotation *ISO. That's what I wanted to say :)

>And how is it declared in the Stored Procedure?

It's embedded SQL inside RPGLE. This is specified as part of the
compilation parameters:

CRTSQLRPGI DATFMT(*ISO) DATSEP(-) TIMFMT(*ISO) TIMSEP(':')

and to be sure I did a DPSPGM:

Date format . . . . . . . . . . . . . . . . . : *ISO
Date separator . . . . . . . . . . . . . . . . : -
Time format . . . . . . . . . . . . . . . . . : *ISO
Time separator . . . . . . . . . . . . . . . . : :

[Mark]

Yes, that's exactly right.

And with the Toolbox version _and_ parameter "driver=native" I also get
HH.MM.SS.

[walker.l2]

Well I think you have identified your own problem there:

TIMSEP(':')

Time separator . . . . . . . . . . . . . . . . : :

Don't you want those to be a . character? So shouldn't you have
TIMSEP('.') on your CRTSQLRPGI command?
(*ISO format should use the . time separator

It looks like the Toolbox driver is ignoring the set up of your Stored
Procedure, and allowing you to override the separator. Whereas the
Native driver is honouring the separator defined in the Stored
Procedure.

Walker.

[walker.l2]

No I've confused myself with eth separators again!

It looks like the *ISO format identifier is over-riding the (incorrect)
: separator.

I think you want to set the format to *HMS on your CRTSQLRPGI command.

Walker.

[walker.l2]

Sorry, that last post still wasn't very clear. Let me have another try:

When you use *ISO time format, the time separator parameter is ignored,
and the . separator will always be used. The time separator parameter
is only used if you use the *HMS format.

Although you have tried *HMS on your JDBC connection, I think you will
need to specify *HMS as the time format on the CRTSQLRPGI command.

It appears that the Toolbox driver is ignoring the Stored Procedure
specifications, and following the JDBC settings; whereas the Native
driver is honouring the Stored Procedure specifications and
disregarding the JDBC settings.

Perhaps Kent may be able to shed some light on why the drivers have
different behaviour.

Walker.

[Mark]

>Although you have tried *HMS on your JDBC connection, I think you will
>need to specify *HMS as the time format on the CRTSQLRPGI command.

Specifying HMS on the compilation did indeed solve the problem. We are
going to recompile all our SP's.

As for the driver, I am curious about the why of the different
behaviour. I personally think the native driver is faulty and should be
corrected. There is currently no way to override the result and I
believe the parameter is meant for that purpose. Hopefully Kent will
provide some more details.

Anyway, thanks for your time. Much appreciated.

[Kent Milligan]

What time format and separator settings are you specifying on the JDBC
Connections?

Mark wrote:

--

[Mark]

url="jdbc:db2://" + system + ";prompt=false;date format=iso;time
format=hms;time separator=:;";

[Kent Milligan]

If I followed this thread correctly than these JDBC driver settings did
not change. You just recompiled the stored procedure with time
format=hms & sep=:?

Mark wrote:
> url="jdbc:db2://" + system + ";prompt=false;date format=iso;time
> format=hms;time separator=:;";
>

--

[Mark]

You are correct. I have always had the jdbc time formatting parameters
as hms and colon.

I recompiled the stored procedure with format hms and colon as
separator and now I am getting back the correct format: HH:MM:SS.

If I compile the stored procedure with ISO, the driver is ignoring the
parameters and not doing the conversion, as I understand it should do.

[Kent Milligan]

Mark,
Would you be able to open up a problem report with IBM Support on this
behavior with the Native JDBC driver?

--

[Mark]

Sure, I will see what I can do.

Thanks for helping out and thanks for your time.

[Kent Milligan]

Thanks for your patience, too. The Native JDBC driver development team
really needs to get your scenario to analyze what the problem is.

Mark wrote:

> Sure, I will see what I can do.
>
> Thanks for helping out and thanks for your time.
>

--