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 Postgres 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 Postgres 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 Postgres:
-
Create an HVR_STIN table in the source Postgres:
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 Postgres:
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 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)
Once hvr_stis_<channel> is created , before deleting from application table, run hvrinitialize with table enrollment to let HVR know of the hvr_sti*_<channel> name, otherwise capture will still capture unwanted changes;
Example setup where user changes done by user postgres , when making updates,deletes, or inserts, in 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:
1.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_<channel> (
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_<channel>
VALUES ('<table_name>','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:
1.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 DELETEON <channel> FOR EACH ROWBEGINIF (substr(user(),1,locate('@',user())-1)='<username>') THENINSERT INTO hvr_stis_<channel> 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)