db2 internal format time & date



IBM's flagship relational database management system

db2 internal format time & date

Postby vxxxv » Sun Aug 08, 2010 9:38 pm

Hi all,

I have read a lot of docs about DB2 date and time format, generally it's packed decimals, 4 bytes for date and 3 bytes for time. But could you please explain a bit more about this format please? Here is example of some db2pd output:
db2pd -d sales1 -activestatements
Database Partition 0 -- Database SALES1 -- Active -- Up 0 days 13:40:08
Active Statement List:
Address AppHandl [nod-index] UOW-ID StmtID AnchID StmtUID EffISO EffLockTOut EffDegree EntryTime StartTime LastRefTime
0x0700000042919800 8 [000-00008] 1 454715737 0 0 1 -2 0 Tue Aug 3 23:59:50 Tue Aug 3 23:59:50 Tue Aug 3 23:59:50
0x070000004259CFC0 8 [000-00008] 1 3 0 0 1 -2 2 Tue Aug 3 19:34:20 Tue Aug 3 19:34:20 Tue Aug 3 19:34:20
0x070000004259D300 8 [000-00008] 1 2 0 0 1 -2 2 Tue Aug 3 19:34:20 Tue Aug 3 19:34:20 Tue Aug 3 23:59:50
0x070000004259D640 8 [000-00008] 1 1 196 1 1 -2 0 Tue Aug 3 19:34:19 Tue Aug 3 19:34:19 Tue Aug 3 19:34:19

The question is about EntryTime, StartTime, LastRefTime. How will "Tue Aug 3 23:59:50" be stored? According to docs it should be like following: 20100803235950 and then convert each two digits as packed decimals into one byte. Can you please show me an example how this process looks like?

Thank you! :)
vxxxv
 
Posts: 4
Joined: Sun Aug 08, 2010 9:27 pm
Has thanked: 0 time
Been thanked: 0 time

Re: db2 internal format time & date

Postby dick scherrer » Mon Aug 09, 2010 5:56 am

Hello and welcome to the forum,

It is not clear what you are asking. . . Suggest you post a few of these values in hex so we can all see the "same thing".

Also post the info from the docs where you have a doubt using copy/paste to show the particular statement(s) you want clarified..
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: db2 internal format time & date

Postby vxxxv » Mon Aug 09, 2010 1:37 pm

Thank for response :) Here are examples:
0x1e8c12ff5dcdf should be  Wed Jul  7 17:15:59     
0xc0cc6f132bc7   should be  Tue Aug  3 09:14:16
0xc7c98b590795 should be  Tue Aug  3 19:34:19
0xcac7aba6b7a2 should be  Tue Aug  3 23:59:50


Here are links with explain:
http://publib.boulder.ibm.com/infocente ... dattys.htm
http://www.devx.com/dbzone/Article/28713/1954

So if I'm correct, I need first to convert each byte from hex representation to binary representation. Example of 0xc0cc6f132bc7:
1100 0000 1100 1100 0110 1111 0001 0011 0010 1011 1100 0111
c 0 c c 6 f 1 3 2 b c 7

Now I need to add 0110 to invalid BCD numbers (bigger than 1001) to convert them to valid BCD (0...9). Thus each half byte will represent one decimal digit. I add carry to the next half byte, maybe this is wrong? Example 1000 1111. 0110 needs to be added to 1111, since it is invalid BCD number 1111 + 0110 = 10101 -> 1 0101 . The 1 from left I added to next half byte so: 1000 1111 = 1001 0101. Here we go:

1 0010 0001 0011 0010 0111 0101 0001 0011 0011 0010 0010 0111
1 2 1 3 2 7 5 1 3 3 2 2 7

So, how from 1213275133227 should I get Tue Aug 3 09:14:16 :?

Thanks!
vxxxv
 
Posts: 4
Joined: Sun Aug 08, 2010 9:27 pm
Has thanked: 0 time
Been thanked: 0 time

Re: db2 internal format time & date

Postby dick scherrer » Tue Aug 10, 2010 1:00 am

Hello,

Where did the 0x values originate - they do not look like the examples in the linked documentation. . .?

Now I need to add 0110 to invalid BCD numbers (bigger than 1001) to convert them to valid BCD (0...9).
I believe not. . . I've never seen this required when working with database info, so i'm not sure why it would be needed here.

Suggest you unload a few rows into a qsam file and show the contents of some date/time columns in hex.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: db2 internal format time & date

Postby Robert Sample » Tue Aug 10, 2010 3:05 am

I think you are completely and totally off base -- but then, I don't know DB2 either.

Your data is not consistent with the quoted material -- your four values are 6.5, 6, 6, and 6 bytes of hexadecimal data. Yet the quoted material says a DB2 date is 4 bytes in hex, a time is 3 bytes in hex, and a date/time stamp is 10 bytes in hex. So you have too many bytes for a date or time field, not enough bytes for a date/time stamp, and not enough bytes for a date and time field combined. So it is not at all clear exactly what your values represent.

The only time I ever seen a need to adjust for BCD is certain very early model teletypes and other machines (by very early, I mean before 1973). The mainframe handles EBCDIC, handles it well, and there is absolutely no reason to be converting from the native EBCDIC to BCD.
Robert Sample
Global moderator
 
Posts: 3719
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 279 times

Re: db2 internal format time & date

Postby GuyC » Tue Aug 10, 2010 1:50 pm

the tool referenced : db2pd , is one for DB2 on LUW
I'm pretty sure internal formats of timestamps are different on Windows vs z/OS.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: db2 internal format time & date

Postby GuyC » Tue Aug 10, 2010 1:51 pm

or Unix vs z/OS
( no edit post function on this forum :cry: )
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: db2 internal format time & date

Postby vxxxv » Wed Aug 11, 2010 1:05 am

I agree, it doesn't look like BCD at all. Maybe this is number of seconds since instance start time. I will try to read this as double or long double, I read this value from shared memory of DB2 on AIX, so I don't know how to print this into qsam files
vxxxv
 
Posts: 4
Joined: Sun Aug 08, 2010 9:27 pm
Has thanked: 0 time
Been thanked: 0 time

Re: db2 internal format time & date

Postby vxxxv » Thu Aug 12, 2010 1:09 am

I'm wrong again :( I found out that this is interval from session start time. But I have no idea in what data structure it is saved. I tried to read this value as double, float, long double I got a very small number, something like 1.7e-300. I am stuck :(
vxxxv
 
Posts: 4
Joined: Sun Aug 08, 2010 9:27 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post