RE: Temporal tables and updates with no data changes

I was hoping someone noticed a line in the documentation that I missed.

Someone else thought it might be a defect but I am not so certain its is. I am wondering if the value assignment of the audit column data is done at a layer that is above the code that evaluates if the whole record has changed. Since the audit columns were updated, the lower layer probably sees a change in the whole record thus the change occurs.

Even if I do open a PMR and it is a problem I still have to make adjustments to my code now as it will go in before any fix can be applied.

-Matt


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vern Hamberg
Sent: Thursday, June 24, 2021 5:45 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: Temporal tables and updates with no data changes

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://urldefense.com/v3/__https://www.ibm.com/docs/en/i/7.3?topic=ad
ministration-working-system-period-temporal-tables__;!!O6xM9Yim9Yk!otI
ik-HWu5HDLpHfkZPOLW9YmsuNfGbvVXEOHNuvlLYBjb_qWpczxWiZf41vonjuohpu$

-Matt

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://urldefense.com/v3/__https://lists.midrange.com/mailman/listinfo/midrange-l__;!!O6xM9Yim9Yk!otIik-HWu5HDLpHfkZPOLW9YmsuNfGbvVXEOHNuvlLYBjb_qWpczxWiZf41vohgdaz76$
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://urldefense.com/v3/__https://archive.midrange.com/midrange-l__;!!O6xM9Yim9Yk!otIik-HWu5HDLpHfkZPOLW9YmsuNfGbvVXEOHNuvlLYBjb_qWpczxWiZf41vogeYHx0X$ .

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://urldefense.com/v3/__https://amazon.midrange.com__;!!O6xM9Yim9Yk!otIik-HWu5HDLpHfkZPOLW9YmsuNfGbvVXEOHNuvlLYBjb_qWpczxWiZf41volAUXLdz$