Re: Temporal tables and updates with no data changes

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Hey Matt

I would call IBM i support - they are good regarding these things.

Some of the DB team are on these lists - they might reply already, but 
no promises on that, right?

Could also be a candidate for an RFE.

Cheers
Vern

On 6/24/2021 4:07 PM, Tyler, Matt wrote:
> I am implementing temporal tables (TT) for a small system to iron out the wrinkles before allowing them in our larger products. So far so good with one wrinkle, I do not really like.
>
> When I perform an update to a TT where any column (regular) listed in the update statement contains no data changes, the system will creates a history record and modifies the current records row-begin timestamp.   I know that I can do a pre-check before hand to ensure any of the data is modified or not but that seems at odds when comparing to a typical table without the audit columns in a TT.    An update on typical table without any data modified reports the update successful but looking into the journals nothing is recorded.
>
> We implement SQL base routines for newer programs that add, update or delete using record DSs.  Reading the IBM documentation, it states (I don't recall where) that even if you modify one of the audit columns in your RPG code the system will not allow those changes to be applied and will instead use its own values.  So there is no harm in performing updates using a DS that is made up of all the actual columns of a TT.   Using DS' to perform adds and updates make the code logic smaller.  Using DS', updates to typical tables, I don't normally need to check the column data for changes.
>
>
> My only interest in this is to prevent the history table from getting too large if the programmer does not account for how the TT updates work when no data is modified.
>
> I have tested with updates that only select one column in the statement and the system still records that changes occurred.
>
> What I am looking for is am IBM reference that indicates this happening.  I have not noticed it (yet) in the online documentation in the link below.   Any help would be welcome.
>
> IBM documentation for those interested.
> https://www.ibm.com/docs/en/i/7.3?topic=administration-working-system-period-temporal-tables
>
> -Matt