How to Ignore Mass Deletes Based on Username in PostgreSQL, DB2 for LUW, and MySQL
There are use cases where customers want to instruct HVR to not capture certain changes from their source database, like changes generated by a purge process that removes obsolete data from the database.
In Oracle, this can be easily achieved by using a dedicated user for the purge process in the source database and defining the same username in the /Capture action's /IgnoreSessionName field.
In PostgreSQL and DB2 LUW it is more complicated to skip changes based on the user/session name because this information is not stored in the database's own transaction log. In PostgreSQL and DB2 LUW we can leverage HVR's own loop detection mechanism (which prevents the "boomerang effect" in bidirectional replications) by acting as HVR integrate process during the purge activity.
Steps for PostgreSQL:
Create an HVR_STIN table in the source PostgreSQL:
CREATE TABLE HVR_STIN_<channel> (SESSION_NAME VARCHAR(128) NOT NULL );
Insert a row into HVR_STIN:
insert into HVR_STIN_<channel> values ('hvr_integrate');
Create an HVR_STIS table in the source PostgreSQL:
CREATE TABLE HVR_STIS_<channel> (SESSION_NAME VARCHAR(128) NOT NULL ,DEL_TIME TIMESTAMP(0));
Create a function that inserts a new row into HVR_STIS:
CREATE OR REPLACE FUNCTION hvr_add_row_stis() RETURNS trigger AS $$ BEGIN IF user='<user>' THEN INSERT INTO HVR_STIS_<channel> VALUES ('hvr_integrate',now()); RETURN NEW; ELSE RETURN NEW; END IF; END $$ LANGUAGE 'plpgsql';
Create a trigger that invokes the function on deletes:
CREATE TRIGGER HVR_IGNORE_TEST BEFORE DELETE ON <table> EXECUTE PROCEDURE hvr_add_row_stis(); END;
Replace the <channel>, <table>, <username> placeholders with the actual channel, table and user name
Make sure the STIS table gets truncated from time to time, so it doesn't grow infinite
The user who performs the mass delete must have INSERT privilege on the HVR_STIS table
No additional setup is needed in HVR (leave /IgnoreSessionName empty in the Capture action)
Once hvr_stis_<channel> is created, before deleting from the application table, run hvrinitialize with table enrollment to let HVR know of the hvr_sti*_<channel> name, otherwise, the Capture will still capture unwanted changes;
Following is an example setup where changes done by user postgres
when making updates, deletes, or inserts in the application table will not be captured:
CREATE TABLE public.HVR_STIN_tremp_659 (SESSION_NAME VARCHAR(128) NOT NULL ); INSERT INTO public.HVR_STIN_tremp_659 values ('hvr_integrate'); CREATE TABLE public.HVR_STIS_tremp_659 (SESSION_NAME VARCHAR(128) NOT NULL ,CHANGE_TIME TIMESTAMP(0)); CREATE OR REPLACE FUNCTION hvr_add_row_stis() RETURNS trigger AS $$ BEGIN IF user='postgres' THEN INSERT INTO public.HVR_STIS_tremp_659 VALUES ('hvr_integrate',now()); RETURN NEW; ELSE RETURN NEW; END IF; END $$ LANGUAGE 'plpgsql'; CREATE TRIGGER HVR_IGNORE_TEST BEFORE delete or insert or update ON dr_core24.dm01_order EXECUTE PROCEDURE hvr_add_row_stis(); END;
Or without function/trigger:
CREATE TABLE public.HVR_STIN_tremp_659 (SESSION_NAME VARCHAR(128) NOT NULL ); INSERT INTO public.HVR_STIN_tremp_659 values ('hvr_integrate'); CREATE TABLE public.HVR_STIS_tremp_659 (SESSION_NAME VARCHAR(128) NOT NULL, CHANGE_TIME TIMESTAMP(0));
Run hvrinit with table enrollment.
Then update application table like this;
begin; insert into public.HVR_STIS_tremp_659 values ('hvr_integrate', now()); insert into dr_core24.dm01_order values (4,'test insert 4',current_timestamp); delete from dr_core24.dm01_order; end;
Steps for DB2 LUW:
Create an HVR_STIN table in the source system:
CREATE TABLE HVR_STIN_<channel> (""SESSION_NAME"" VARCHAR(128 OCTETS) NOT NULL );
Insert a line into HVR_STIN:
INSERT INTO HVR_STIN_<channel> (SESSION_NAME) VALUES('hvr_integrate');
Create an HVR_STIS table in the source system:
CREATE TABLE HVR_STIS_ ( TABNAME VARCHAR(32 OCTETS) NOT NULL , SESSION_NAME VARCHAR(128 OCTETS) NOT NULL , DEL_TIME TIMESTAMP(0) ) ;
Enable logging on the HVR_STIS table:
ALTER TABLE HVR_STIS_<channel> DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS;
Create a trigger that inserts a line into HVR_STIS on deletes:
CREATE OR REPLACE TRIGGER HVR_IGNORE_TEST AFTER DELETE ON <table_name> FOR EACH STATEMENT MODE DB2SQL BEGIN IF ( EXISTS ( select * FROM sysibm.sysdummy1 WHERE '<username>' IN ( SELECT user FROM sysibm.sysdummy1))) THEN INSERT INTO HVR_STIS_ VALUES ('','hvr_integrate',CURRENT TIMESTAMP); END IF; END@
Replace the <channel>, <table>, <username> placeholders with the actual channel, table and user name
Make sure the STIS table gets truncated time by time so it doesn't grow infinite
The user who performs the mass delete must have INSERT privilege on the HVR_STIS table
No additional setup is needed in HVR (leave /IgnoreSessionName empty in the /Capture action)
Steps for MySQL:
- Create an HVR_STIN table in the source system:
CREATE TABLE hvr_stin_<channel> (SESSION_NAME VARCHAR(128) NOT NULL);
- Insert a line into HVR_STIN:
INSERT INTO hvr_stin_<channel> VALUES ('hvr_integrate');
- Create an HVR_STIS table in the source system:
CREATE TABLE hvr_stis_<channel> (session_name VARCHAR(128) NOT NULL, del_time TIMESTAMP(0));
- Create a trigger that inserts a line into HVR_STIS on deletes:
delimiter # CREATE TRIGGER hvr_ignore_test BEFORE DELETE ON FOR EACH ROW BEGIN IF (substr(user(),1,locate('@',user())-1)='') THEN INSERT INTO hvr_stis_ VALUES ('hvr_integrate',CURRENT_TIMESTAMP()); END IF; END#
- Replace the <channel>, <table>, <username> placeholders with the actual channel, table and user name
- Make sure the STIS table gets truncated time by time so it doesn't grow infinite
- The user who performs the mass delete must have INSERT privilege on the HVR_STIS table
- No additional setup is needed in HVR (leave /IgnoreSessionName empty in the /Capture action)