Re: SQL Comparing two identical files for differences with log file

What I propose may not be an exact "what has changed between these two
files" scenario it may be useful, and also have the desired log file.

In summary:
Add temporal support and query this:

select ilid, ilinvn, illine, audit_op, system_start, system_end
from sil_history
where system_start > current timestamp - 10 minutes
order by system_start desc;

IX 2000000 1 D 2017-05-19 10:36:40.734954000244
2017-05-19 10:36:40.734954000244
IX 2000000 1 U 2017-05-19 10:36:38.249088000244
2017-05-19 10:36:40.734954000244
IL 2000000 1 I 2017-05-19 10:35:57.686969000244
2017-05-19 10:36:38.249088000244


In detail (all tested from within Run SQL Scripts):

create schema robsil;

Set current schema robsil;

CL: CLRSAVF ROB/ROB;

CL: SAVOBJ OBJ(SIL SILL*) LIB(ERPLXF) DEV(*SAVF) SAVF(ROB/ROB);

CL: RSTOBJ OBJ(*ALL) SAVLIB(ERPLXF) DEV(*SAVF) SAVF(ROB/ROB)
RSTLIB(ROBSIL);

-- If you already journal this file (Mimix shop?) turn off old journal
CL: ENDJRNPF FILE(ROBSIL/SIL);

CL: STRJRNPF FILE(ROBSIL/SIL) JRN(ROBSIL/QSQJRN) IMAGES(*BOTH);

-- Just confirming we are in our test schema
Set current schema robsil;

Alter table sil
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 COLUMN audit_user VARCHAR(128) GENERATED ALWAYS AS (SESSION_USER)
ADD COLUMN audit_op CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION)
add PERIOD for SYSTEM_TIME (system_start, system_end);

Create table sil_history
for system name sil_hist
like sil;

ALTER TABLE sil ADD VERSIONING USE HISTORY TABLE sil_history on delete add
extra row;

insert into sil (ilinvn, illine, ilid) values (2000000, 1, 'IL');

update sil
set ilid='IX'
where ilinvn=2000000 and illine=1;

delete from sil
where ilinvn=2000000 and illine=1;

select ilid, ilinvn, illine, audit_op, system_start, system_end
from sil_history
where system_start > current timestamp - 10 minutes
order by system_start desc;

Since LX uses the logical files to get to the data,
and their logical files select individual columns and use a different
record format than the physical,
your RPG won't suffer any record format level checks.



Rob Berendt