Good Day,

IBM Db2 Database for Windows Version 9.
About DB2 Administration Tools Environment
DB2 administration tools level:
Product identifier SQL09019
Level identifier 020A0107
Level DB2 v9.1.900.215
Build level s100326
PTF WR21463
Java development kit (JDK):
Level IBM Corporation 1.5.0
Windows 2008 64 bit

I am very new to DB2 and database administration. I have a DB2 database that I am looking at for the first time. My intentions are to connect to the DB2 database for doing reporting on a Service Management Call tracking tool. DB2 is the database for our Call tracking application. Currently, I connect to the database via an HP ODBC driver. This driver then connects to the application to get the data for my reporting. We don't directly connect to the DB2 database. (I hope that makes sense).

In our application, there is a table called CLOCKS. This table contains 14 columns. 4 of the 14 fields are Data Type TIMESTAMP. (Length 10) Nullable Yes.

In the application, the date in those four fields are stored as an elapsed time.
If a ticket in the system has been opened for 12 minutes, the field looks like 00:12:14 in the application.

When I look in DB2, the information does not look like 00:12:14. It looks like Jan 1, 4000 12:10:59 AM 000000. When I click into the field in DB2, it then looks like 4000-01-01 00:10:59.0.

The HP ODBC driver "translates" the proper time for me when I go through the application. But, it is so slow to query. I would ideally like to go directly against the DB2 database (bypassing the application altogether).

Can anyone tell me why the field in DB2 stores the timestamp as Jan 1, 4000 12:10:59 AM 000000 instead of 00:12:14. Or, can someone tell me how I can translate Jan 1, 4000 12:10:59 AM 000000 into 00:12:14?

Any help is appreciated!

Thanks!

Jennifer

A timestamp is the combination of a date and a time, i.e. 4000-01-01 as date and 12:10:59 as time in your case. From your explanation, a timestamp doesn't seem to be the correct approach - if you want to track durations, don't use a timestamp but rather some other data type like an INT or BIGINT and store the seconds in such a column. If you want to have absolute points in time, then a timestamp would be a good approach. So it boils down to using the correct data type for each type of data.

Jennifer, DB2 stores timestamp information in a table in a modified pack format.

4000015000
0011009000

it is read top to bottom and left to right. You will notice that it takes 10 bytes to store.

Beyond that, how it is displayed is up to the application.

When you mention:
How are you looking at the data? Are you using Command Editor or Control Center with the result in a Grid? If you are, the Jan 1,... format is set in the application and (according to IBM) can't be changed.

If you select the column so the output shows up in the result pane (you can change Tool Settings on the Command Editor tab and unchecking the 'Display Results for a single query on the Query Results pane'), you should see the (normal) output of:

4000-01-01-00.10.59.000000