Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.