Last active
December 1, 2022 15:00
-
-
Save forstie/60aec92e2efccc22a4544d43b1c612f3 to your computer and use it in GitHub Desktop.
This example shows how easy it is to pull together a data streaming service using SQL, JSON, triggers, and a Data Queue.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
x---------------------------------------------------------------------------------------------------- | |
-- | |
-- Title: Demonstrate how to capture changes being made to a table, publish the changes using | |
-- JSON publishing functions, and send the detail to a data queue | |
-- Author: Scott Forstie | |
-- Date: June, 2020 | |
-- | |
-- | |
---------------------------------------------------------------------------------------------------- | |
-------------------------------------------------------------------------------------------------- | |
-- | |
-- Setup a sample Bank - Branch Office | |
-- | |
-------------------------------------------------------------------------------------------------- | |
drop SCHEMA BRANCH_OFFICE_099 cascade ; | |
CREATE SCHEMA BRANCH_OFFICE_099 FOR SCHEMA BANKONOSS ; | |
-------------------------------------------------------------------------------------------------- | |
-- | |
-- Customers table | |
-- | |
-------------------------------------------------------------------------------------------------- | |
CREATE OR REPLACE TABLE BRANCH_OFFICE_099.CUSTOMERS ( | |
CUSTOMER_ID FOR COLUMN CUSTID INTEGER GENERATED ALWAYS AS IDENTITY ( | |
START WITH 1 INCREMENT BY 1 | |
NO MINVALUE NO MAXVALUE | |
NO CYCLE NO ORDER | |
CACHE 20 ) | |
, | |
CUSTOMER_NAME FOR COLUMN CUSTNAME VARCHAR(30) CCSID 37 NOT NULL , | |
CUSTOMER_ADDRESS FOR COLUMN CUSTADDR VARCHAR(300) CCSID 37 NOT NULL , | |
CUSTOMER_CITY FOR COLUMN CUSTCITY VARCHAR(30) CCSID 37 NOT NULL , | |
CUSTOMER_STATE FOR COLUMN CUSTSTATE CHAR(2) CCSID 37 NOT NULL , | |
CUSTOMER_PHONE FOR COLUMN CUSTPHONE CHAR(20) CCSID 37 NOT NULL , | |
CUSTOMER_EMAIL FOR COLUMN CUSTEMAIL VARCHAR(30) CCSID 37 NOT NULL default 'not set' , | |
CUSTOMER_TAX_ID FOR COLUMN CUSTTAXID CHAR(16) CCSID 37 NOT NULL NOT NULL default 'not set' , | |
CUSTOMER_DRIVERS_LICENSE_NUMBER FOR COLUMN CUSTLIC CHAR(16) CCSID 37 NOT NULL default 'not set' , | |
CUSTOMER_LOGIN_ID FOR COLUMN CUSTLOGIN VARCHAR(30) CCSID 37 NOT NULL default 'not set' , | |
CUSTOMER_SECURITY_QUESTION FOR COLUMN CUSTQUERY VARCHAR(100) CCSID 37 NOT NULL default 'not set' , | |
CUSTOMER_SECURITY_QUESTION_ANSWER FOR COLUMN CUSTANS VARCHAR(100) CCSID 37 NOT NULL default 'not set' , | |
INSERT_TIMESTAMP FOR COLUMN WHENINS TIMESTAMP NOT NULL DEFAULT | |
CURRENT_TIMESTAMP IMPLICITLY HIDDEN , | |
UPDATE_TIMESTAMP FOR COLUMN WHENUPD | |
TIMESTAMP GENERATED ALWAYS FOR EACH ROW ON UPDATE | |
AS ROW CHANGE TIMESTAMP NOT NULL IMPLICITLY HIDDEN , | |
CONSTRAINT BRANCH_OFFICE_099.CUSTOMER_ID_PK PRIMARY KEY( CUSTOMER_ID ), | |
CONSTRAINT BRANCH_OFFICE_099.CUSTOMER_LOGIN_ID_UK | |
UNIQUE( CUSTOMER_LOGIN_ID ) ) ON REPLACE PRESERVE ROWS; | |
truncate BRANCH_OFFICE_099.CUSTOMERS ; | |
-------------------------------------------------------------------------------------------------- | |
-- | |
-- Accounts table | |
-- | |
-------------------------------------------------------------------------------------------------- | |
CREATE OR REPLACE TABLE BRANCH_OFFICE_099.ACCOUNTS ( | |
ACCOUNT_ID INTEGER GENERATED ALWAYS AS IDENTITY ( | |
START WITH 1 INCREMENT BY 1 | |
NO MINVALUE NO MAXVALUE | |
NO CYCLE NO ORDER | |
CACHE 20 ) | |
, | |
CUSTOMER_ID FOR COLUMN CUSTID INTEGER NOT NULL , | |
ACCOUNT_NUMBER FOR COLUMN ACCOUNTNO VARCHAR(10) CCSID 37 NOT NULL default '0000000000', | |
ACCOUNT_NAME FOR COLUMN ACCOUNTNAM CHAR(50) CCSID 37 NOT NULL , | |
ACCOUNT_DATE_OPENED FOR COLUMN OPENDATE DATE DEFAULT CURRENT_DATE , | |
ACCOUNT_DATE_CLOSED FOR COLUMN CLOSEDATE DATE DEFAULT NULL , | |
ACCOUNT_CURRENT_BALANCE FOR COLUMN ACCTBAL DECIMAL(11, 2) NOT NULL DEFAULT 0 | |
, | |
INSERT_TIMESTAMP FOR COLUMN INSDATE | |
TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP IMPLICITLY HIDDEN , | |
UPDATE_TIMESTAMP FOR COLUMN UPDDATE | |
TIMESTAMP GENERATED ALWAYS FOR EACH ROW ON UPDATE | |
AS ROW CHANGE TIMESTAMP NOT NULL IMPLICITLY HIDDEN , | |
CONSTRAINT BRANCH_OFFICE_099.ACCOUNT_ID_PK PRIMARY KEY( ACCOUNT_ID ), | |
CONSTRAINT BRANCH_OFFICE_099.ACCOUNT_CUSTOMER_ID_FK | |
FOREIGN KEY( CUSTOMER_ID ) | |
REFERENCES BRANCH_OFFICE_099.CUSTOMERS ( CUSTID ) | |
ON DELETE RESTRICT | |
ON UPDATE RESTRICT ) ON REPLACE PRESERVE ROWS; | |
-------------------------------------------------------------------------------------------------- | |
-- | |
-- Transactions table | |
-- | |
---------------------------------------------------------------------------------------------------- | |
CREATE TABLE BRANCH_OFFICE_099.TRANSACTIONS FOR SYSTEM NAME TRANS ( | |
TRANSACTION_ID FOR COLUMN TRANSID INTEGER GENERATED ALWAYS AS IDENTITY ( | |
START WITH 1 INCREMENT BY 1 | |
NO MINVALUE NO MAXVALUE | |
NO CYCLE NO ORDER | |
CACHE 20 ) | |
); | |
CREATE OR REPLACE TABLE BRANCH_OFFICE_099.TRANSACTIONS FOR SYSTEM NAME TRANS ( | |
TRANSACTION_ID FOR COLUMN TRANSID INTEGER GENERATED ALWAYS AS IDENTITY ( | |
START WITH 1 INCREMENT BY 1 | |
NO MINVALUE NO MAXVALUE | |
NO CYCLE NO ORDER | |
CACHE 20 ) | |
, | |
ACCOUNT_ID INTEGER NOT NULL , | |
TRANSACTION_TYPE FOR COLUMN TRANTYPE CHAR( 1 ) CCSID 37 NOT NULL , | |
TRANSACTION_DATE FOR COLUMN TRANDATE DATE NOT NULL DEFAULT CURRENT_DATE , | |
TRANSACTION_TIME FOR COLUMN TRANTIME TIME NOT NULL DEFAULT CURRENT_TIME , | |
TRANSACTION_AMOUNT FOR COLUMN TRANAMT DECIMAL(11, 2) NOT NULL , | |
INSERT_TIMESTAMP FOR COLUMN WHENINS TIMESTAMP NOT NULL DEFAULT | |
CURRENT_TIMESTAMP IMPLICITLY HIDDEN , | |
UPDATE_TIMESTAMP FOR COLUMN WHENUPD | |
TIMESTAMP GENERATED ALWAYS FOR EACH ROW ON UPDATE | |
AS ROW CHANGE TIMESTAMP NOT NULL IMPLICITLY HIDDEN, | |
CONSTRAINT BRANCH_OFFICE_099.TRANSACTION_ID_PK PRIMARY KEY( TRANSACTION_ID ), | |
CONSTRAINT BRANCH_OFFICE_099.TRANSACTIONS_ACCOUNT_ID_FK | |
FOREIGN KEY( ACCOUNT_ID ) | |
REFERENCES BRANCH_OFFICE_099.ACCOUNTS ( ACCOUNT_ID ) | |
ON DELETE RESTRICT | |
ON UPDATE RESTRICT ) ON REPLACE PRESERVE ROWS; | |
---------------------------------------------------------------------------------------------------- | |
-- | |
-- Public and private authorizations | |
-- | |
---------------------------------------------------------------------------------------------------- | |
CL: GRTOBJAUT OBJ(BANKONOSS) OBJTYPE(*LIB) USER(*PUBLIC) AUT(*ALL); | |
stop; | |
---------------------------------------------------------------------------------------------------- | |
-- | |
-- Create the data queue | |
-- | |
---------------------------------------------------------------------------------------------------- | |
cl:CRTDTAQ DTAQ(BANKONOSS/HANDOFF_DQ) MAXLEN(64000) SENDERID(*YES) SIZE(*MAX2GB) TEXT('row level changes in BANKONOSS'); | |
create or replace variable BANKONOSS.dq_json clob(64000) ccsid 1208; | |
stop; | |
---------------------------------------------------------------------------------------------------- | |
-- | |
-- Triggers | |
-- | |
---------------------------------------------------------------------------------------------------- | |
create or replace trigger branch_office_099.customers_trigger | |
after update or insert or delete on branch_office_099.customers | |
referencing new as n old as o for each row | |
when (inserting or updating or deleting) | |
begin atomic | |
declare operation varchar(10) for sbcs data; | |
if inserting then | |
set operation = 'INSERT'; | |
end if; | |
if deleting then | |
set operation = 'DELETE'; | |
end if; | |
if updating then | |
set operation = 'UPDATE'; | |
end if; | |
if (inserting or updating) then | |
set BANKONOSS.dq_json = JSON_OBJECT(KEY 'table' VALUE 'customers', | |
KEY 'operation' VALUE operation, | |
KEY 'row' VALUE | |
JSON_OBJECT( | |
KEY 'customer_id' VALUE n.customer_id, | |
KEY 'customer_name' VALUE n.customer_name, | |
KEY 'customer_address' VALUE n.customer_address, | |
KEY 'customer_city' VALUE n.customer_city, | |
KEY 'customer_state' VALUE n.customer_state, | |
KEY 'customer_phone' VALUE n.customer_phone, | |
KEY 'customer_email' VALUE n.customer_email, | |
KEY 'customer_tax_id' VALUE n.customer_tax_id, | |
KEY 'customer_drivers_license_number' VALUE n.customer_drivers_license_number, | |
KEY 'customer_login_id' VALUE n.customer_login_id, | |
KEY 'customer_security_question' VALUE n.customer_security_question, | |
KEY 'customer_security_question_answer' VALUE n.customer_security_question_answer, | |
KEY 'insert_timestamp' VALUE n.insert_timestamp, | |
KEY 'update_timestamp' VALUE n.update_timestamp | |
)); | |
else | |
set BANKONOSS.dq_json = JSON_OBJECT(KEY 'table' VALUE 'customers', | |
KEY 'operation' VALUE operation, | |
KEY 'row' VALUE | |
JSON_OBJECT( | |
KEY 'customer_id' VALUE o.customer_id, | |
KEY 'customer_name' VALUE o.customer_name, | |
KEY 'customer_address' VALUE o.customer_address, | |
KEY 'customer_city' VALUE o.customer_city, | |
KEY 'customer_state' VALUE o.customer_state, | |
KEY 'customer_phone' VALUE o.customer_phone, | |
KEY 'customer_email' VALUE o.customer_email, | |
KEY 'customer_tax_id' VALUE o.customer_tax_id, | |
KEY 'customer_drivers_license_number' VALUE o.customer_drivers_license_number, | |
KEY 'customer_login_id' VALUE o.customer_login_id, | |
KEY 'customer_security_question' VALUE o.customer_security_question, | |
KEY 'customer_security_question_answer' VALUE o.customer_security_question_answer, | |
KEY 'insert_timestamp' VALUE o.insert_timestamp, | |
KEY 'update_timestamp' VALUE o.update_timestamp | |
)); end if; | |
call qsys2.send_data_queue_utf8( | |
message_data => BANKONOSS.dq_json, | |
data_queue => 'HANDOFF_DQ', | |
data_queue_library => 'BANKONOSS'); | |
end; | |
stop; | |
values BANKONOSS.dq_json; | |
---------------------------------------------------------------------------------------------------- | |
-- | |
-- Load some sample data into CUSTOMERS | |
-- | |
---------------------------------------------------------------------------------------------------- | |
delete from BRANCH_OFFICE_099.CUSTOMERS; | |
INSERT INTO BRANCH_OFFICE_099.CUSTOMERS | |
(CUSTOMER_ID, | |
CUSTOMER_NAME, | |
CUSTOMER_ADDRESS, | |
CUSTOMER_CITY, | |
CUSTOMER_STATE, | |
CUSTOMER_PHONE, | |
CUSTOMER_EMAIL, | |
CUSTOMER_TAX_ID, | |
CUSTOMER_DRIVERS_LICENSE_NUMBER, | |
CUSTOMER_LOGIN_ID, | |
CUSTOMER_SECURITY_QUESTION, | |
CUSTOMER_SECURITY_QUESTION_ANSWER) | |
VALUES(default, 'Becky Silver', 'King''s Cross Station Platform 9', | |
'London', 'UK', '+44-1475-898-073', 'bsilver@hogwarts.edu', | |
'GB999 9999 73', | |
'ABCDE123456AB1AB', 'Bs1lver', 'Who has the best football team?', | |
'Manchester United'); | |
INSERT INTO BRANCH_OFFICE_099.CUSTOMERS | |
(CUSTOMER_ID, | |
CUSTOMER_NAME, | |
CUSTOMER_ADDRESS, | |
CUSTOMER_CITY, | |
CUSTOMER_STATE, | |
CUSTOMER_PHONE, | |
CUSTOMER_EMAIL, | |
CUSTOMER_TAX_ID, | |
CUSTOMER_DRIVERS_LICENSE_NUMBER, | |
CUSTOMER_LOGIN_ID, | |
CUSTOMER_SECURITY_QUESTION, | |
CUSTOMER_SECURITY_QUESTION_ANSWER) | |
VALUES(default, 'Sammie Gold', '20 Deans Yd', | |
'London', 'UK', '+44-20-7222-5152', 'seegold@westminster.org', | |
'GB888 8888 11', | |
'GEEE0101011CDDFE', 'sg0lden', 'Who has the best football team?', | |
'Manchester City'); | |
INSERT INTO BRANCH_OFFICE_099.CUSTOMERS | |
(CUSTOMER_ID, | |
CUSTOMER_NAME, | |
CUSTOMER_ADDRESS, | |
CUSTOMER_CITY, | |
CUSTOMER_STATE, | |
CUSTOMER_PHONE, | |
CUSTOMER_EMAIL, | |
CUSTOMER_TAX_ID, | |
CUSTOMER_DRIVERS_LICENSE_NUMBER, | |
CUSTOMER_LOGIN_ID, | |
CUSTOMER_SECURITY_QUESTION, | |
CUSTOMER_SECURITY_QUESTION_ANSWER) | |
VALUES(default, 'Roger Moore', '85 Albert Embankment', | |
'London', 'UK', '+44-99-0077-0077', 'Jbond@greatmovies.com', | |
'GB888 8888 11', | |
'GFFF0070077TTRED', 'james', 'Who has the best football team?', | |
'Arsenal'); | |
INSERT INTO BRANCH_OFFICE_099.CUSTOMERS | |
(CUSTOMER_ID, | |
CUSTOMER_NAME, | |
CUSTOMER_ADDRESS, | |
CUSTOMER_CITY, | |
CUSTOMER_STATE, | |
CUSTOMER_PHONE, | |
CUSTOMER_EMAIL, | |
CUSTOMER_TAX_ID, | |
CUSTOMER_DRIVERS_LICENSE_NUMBER, | |
CUSTOMER_LOGIN_ID, | |
CUSTOMER_SECURITY_QUESTION, | |
CUSTOMER_SECURITY_QUESTION_ANSWER) | |
VALUES(default, 'John Cleese', '162-168 Regent St.', | |
'London', 'UK', '+44-99-0077-0077', 'Knightswhosayni@python.org', | |
'GB4444 4444 22', | |
'GEEE911911911BLU', 'SirJohn', 'Who has the best football team?', | |
'Shrubbery'); | |
update BRANCH_OFFICE_099.CUSTOMERS set customer_city = upper(customer_city); | |
-------------------------------------------------------------------------------------------------- | |
-- | |
-- End - Load some sample data into CUSTOMERS | |
-- | |
-------------------------------------------------------------------------------------------------- | |
stop; | |
---------------------------------------------------------------------------------------------------- | |
-- | |
-- Load some sample data into ACCOUNTS | |
-- | |
---------------------------------------------------------------------------------------------------- | |
INSERT INTO BRANCH_OFFICE_099.ACCOUNTS | |
(ACCOUNT_ID, | |
CUSTOMER_ID, | |
ACCOUNT_NUMBER, | |
ACCOUNT_NAME, | |
ACCOUNT_DATE_OPENED, | |
ACCOUNT_DATE_CLOSED, | |
ACCOUNT_CURRENT_BALANCE, | |
INSERT_TIMESTAMP, | |
UPDATE_TIMESTAMP) | |
VALUES(default, 1, 'CHK100112P', 'Becky''s checking account', | |
current date - 7 days, | |
NULL, | |
1034.44, default, default); | |
INSERT INTO BRANCH_OFFICE_099.ACCOUNTS | |
(ACCOUNT_ID, | |
CUSTOMER_ID, | |
ACCOUNT_NUMBER, | |
ACCOUNT_NAME, | |
ACCOUNT_DATE_OPENED, | |
ACCOUNT_DATE_CLOSED, | |
ACCOUNT_CURRENT_BALANCE, | |
INSERT_TIMESTAMP, | |
UPDATE_TIMESTAMP) | |
VALUES(default, 2, 'CHK100332P', 'Sammie''s checking account', | |
current date - 7 days, | |
NULL, | |
5055.55, default, default); | |
INSERT INTO BRANCH_OFFICE_099.ACCOUNTS | |
(ACCOUNT_ID, | |
CUSTOMER_ID, | |
ACCOUNT_NUMBER, | |
ACCOUNT_NAME, | |
ACCOUNT_DATE_OPENED, | |
ACCOUNT_DATE_CLOSED, | |
ACCOUNT_CURRENT_BALANCE, | |
INSERT_TIMESTAMP, | |
UPDATE_TIMESTAMP) | |
VALUES(default, 3, 'CHK100443O', 'Roger''s checking account', | |
current date - 7 days, | |
NULL, | |
43.01, default, default); | |
INSERT INTO BRANCH_OFFICE_099.ACCOUNTS | |
(ACCOUNT_ID, | |
CUSTOMER_ID, | |
ACCOUNT_NUMBER, | |
ACCOUNT_NAME, | |
ACCOUNT_DATE_OPENED, | |
ACCOUNT_DATE_CLOSED, | |
ACCOUNT_CURRENT_BALANCE, | |
INSERT_TIMESTAMP, | |
UPDATE_TIMESTAMP) | |
VALUES(default, 4, 'CHK100554G', 'John''s gold checking account', | |
current date - 7 days, | |
NULL, | |
69054.13, default, default); | |
---------------------------------------------------------------------------------------------------- | |
-- | |
-- End --- Load some sample data into ACCOUNTS | |
-- | |
---------------------------------------------------------------------------------------------------- | |
---------------------------------------------------------------------------------------------------- | |
-- | |
-- End --- Load some sample data into TRANSACTIONS | |
-- | |
---------------------------------------------------------------------------------------------------- | |
INSERT INTO BRANCH_OFFICE_099.TRANSACTIONS | |
(TRANSACTION_ID, | |
ACCOUNT_ID, | |
TRANSACTION_TYPE, | |
TRANSACTION_DATE, | |
TRANSACTION_TIME, | |
TRANSACTION_AMOUNT, | |
INSERT_TIMESTAMP, | |
UPDATE_TIMESTAMP) | |
VALUES(default, 1, 'D', current date, current time, 10.00, default, default); | |
INSERT INTO BRANCH_OFFICE_099.TRANSACTIONS | |
(TRANSACTION_ID, | |
ACCOUNT_ID, | |
TRANSACTION_TYPE, | |
TRANSACTION_DATE, | |
TRANSACTION_TIME, | |
TRANSACTION_AMOUNT, | |
INSERT_TIMESTAMP, | |
UPDATE_TIMESTAMP) | |
VALUES(default, 2, 'W', current date, current time, 100.00, default, default); | |
INSERT INTO BRANCH_OFFICE_099.TRANSACTIONS | |
(TRANSACTION_ID, | |
ACCOUNT_ID, | |
TRANSACTION_TYPE, | |
TRANSACTION_DATE, | |
TRANSACTION_TIME, | |
TRANSACTION_AMOUNT, | |
INSERT_TIMESTAMP, | |
UPDATE_TIMESTAMP) | |
VALUES(default, 3, 'W', current date, current time, 5.00, default, default); | |
INSERT INTO BRANCH_OFFICE_099.TRANSACTIONS | |
(TRANSACTION_ID, | |
ACCOUNT_ID, | |
TRANSACTION_TYPE, | |
TRANSACTION_DATE, | |
TRANSACTION_TIME, | |
TRANSACTION_AMOUNT, | |
INSERT_TIMESTAMP, | |
UPDATE_TIMESTAMP) | |
VALUES(default, 4, 'W', current date, current time, 1000.00, default, default); | |
INSERT INTO BRANCH_OFFICE_099.TRANSACTIONS | |
(TRANSACTION_ID, | |
ACCOUNT_ID, | |
TRANSACTION_TYPE, | |
TRANSACTION_DATE, | |
TRANSACTION_TIME, | |
TRANSACTION_AMOUNT, | |
INSERT_TIMESTAMP, | |
UPDATE_TIMESTAMP) | |
VALUES(default, 4, 'D', current date, current time, 5055.25, default, default); | |
---------------------------------------------------------------------------------------------------- | |
-- | |
-- End --- Load some sample data into TRANSACTIONS | |
-- | |
---------------------------------------------------------------------------------------------------- | |
select * | |
from qsys2.data_queue_info | |
where data_queue_library = 'BANKONOSS' | |
and data_queue_name = 'HANDOFF_DQ'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi Scott, is this something we can modify to write the changes to an IBM i MQ rather than a data queue? this is using trigger programs rather than relying on updates to the IBM i journal, what are the advantages or disadvantages of using trigger programs instead of IBM i journal entries? How about database commits and rollbacks?
thanks..
Joel