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.
-- | |
-- | |
-- 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; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment