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.
-----Original Message-----
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?
<snip>
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.
</snip>
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzahf/rzahftmprlupdate.htm
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
-----Original Message-----
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
on a.PROD=b.PROD
when matched then update set
desc=b.desc
when not matched then insert
(prod,desc)
values(
b. prod,
b. desc)
midrange.com
