audit fields

We would like to register info about creation and update of a row (and
delete when it comes to temporal tables).

For these lifeCycleEvents', we need to store automatically (once for
creation, always for update) :

- current timestamp,
- transactionId (as known in the journal under Commit cycle ID or maybe
the timestamp where transaction started)
- job
- program
- threadId
- some 'private information' known at thread level (e.g. a sessionId of
the client and the message currently being processed)

We have the following issues :

- the auditing fields are not handled correctly, i.e. when updating a
record, creationAudit is overwritten
- the second FOR EACH ROW ON UPDATE AS (USER) is not accepted (same for
QSYS2.JOBNAME)
- QSYS2.THREAD_ID is not accepted at all

We use this SQL to create the table from scratch on V7R4 :

CREATE OR REPLACE TABLE C##GEN."keyList_toAvoid_SQL7029"

FOR SYSTEM NAME ##PKL
(
"keyList" FOR K3D898 DECIMAL(10, 0) DEFAULT 0
NOT NULL ,

"keyListText" FOR K3D900 CHAR(256) DEFAULT '
' NOT NULL ,
"keyFilter" FOR K3D915 DECIMAL(10, 0) DEFAULT 0
NOT NULL ,
"keyRange" FOR K3D885 DECIMAL(10, 0) DEFAULT 0
NOT NULL ,
--
--"#id" FOR K3#ID DECIMAL(20)
generated as identity ,
--"#changed" FOR K3#CHG FOR EACH ROW ON UPDATE AS ROW
CHANGE TIMESTAMP NOT NULL ,

-- dataChangeOperation : I=Insert, D=Delete, U=Update
"#op" FOR K3#OP CHAR(1) GENERATED
ALWAYS AS (DATA CHANGE OPERATION) NOT NULL ,
-- 1234567890
"#insertTime" FOR K3#I_TIME TIMESTAMP(12)
GENERATED ALWAYS AS ROW BEGIN NOT NULL,
"#insertUser" FOR K3#I_USR VARCHAR(18)
GENERATED ALWAYS AS (USER) NOT NULL,
"#insertJob" FOR K3#I_JOB VARCHAR(28)
GENERATED ALWAYS AS (QSYS2.JOB_NAME) NOT NULL,
-- "#insertThread" FOR K3#I_THRD BIGINT
GENERATED ALWAYS AS (QSYS2.THREAD_ID) NOT NULL,


"#updateTime" FOR K3#U_TIME TIMESTAMP(6) FOR
EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL,
-- "#updateUser" FOR K3#U_USR VARCHAR(18) FOR
EACH ROW ON UPDATE AS (USER) NOT NULL,
-- "#updateJob" FOR K3#U_JOB VARCHAR(28) FOR
EACH ROW ON UPDATE AS (QSYS2.JOB_NAME) NOT NULL,
-- "#updateThread" FOR K3#U_THRD BIGINT FOR
EACH ROW ON UPDATE AS (QSYS2.THREAD_ID) NOT NULL,

-- for temporal tables

"#deleteTime" FOR K3#D_TIME TIMESTAMP(12)
GENERATED ALWAYS AS ROW END NOT NULL,
-- "#deleteUser" FOR K3#D_USR VARCHAR(18)
GENERATED ALWAYS AS (USER) NOT NULL,
-- "#deleteJob" FOR K3#D_JOB VARCHAR(28)
GENERATED ALWAYS AS (QSYS2.JOB_NAME) NOT NULL,
-- "#deleteThread" FOR K3#D_THRD BIGINT
GENERATED ALWAYS AS (QSYS2.THREAD_ID) NOT NULL,

PERIOD SYSTEM_TIME ( "#insertTime" , "#deleteTime" ) ,
--
CONSTRAINT "keyList_primary" PRIMARY KEY
(
"keyList"
)
--
)
ON REPLACE PRESERVE ALL ROWS
;

Customers more and more want to add automatic audit fields in the tables but find it difficult to make them act like they expect them to do.