Re: Read all about it - 8 IBM RFE's entered. Vote now!

I think people really need to study temporal tables.

Let's say you have an existing table.

Now let's add a few columns which ARE needed for temporal table support:
Alter table rco
add column system_start TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW
BEGIN
add column system_end TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END
add column transaction_ID TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION
START ID
add PERIOD for SYSTEM_TIME (system_start, system_end)

Now let's the history table
Create table rco_history
for system name rco_hist
like rco;

If you look at DSPFD on rco_hist you will see you are allowed to read,
write, update, and delete rows in rco_hist.

Now let's link them together.
ALTER TABLE rco ADD VERSIONING USE HISTORY TABLE rco_history;

If you look at DSPFD on rco_hist you will see you are no longer allowed to
write or update rows (IDK if you're QSECOFR) in rco_hist. You can purge
out old rows though.

Let's insert a couple of new rows into rco:
Insert into rco (CRSTS, CMPNY, CMPNAM) VALUES ('A', 20, 'Test');
Insert into rco (CRSTS, CMPNY, CMPNAM) VALUES ('A', 25, 'Suburbia');

Then will not yet appear in rco_hist
Select CRSTS, CMPNY, CMPNAM, system_start, system_end, transaction_id
from rco_history;

Let's do some updates:
Update rco set cmpnam='Rural' where cmpny=20;
Delete from rco where cmpny=25;

rco will have
A, 20, Rural, timestampwhenlastupdated, hugetimestampinyear9999
rco_hist will have
A, 20, Test, timestampwhenITwaslastupdated,
timestampwhenitwasnolongeractive
A, 25, Suburbia, timestampwhenitwascreated, timestampwhenitwasdeleted



To query both tables as one does not require any joins, etc. You simply
use the AS OF.
Select CRSTS, CMPNY, CMPNAM, system_start, system_end, transaction_id
from rco
AS OF TIMESTAMP '2016-01-29 13:32:00.000000';
And it will retrieve the values of the rows in that table at that
particular date and time.

More examples:
Select * from rco AS OF TIMESTAMP(‘2016-02-05-10.36.00.000000’);

Select * from rco FOR SYSTEM_TIME FROM '2016-02-05 10:35:00.000000‘
TO '2016-02-05 10:37:00.000000‘;

Select * from rco FOR SYSTEM_TIME BETWEEN '2016-02-05 10:35:00.000000‘
AND '2016-02-05 10:37:00.000000';

Set current temporal system_time='2016-02-05 10:36:00.000000';
Select * from rco;
Set current temporal system_time null;

Some other examples to comtemplate:
Select Division, avg(payrate) from employees
AS OF TIMESTAMP '2016-01-29 13:32:00.000000‘
Group by Division order by division;

Want to employees current rate compared to their rate awhile back?
select a.employee, a.payrate, b.payrate, (b.payrate - a.payrate) as
increase
from employees as of timestamp '2016-01-29 14:19:00.000000' a
join employees b on a.employee=b.employee;

Select item_number, special_price
From pricing AS OF TIMESTAMP '2016-01-29 13:32:00.000000‘;

If you add a new column to the original table it is automatically added to
the history table

Now let's say you add a couple of columns. These aren't really needed for
temporal tables but make sense.
ALTER TABLE rco
ADD COLUMN audit_user VARCHAR(128) GENERATED ALWAYS AS (SESSION_USER)
ADD COLUMN audit_op CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION);
These are automatically added to the history table also.

Now you can tell when a row was "I"nserted, "U"pdated and "D"eleted any by
whom.

I actually did this to the Infor RCO table as a test and did not have to
recompile any RPG using RLA. Mainly because Infor never uses the tables
themselves but only use logical files and those always specify the columns
desired.

Try this on IBM i 7.3. You'll love it.

Rob Berendt