On 28 Jun 2012 11:14, John Yeung wrote:
The link you provided says nothing about disk storage. It just says
"internal representation", which, given the trouble IBM has gone to
maintain the illusion of formatted dates, doesn't necessarily imply
disk.
Sorry, there were supposed to be two links, each with some quoted
text. I somehow lost the second link in my reply, and what remained was
the quoted text from that second\lost link. Presumably something went
awry while I was looking for and intending to paste the drill-down text;
the drill-down text is not presented for the page served by the second
link.
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference
-> Language elements -> Data types -> Datetime values
_Date_
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzch2date.htm
"... The internal representation of a date is a string of 4 bytes that
contains an integer. The integer (called the Scaliger number) represents
the date. ..."
_Date, Time, and Timestamps_
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/rzatk/MINDTCON.htm
"External Data Formats
For support of date, time, and timestamp, the external data format that
is used is a character string. Associated with this character string
will be a data definitional attribute list (DDAT) that describes all the
attributes of the string. ...
Data Definitions
DATE
Date data type. The internal format is a 4 byte binary value. The
DDAT number must reference a DDAT with an internal DATE format code or
be set to zero which implies internal format. The internal format is
fixed length, trailing blanks are NOT allowed.
TIME
Time data type. The internal format is six numbers packed into a
three byte value. There is no sign nibble. The DDAT number must
reference a DDAT with an internal TIME format code or be set to zero
which implies internal format. The internal format is fixed length,
trailing blanks are NOT allowed.
TIMESTAMP
Timestamp data type. The internal format of a Timestamp is a ten
byte composite value. The composite is two numbers: one for date and
time respectfully. The date and time numbers have the same encoding as
the date and time data types, with an exception to the time number. The
time number has an additional 3 bytes for a six packed digit microsecond
value. ...
System internal format
Date - 4 byte binary Scaliger number ... "
.. I have just done some experimentation which corroborates the idea
that date fields are just 4 bytes on disk, but I'm confused why IBM
would be so adamant about hiding this. For packed and binary
fields, IBM is happy to have the "field length" and "buffer length"
be mismatched, with the latter showing the actual number of bytes
used. Why not do the same for dates? Would it not be simpler to
implement, as well as more informative to people using DSPFD, DSPFFD,
DSPPFM, etc.?
The buffer-length reflects the amount of data received by the program
from the database; i.e. the layout of the data available to the program,
in order that the program[mer] knows where the [starting position and
ending position of the] data for each field resides relative within the
buffer. The Packed DECIMAL [BCD] data is compact in storage and remains
compact in the buffer; i.e. the buffer-length matches the internal
storage length. The DATE data is compact in a 4-byte binary internally
in storage *but* the actual value copied into the buffer of the program
is the formatted character string value. Thus the DSPFFD showing the
illicit buffer length, so the program[mer] knows the physical layout of
the data in the program, regardless that the value vitiates the
[perception that the] buffer length reflects the length for the internal
storage of the value.
That implementation reflects some decisions that were made to allow
the HLLs to implement DATE data type support with a lesser impact than
if implemented in some other possible ways; and at the time, IMO an
unfortunate choice. I had hoped that the /datetime/ types would be
implemented much like BCD [Binary Coded Decimal] value, as an effective
Binary Coded Date value, whereby an "edit" instruction was applied to
value to get the /formatted/ character string representation of the Date
Value.
I owned the DSPPFM code and wanted to implement the visibility to the
internal data, the four byte integer, but in order to be consistent with
the buffer-length as presented by DSPFFD, the decision was made to
present the external form [and thus suffer negative consequences; i.e.
values that can not be converted to a decipherable character string will
cause an I\O error :-( ] Better to debase the apparent equivalence
between internal storage length and buffer length than to obfuscate the
correlation between the buffer layout between DSPFFD, the actual HLL
buffer [for READ\WRITE], and ruler\scale line on DSPPFM.
Regards, Chuck
midrange.com
