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


-- 
This is the BPCS ERP System (BPCS-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/bpcs-l
or email: [email protected]
Before posting, please take a moment to review the archives
at http://archive.midrange.com/bpcs-l.