Re: TIMESTAMP(12) with System Period Temporal Tables

On Sat, 2018-04-28 at 22:08 +0200, Joep Beckeringh wrote:

Jonathan,

Temporal tables need a few specific columns
(<https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/rzahf/rzahftmprltt.htm>);
row start and row end are automatically filled by the system. These
column contain the values ending on '000244'.

Having looked at that section, it does indeed seems as if the date
should be "maintained by DB2" so taken out of the hands of the program
code (even unseen automagically generated code).

While the examples given on the IBM site are just that, examples, its
interesting to note that they also truncate the date
( https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/rzahf/rzahftmprldelete.htm#rzahftmprldelete ) in the examples given as all the dates have at least 6 zeros (000000) in them.

I guess as a final test you could try inserting a batch of test records,
in a test file, via SQL and see if the date also suffers from truncation
and/or 000244 substitution.

According to this doc
( http://enterprisesystemsmedia.com/article/print/traveling-through-time-with-temporal-tables-in-db2-10-for-luw ) which is from db2-10 luw (so not IBMi) "These columns use a TIMESTAMP(12) data type to provide the precision necessary to prevent duplicate rows. Time periods apply to system time and business time. A period indicates the starting and ending points of a time interval." so it seems odd that the system isn't supplying a detailed microsecond time.

Perhaps its either a bug, or the system is cheating and is preventing
duplicates by playing with the fractional time but not actually using a
true microsecond value. I guess the next stage is a call to IBM in order
to find out.

Joep Beckeringh