RE: Re: Temporal Tables and SQL0109

(I meant to reply to RPG list originally.)
I did initially make the audit columns hidden but in discussion in-house, we decided that hiding them was not really of value to us. Regardless of hiding temporal table audit columns or not you still need to use the view technic or explicitly define the insert/update columns. We have a template program system that makes it really easy to start a interactive maintenance program and it uses add and update procedures that use SQL DS insert/update statements. So it was quite easy to just replace a reference to the table with a view without the audit columns.

I created a procedure to add the columns to tables. I only need to add the base columns to my source and when the table is create it adds the audit columns for me.

call TEMPORAL_ADD_AUDIT_COLS( '&LIB', 'MY_Long_TABLE_Name',"FIELD_PREFIX" => 'TE');
or
call TEMPORAL_ADD_AUDIT_COLS( '&LIB', 'MY_TT_TABLE',"FIELD_PREFIX" => 'TE');


SQL PROC Source:
create or replace procedure TEMPORAL_ADD_AUDIT_COLS (
in IN_LIB varchar(10)
,in IN_TABLE varchar(128)
,in FIELD_PREFIX CHAR(2) default 'HS'
)
language SQL
specific TTB01APC01
not deterministic
modifies sql data
called on null input
set option alwblk = *allread
,alwcpydta = *OPTIMIZE
,COMMIT = *NONE
,dbgview = *SOURCE
,decresult = (31 ,31 ,00)
,dyndftcol = *NO
,dynusrprf = *USER
,srtseq = *hex
begin
declare STMT varchar(4096);
declare SQLSTATE char(5) default '00000';
declare SQLSTATE1 char(5);
declare MSG varchar(2048);
declare continue handler for SQLEXCEPTION
begin
set SQLSTATE1 = SQLSTATE;
get diagnostics condition 1 MSG = MESSAGE_TEXT;
end;

set STMT = 'alter table ' ||IN_LIB||'.'|| IN_TABLE
||' add column History_Start_period'
||' for column '||FIELD_PREFIX||'TS_START'
||' timestamp(12) generated always as row begin not null'
||' add column History_End_period'
||' for column '||FIELD_PREFIX||'TS_END'
||' timestamp(12) generated always as row end not null'
||' add column History_Transaction_ID'
||' for column '||FIELD_PREFIX||'RSTS'
||' timestamp(12) generated always as transaction start id not null'
||' add column History_Operation'
||' for column '||FIELD_PREFIX||'RSSTS'
||' char(1) ccsid 37 generated always as (data change operation) not null'
||' add column History_Change_User'
||' for column '||FIELD_PREFIX||'RSUSR'
||' varchar(18) ccsid 37 generated always as (user) not null'
||' add column History_Change_Program'
||' for column '||FIELD_PREFIX||'RSPGM'
||' varchar(255) allocate(21) ccsid 37 generated always as (current CLIENT_PROGRAMID) not null'
||' add column History_Change_Job'
||' for column '||FIELD_PREFIX||'RSJOB'
||' varchar(28) allocate(28) ccsid 37 generated always as (QSYS2.JOB_NAME) not null'
||' add period system_time (HISTORY_START_PERIOD, HISTORY_END_PERIOD)'
;
execute immediate STMT;

set STMT = 'label on column ' ||IN_LIB||'.'|| IN_TABLE
||'('
||' '||FIELD_PREFIX||'TS_START is ''Start Timestamp of History Table Data'''
||','||FIELD_PREFIX||'TS_END is ''End Timestamp of History Table Data'''
||','||FIELD_PREFIX||'RSTS is ''Record Status Timestamp'''
||','||FIELD_PREFIX||'RSSTS is ''Record Status U/I/D'''
||','||FIELD_PREFIX||'RSUSR is ''Record Status User'''
||','||FIELD_PREFIX||'RSPGM is ''Record Status Program'''
||','||FIELD_PREFIX||'RSJOB is ''Record Status Job'''
||')';
execute immediate STMT;

set STMT = 'label on column ' ||IN_LIB||'.'|| IN_TABLE
||'('
||' '||FIELD_PREFIX||'TS_START text is ''Start Timestamp of History Table Data'''
||','||FIELD_PREFIX||'TS_END text is ''End Timestamp of History Table Data'''
||','||FIELD_PREFIX||'RSTS text is ''ID Timestamp of History Data. Used for commits.'''
||','||FIELD_PREFIX||'RSSTS text is ''U/I/D Update/Insert/Delete. Operation performed.'''
||','||FIELD_PREFIX||'RSUSR text is ''User of operation. Can''''t edit or override.'''
||','||FIELD_PREFIX||'RSPGM text is ''Pgm of operation. Change using WLM_SET_CLIENT_INFO'''
||','||FIELD_PREFIX||'RSJOB text is ''Job of operation. Can''''t edit or override.'''
||')';
execute immediate STMT;
end;

label on specific procedure TTB01APC01
is 'Add Audit Cols for Temporary/History Table';

-Matt