Managing Ever-Increasing Amounts of Data with IBM DB2 for z/OS: Using Temporal Data Management, Archive Transparency, and the DB2 Analytics Accelerator

Readers' comments

Readers' comments (18) 

lockedThis discussion is now locked


Posted by Hari Shanmugadhasan on 22 December 2015 at 13:51

Please clarify, p16 "The row-end column in the system-period temporal table will always contain a maximum value for the data type of the column. " which seems to be contradicted by p19 "When you add the timestamp columns to an existing table, all the existing rows get the value 9999-12-30-00.00.00.000000000000 in the system_end column, indicating that all rows are current rows." since this is not the max value for the TIMESTAMP(12) data type.

Posted by Hari Shanmugadhasan on 22 December 2015 at 13:55

Please clarify, p19 "When you add the timestamp columns to an existing table, all the existing rows get the value 9999-12-30-00.00.00.000000000000 in the system_end column, indicating that all rows are current rows." which seems to be contradicted by
p23 "When using DB2 data sharing, the timestamp(12) value is composed of a timestamp(9) value concatenated with 3 trailing bytes identifying the data sharing member." since p19 doesn't show such a value.

Posted by Hari Shanmugadhasan on 22 December 2015 at 13:57

It would be helpful to explain the value and implications of p23 "When using DB2 data sharing, the timestamp(12) value is composed of a timestamp(9) value concatenated with 3 trailing bytes identifying the data sharing member." instead of simply using the real TIMESTAMP(12).

Posted by Hari Shanmugadhasan on 22 December 2015 at 13:59

It would be helpful to state that in a data sharing environment midnight isn't midnight for system-period temporal tables thanks to the data sharing member fudge.

Posted by Hari Shanmugadhasan on 22 December 2015 at 14:02

Please clarify, p23 "concatenated with 3 trailing bytes identifying the data sharing member." seems to imply that 3 bytes of storage are added. Is the real situation that it is 3 trailing digits not bytes? So less of a storage requirement?

Posted by Hari Shanmugadhasan on 22 December 2015 at 14:04

Please clarify the value of having "the data sharing member" in the three system-period columns.

Posted by Hari Shanmugadhasan on 22 December 2015 at 14:14

Please clarify why it wouldn't be better to use real TIMESTAMP(12) values for system-period temporal tables. This Redbook doesn't indicate any uniqueness requirement, in fact, p19 states that all existing rows get the same system start and end timestamps without mention of data sharing member. p16 states something similar for the row_end column without mention of data sharing member.

Posted by Hari Shanmugadhasan on 22 December 2015 at 14:30

Please clarify, p19 seems to contradict the DB2 11 manual which states "A value for a transaction-start-ID column is composed of a TIMESTAMP(9) value that is unique per transaction per data sharing member followed by 3 digits that indicate the data sharing member number." for a column with no use. Also, "A value for a row-begin column is composed of a TIMESTAMP(9) value that is unique per transaction per data sharing member . . ." P16 and p23 don't mention unique

Posted by Hari Shanmugadhasan on 22 December 2015 at 14:33

It would be helpful if the Redbook had a good index. There doesn't seem to be any index currently at least not in the pdf that I downloaded.

Posted by Hari Shanmugadhasan on 22 December 2015 at 14:55

Please clarify how this very narrow p25 uniqueness requirement is enforced, "the history table must not contain two or more rows with the same key and overlapping periods" in the non-migration scenario when the manual states " If multiple rows are inserted with a single SQL unit of work, the values for the transaction start timestamp column are the same." while saying nothing about multiple rows being updated which suggest all row begin values are the same for the whole UOW.

Posted by Hari Shanmugadhasan on 22 December 2015 at 14:59

Please clarify how the previously mentioned p25 uniqueness requirement works with p27 "The values of the columns in the history table that correspond to the row-begin and row-end columns, have the same value as the first row, reflecting the time of the deletion and the values of the initial row." which sounds like overlapping.

Posted by Hari Shanmugadhasan on 23 December 2015 at 11:50

Please clarify the p25 rule against "two or more rows with the same key and overlapping periods" which would seem to occur quite naturally if there were a sequence of UOWs updating the same key during the time change "fall back" when clocks go back an hour, so a later update could overlap with a previous update if the timestamps are not defined WITH TIME ZONE like the Redbook DDL. examples.

Posted by Hari Shanmugadhasan on 23 December 2015 at 11:54

Please clarify how audit, compliance, and end user confusion can be avoided when using WITHOUT TIME ZONE when the clocks go back in time in the Fall since system-period timestamp values couldn't provide an accurate sequence of events.

Posted by Hari Shanmugadhasan on 23 December 2015 at 11:58

It would be helpful to explain the pros and cons of defining the system-period columns WITH TIME ZONE. On the surface it would seem to make auditing, compliance, and querying easier WITH TIME ZONE with only the cost of two extra bytes per column.

Posted by Hari Shanmugadhasan on 23 December 2015 at 12:22

It would be helpful if the Redbook indicated the pros and cons of using the transaction start id or row begin timestamp columns for optimistic locking or any of the other uses of a row change timestamp column since the table isn't archiveable. The use of a single timestamp value for multiple rows in a UOW instead of possibly different values doesn't seem to block the use for optimistic locking. It isn't clear you would want them to be different with precision of only 6 and no TIME ZONE option.

Posted by Hari Shanmugadhasan on 23 December 2015 at 12:38

My previous comment was in the context of p5 "Temporal tables are mutually exclusive with archive transparency. A system-period temporal table or application-period temporal table cannot be defined as an archive-enabled
table or archive table." But that seems to leave open the possibility that a temporal history table could be archive-enabled. Please clarify if that is true.

Posted by Hari Shanmugadhasan on 23 December 2015 at 12:51

Please confirm that a temporal history table is vulnerable to utilities and SQL deleting and updating rows since p37 only restricts utilities against the base and the only history DML restriction is "Cannot use the UPDATE and DELETE syntax that specifies the BUSINESS_TIME period on the history table." And manual only states, "On the history table, you cannot use the UPDATE, DELETE, or SELECT statement syntax that specifies the application period."

Posted by Hari Shanmugadhasan on 23 December 2015 at 12:53

Further, please confirm that one can also create fake temporal history rows by using LOAD RESUME, Insert, and Merge against the history table.


Profile

Publish Date
25 September 2015

Last Update
20 October 2015


Rating: Not yet rated


Author(s)

ISBN-10
0738440965

IBM Form Number
SG24-8316-00