That's talking about commitment control. So, you can make multiple updates to the record under one commitment transactions, and once you commit, only one history table record will be written. That doesn't help with the current situation much unfortunately.
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob Berendt
Sent: Friday, January 10, 2020 12:22 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL MERGE and temporal tables overpopulated history table
What defines "a single transaction" in the following context?
If a single transaction updates the same row multiple times, only one history row is generated and that row reflects the state of the record before any changes were made by the transaction.
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
Kendallville, IN 46755
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Darren Strong
Sent: Friday, January 10, 2020 11:59 AM
To: midrange-l General Questions (midrange-l@xxxxxxxxxxxxxxxxxx) <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL MERGE and temporal tables overpopulated history table
I have a table I created as a temporal table using a command as follows:
create table darren/tstp
(prod char(35) not null with default,
desc char(50) not null with default,
ChgUsr varchar(18) implicitly hidden generated always as (USER),
Start_TS timestamp(12) implicitly hidden not null generated always as row begin,
End_TS timestamp(12) implicitly hidden not null generated always as row end,
TS_ID timestamp(12) implicitly hidden generated always as transaction start id,
period system_time (start_ts, end_ts)
ALTER TABLE TSTP ADD VERSIONING USE HISTORY TABLE TSTP_H;
If I run an SQL MERGE similar to the following, where the QTEMP table has the same data as the target table, I get a history table record every time the merge is run. Is there an option I'm missing to basically say, don't update the table if none of the fields are different? I realize I could perhaps write a compare for each field, but sometimes a file might have hundreds of fields, so, I was hoping for something a little less laborious. An SQL update does the same thing (a history record for every non-update), but, I looked at the MERGE, hoping it was a little more intelligent, so, if there is something different that could be done with the UPDATE, that might be interesting too.
merge into darren/tstp a using
(select * from QTEMP/tstp) b
when matched then update set
when not matched then insert
As an Amazon Associate we earn from qualifying purchases.