Re: time/date as key fields

Yep, and from 6.1 I believe the resolution is 1 microsecond per clock tick, not 8 microseconds as the article says. A matter of using 3 more bits to hold the count.

Here is the explanation of the format for the TOD - standard format, it's called - this is as of 6.1 - earlier releases would have said that bit 48 was incremented in units of 8 microseconds.

The *_Standard Time Format_* is defined as a 64-bit (8-byte) unsigned binary value as follows:
Offset
Dec Hex
Field Name
Data Type and Length
0 0
Standard Time Format
UBin(8)
start of change 0 0
Time
Bits 0-51
0 0
Uniqueness bits
Bits 52-63end of change
8 8
--- End ---


The *_time_* field is a binary number which can be interpreted start of change as a time value in units of 1 microsecond. A binary 1 in bit 51 is equal to 1 microsecond.end of change

The *_uniqueness bits_* field may contain any combination of binary 1s and 0s. These bits do not provide additional granularity for a time value; they merely allow unique 64-bit values to be returned, such as when the value of the /time-of-day (TOD) clock/ is materialized. start of change When the /uniqueness bits/ all contain binary 0s, then the 64-bit value returned is not unique. Unless explicitly stated otherwise, MI instructions which materialize the /TOD clock/ return a unique 64-bit value.end of change


So if you want the uniqueness, you don't convert it into timestamps for presentation - only for use as a unique ID. Similar to the UUID or GUID that Rob mentions. I don't know if you can use UUID for sorting, can you rely on it to be in date/time order?

HTH
Vern

On 11/19/2010 10:49 AM, Dave McKenzie wrote:

The 8-byte TOD value returned by MATTOD is unique because the system
adds "uniqueness bits" to guarantee it. But when converted to other
formats, values may be rounded and not unique. See:

http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/rzatk/MININ.htm#HDRTIMEFMT

under "Standard Time Format".

and MATMDATA options x0000 and x0004:

http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=/rzatk/MATMDATA.htm


--Dave

On 11/19/2010 06:44 AM, dieter.bender@xxxxxxxxxxxx wrote:

MATTOD seems not to be guaranteed to be unique:
http://www-01.ibm.com/support/docview.wss?uid=nas186213a22df7617f886256c25005de629

Dieter
--------------------------------------------------
From: "Vern Hamberg"<vhamberg@xxxxxxxxxxx>
Sent: Friday, November 19, 2010 2:47 PM
To: "Midrange Systems Technical Discussion"<midrange-l@xxxxxxxxxxxx>
Subject: Re: time/date as key fields

Yep, even timestamps on modern fast boxes will not guarantee uniqueness,
even down to the microsecond. There is an MI timestamp, TOD, that is 8
bytes long and guaranteed to be unique - pretty easily retrieved
(MATTOD) and converted to a 16-character value (CVTHC, or CVTCH - i
forget!) that sorts by date/time. Or you can leave it as its 8-byte
value, that'll still sort just fine.

Vern

On 11/19/2010 4:47 AM, dieter.bender@xxxxxxxxxxxx wrote:

...uniqueness is not guaranteed and the sequence might not be strict. A
transaction number could be addedd automatically and using a view it
could
be unvisable to the application.

Dieter



--------------------------------------------------
From: "David FOXWELL"<David.FOXWELL@xxxxxxxxx>
Sent: Friday, November 19, 2010 10:21 AM
To: "Midrange Systems Technical Discussion"<midrange-l@xxxxxxxxxxxx>
Subject: time/date as key fields

Is there anything wrong in using date and time of creation fields
already
existing in a transaction table to keep track of transaction history or
would it be advisable to add a separate field transaction_number? The
latter means a lot more program changes.

Thanks