Performing point in time queries against Infor data just using utilities built into the OS.

Please read the caveat at the end also.

IBM has added some features in IBM i 7.3 which should allow you to perform
point in time queries against Infor data just by using stuff built into
IBM i 7.3.
This new feature is called "Temporal Tables".
I will give you a sample.
We'll start out with a very simple Infor table: Company Master (RCO).
One thing to keep in mind is that Infor's RPG programs normally do not
access the physical files. They go against the logical files instead. So
instead of processing the RCO table directly they'll go against the
logical files instead like RCOL01. The reason this is important is that
we can add columns (fields) to the RCO file and we will not have to
recompile any of the logical files, nor any of the RPG code and we will
not get any record format level checks. The new columns are only in the
PF, not the LF's.

First I used SQL to create a test library.
create schema rob730;

Then, while still in SQL I changed that to my default library
Set current schema rob730;

The I used a command to copy my data into that test library
SAVOBJ OBJ(RCO RCOL*) LIB(ERPLXF) DEV(*SAVF) SAVF(ROB/ROB)
RSTOBJ OBJ(*ALL) SAVLIB(ERPLXF) DEV(*SAVF) SAVF(ROB/ROB) RSTLIB(ROB730)
STRJRNPF FILE(ROB730/RCO) JRN(ROB730/QSQJRN) IMAGES(*BOTH)

Then I will add three columns. Two of these columns will be used for
calculating when a row was active and when it was no longer active. This
is indicated by the "period for system time" clause. The transactiond_id
column will be used for something to do with this but it gets kind of
technical and I want to keep this simple for now.
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);
Initially all your existing rows in this table will have system_start set
to 0001-01-01 00.00.00.000000000000 and system_end set to 9999-12-30
00.00.00.000000000000

Now we'll create a history file:
Create table rco_history
for system name rco_hist
like rco;
It will have the exact same columns as the rco table.

Now, to enable temporal tracking, which ties the RCO table to the history
table RCO_HISTORY, you use this statement
ALTER TABLE rco ADD VERSIONING USE HISTORY TABLE rco_history;

Normally you would use company master maintenance to add rows to your rco
table. I just used SQL for brevity.
Insert into rco (CRSTS, CMPNY, CMPNAM) VALUES ('A', 20, 'Test');
Insert into rco (CRSTS, CMPNY, CMPNAM) VALUES ('A', 25, 'Suburbia');

If you look at the RCO table you will see:
CRSTS CMPNY, CMPNAM, SYSTEM_START, SYSTEM_END
A 20 Test 2016-01-29 13:22:24.172265000244 9999-12-30
00:00:00.000000000000
A 25 Suburbia 2016-01-29 13:30:05.684039000244 9999-12-30
00:00:00.000000000000
Since these were only additions you will not see any rows in RCO_HIST yet.

Now let's do an update
Update rco set cmpnam='Rural' where cmpny=20;
and a delete
Delete from rco where cmpny=25;

We'll see that the update added a row to the history table and updated the
row in the RCO.
The delete moved the row from the master to the history.

RCO
CRSTS CMPNY, CMPNAM, SYSTEM_START, SYSTEM_END
A 20 Rural 2016-01-29 13:33:39.499891000244 9999-12-30
00:00:00.000000000000
History
A 20 Test 2016-01-29 13:22:24.172265000244 2016-01-29
13:33:39.499891000244
A 25 Suburbia 2016-01-29 13:30:05.684039000244 2016-01-29
13:33:58.833539000244
Pay close attention to the timestamps.
You can tell that until 2016-01-29 13:33:39 the name of company 20 was
Test. After that it was called Rural.

Now, you do NOT have to keep track of whether or not you want to query the
main table or the history table. As of IBM i 7.3 that is unnecessary. You
can simply do this:
Select CRSTS, CMPNY, CMPNAM, system_start, system_end, transaction_id
from rco
AS OF TIMESTAMP '2016-01-29 13:32:00.000000';

or you could do this:
SET CURRENT TEMPORAL SYSTEM_TIME = '2016-01-29 13:32:00.000000';
SELECT * FROM rco;

And you would get.
CRSTS CMPNY, CMPNAM, SYSTEM_START, SYSTEM_END
A 20 Test 2016-01-29 13:22:24.172265000244 2016-01-29
13:33:39.499891000244
A 25 Suburbia 2016-01-29 13:30:05.684039000244 2016-01-29
13:33:58.833539000244

Now, isn't that easy?

You have to use some decent tools to be able to use "as of timestamp". In
other words, Query/400 isn't going to cut it. Check out STRQM which is an
alternative to Query/400 that many shops have. It's part of 5770-ST1. You
can also use something call "Run SQL Scripts". It's a freebie from IBM.
There are other tools like RUNSQLSTM (another freebie from IBM). I used
"Run SQL Scripts" for all this.

Here are some 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;

You could use this to find out your inventory levels as of last period
end.
Find your GL balances before/after that huge posting program.
Compare the pay rates of all your employees between now and a year ago.

More samples:

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

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‘;

We used to do farming like this by using journal receivers and a lot of
scraping of the data out of those. This is MUCH cleaner!

This does not require you purchase any third party products and install
those, pay annual maintenance on them, and then be concerned if you go to
upgrade your OS if they have gotten around to testing their product on the
new release.

I'll be honest, keeping history data around will consume some disk space.
The cost of disk sure isn't what it used to be though.

While you are allowed to purge out old rows from the history table you
cannot update any rows in it. You cannot add any rows into it.
As soon as you do
ALTER TABLE rco ADD VERSIONING USE HISTORY TABLE rco_history;
It turns on these attributes (as seen in DSPFD RCO_HIST)
Allow read operation . . . . . . . . . . . : Yes
Allow write operation . . . . . . . . . . . : No
Allow update operation . . . . . . . . . . : ALWUPD *NO
Allow delete operation . . . . . . . . . . : ALWDLT *YES
It changes these attributes back when you DROP VERSIONING.

If you were to add another column to the RCO table it will automatically
add it to the RCO_HIST file.
I'll show you an example, and how to do some additional tracking.
Alter table rco Drop versioning;
ALTER TABLE rco ADD VERSIONING USE HISTORY TABLE rco_history
on delete add extra row;
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);

We added a column, audit_user, to track WHO did these updates.
We also added a column, audit_op, to track whether the last update was an
add, change, delete.
These two columns are some new items which also came out in IBM i 7.3.

Insert into rco (CRSTS, CMPNY, CMPNAM) VALUES ('A', 30, 'Thirsty');
update rco set cmpnam='Thirty' where cmpny=30;
delete from rco where cmpny=30;
select cmpny, cmpnam, audit_user, audit_op, system_start, system_end
from rco_history
where cmpny='30';

CMPNY, CMPNAM AUDIT_USER AUDIT_OP SYSTEM_START, SYSTEM_END
30 Thirty ROB I 2016-02-05 10:35:57.871821000244
2016-02-05 10:36:03.971156000244
30 Thirsty ROB U 2016-02-05 10:36:03.971156000244
2016-02-05 10:36:07.033435000244
30 Thirsty ROB D 2016-02-05 10:36:07.033435000244
2016-02-05 10:36:07.033435000244

I ran the existing ACR120 after adding these columns and it worked fine.
All the changes were automatically tracked. No recompiling needed.

Now for the caveat.
I ran this on one table, RCO. I did not test every possible program which
may touch RCO. Whether or not any Infor programs access the root PF
directly versus the logical files is up to you to check.

I had no trouble running program ACR125B which does this:
D RCODS E DS EXTNAME(RCO)
...
C* EXECSQL DECLARE C CURSOR FOR
C* SELECT * FROM RCO
C* WHERE CMPNY BETWEEN :W1LWCO AND :W1UPCO AND CRSTS = 'A'
C* ORDER BY CMPNY FOR FETCH ONLY
...
C* EXECSQL FETCH C INTO :RCODS
Again, without recompiling.

Rob Berendt