IBM Mainframe Forum

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!

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..

Thank for response Here are examples:


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!

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.

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.

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.

or Unix vs z/OS
( no edit post function on this forum )

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

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