How to Ignore Mass Deletes Based on Username in PostgreSQL, DB2 for LUW, and MySQL

Question

I want to instruct HVR to not capture certain changes from my source database, like changes generated by a purge process that removes obsolete data from the database.

How can I ignore mass deletes based on a username?

Environment

• HVR 5
• PostgreSQL
• Db2 for LUW
• MySQL

Answer

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

1. Create an HVR_STIN table in the source PostgreSQL:
   CREATE TABLE HVR_STIN_<channel> (SESSION_NAME VARCHAR(128) NOT NULL );

2. Insert a row into HVR_STIN:
   insert into HVR_STIN_<channel> values ('hvr_integrate');

3. Create an HVR_STIS table in the source PostgreSQL:
   CREATE TABLE HVR_STIS_<channel> (SESSION_NAME VARCHAR(128) NOT NULL ,DEL_TIME TIMESTAMP(0));

4. 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';
   

5. 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

1. Create an HVR_STIN table in the source system:
   CREATE TABLE HVR_STIN_<channel> (""SESSION_NAME"" VARCHAR(128 OCTETS) NOT NULL );

2. Insert a line into HVR_STIN:
   INSERT INTO HVR_STIN_<channel> (SESSION_NAME) VALUES('hvr_integrate');

3. 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) ) ;
   

4. Enable logging on the HVR_STIS table:
   ALTER TABLE HVR_STIS_<channel> DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS;

5. 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

1. Create an HVR_STIN table in the source system:
   CREATE TABLE hvr_stin_<channel> (SESSION_NAME VARCHAR(128) NOT NULL);
2. Insert a line into HVR_STIN:
   INSERT INTO hvr_stin_<channel> VALUES ('hvr_integrate');
3. Create an HVR_STIS table in the source system:
   CREATE TABLE hvr_stis_<channel> (session_name VARCHAR(128) NOT NULL, del_time TIMESTAMP(0));
4. 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)