System period temporal tables were added as a feature built into Db2 for i with IBM i 7.3. This example shows how Temporal could be established for all database files within a specific library.

Temporalize a library.sql

--
--
-- description: find database files and deploy Temporal over them
-- note: The history table will be named <existing-table-name>_HISTORY
-- note: Uncomment the LPRINTF's if you've built this procedure or have it from Db2 for i
-- minvrm: V7R3M0
--

CREATE OR REPLACE PROCEDURE coolstuff.deploy_temporal(target_library varchar(10))
   BEGIN
      DECLARE loopvar BIGINT DEFAULT 0;
      DECLARE v_authorization_list_name CHAR(10);
      DECLARE v_start_time TIMESTAMP;
      DECLARE v_eof INTEGER DEFAULT 0;
      DECLARE Prepare_Attributes VARCHAR(100) default ' ';
      declare sql_statement_text clob(10K) ccsid 37;
      declare objn varchar(128);
      DECLARE obj_cursor CURSOR FOR 
SELECT OBJLONGNAME FROM table(qsys2.object_statistics(target_library, '*FILE')) A
 where objattribute = 'PF' order by objname;
 
      OPEN obj_cursor;
      loop_through_data: BEGIN
                            DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
                               BEGIN
                               SET v_eof = 1;
                         END;                                                       
       l3 : LOOP
           FETCH obj_cursor INTO objn;
           IF (v_eof = 1)
           THEN
              LEAVE l3;
           END IF;
           
           -- Make the table "temporal ready"
           set sql_statement_text = 'ALTER TABLE ' concat target_library concat '.' concat objn concat
 '   ADD COLUMN TEMPORAL_ROW_START TIMESTAMP(12) IMPLICITLY HIDDEN NOT NULL GENERATED ALWAYS AS ROW BEGIN
     ADD COLUMN TEMPORAL_ROW_END   TIMESTAMP(12) IMPLICITLY HIDDEN NOT NULL GENERATED ALWAYS AS ROW END
     ADD COLUMN TEMPORAL_ROW_TRANS TIMESTAMP(12) IMPLICITLY HIDDEN NOT NULL GENERATED ALWAYS AS TRANSACTION START ID
     ADD COLUMN TEMPORAL_audit_user VARCHAR(128) GENERATED ALWAYS AS (SESSION_USER)
     ADD COLUMN TEMPORAL_audit_op  CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION)
     ADD PERIOD SYSTEM_TIME(TEMPORAL_ROW_START, TEMPORAL_ROW_END)';
           -- call systools.lprintf('Executing: ' concat sql_statement_text);
           execute immediate sql_statement_text;

           -- Create the history table
           set sql_statement_text = 'CREATE TABLE ' concat target_library concat '.' concat objn concat
 '_history like '  concat target_library concat '.' concat objn;
           -- call systools.lprintf('Executing: ' concat sql_statement_text);
           execute immediate sql_statement_text;

           -- Enable temporals
           set sql_statement_text = 'ALTER TABLE '  concat target_library concat '.' concat objn concat
 ' ADD VERSIONING USE HISTORY TABLE '  concat target_library concat '.' concat objn concat '_history ON DELETE ADD EXTRA ROW';  
           -- call systools.lprintf('Executing: ' concat sql_statement_text);
           execute immediate sql_statement_text;

        END LOOP; /* L3 */
      CLOSE obj_cursor;
   END loop_through_data;
   
END;

stop;

-- Establish temporal table for all database physical files in this library
call coolstuff.deploy_temporal('LIBNAME');
stop;

-- Review what we've created
select *
  from qsys2.sysperiods
  where table_schema like 'LIBNAME%' order by system_table_schema;