Purge obsoleted change events

Wiki (en)
1

Invantive Data Replicator will automatically switch from Trickle Loading to the Copy approach when there are trickle loading change events for over 2.5% of the rows in the last version of a table partition. This is signaled by a message code itgendci208 in dc_event_log_r (native in the repository on the database) or dc_event_log_r@DataCache (from Invantive SQL).

This requires manual action. First step is to remove obsoleted events. The following script provides guidance for this purpose:

--
-- List of table partitions that have old incoming messages
-- that are superfluous since the last synchronization completed
-- already successfully.
--
-- Run direct (native) on SQL Server, Oracle, PostgreSQL or MySQL.
--
create view xxdru_to_remove_from_incoming_messages_r
as
select tpn.tbe_full_qualified_name
,      tpn.ptn_name
,      tpn.tpn_date_last_got_seeding
from   dc_table_partition_versions_r tpn
--
-- Last active table partition version.
--
where  tpn.tpn_state = 'R'

--
-- Remove all incoming messages that are older than any
-- ready table partition version.
--
-- Run direct (native) on SQL Server, Oracle, PostgreSQL or MySQL.
--
-- Use at your own risk.
--
delete dc_incoming_messages
where  date_received 
        < 
		( select min(tpn_date_last_got_seeding)
		  from   xxdru_to_remove_from_incoming_messages_r
        )

--
-- Remove old superfluous incoming messages 
-- per combination of table and partition.
--
-- Run this Invantive PSQL code on Invantive Query Tool.
--
-- Use at your own risk.
--
begin
  for r
  in 
  ( select * 
    from   xxdru_to_remove_from_incoming_messages_r
    order
    by     ptn_name
    ,      tbe_full_qualified_name
  )
  loop 
    begin
      delete dc_incoming_messages
      where  appl_partition_c          = r.ptn_name
      and    table_full_qualified_name = r.tbe_full_qualified_name
      and    date_received             < r.tpn_date_last_got_seeding
      ;
      exception
        when others
        then
          --
          -- Try again.
          --
          begin
            delete dc_incoming_messages
            where  appl_partition_c          = r.ptn_name
            and    table_full_qualified_name = r.tbe_full_qualified_name
            and    date_received             < r.tpn_date_last_got_seeding
            ;
          exception 
            when others 
            then 
              raise_application_error
              ( -20163
              , 'Failed on ' 
                || r.ptn_name 
                || '/' 
                || r.tbe_full_qualified_name 
                || '/' 
                || r.tpn_date_last_got_seeding 
                || '.'
              );
          end;
      end;
  end loop;
end;

A future release of Invantive Data Replicator may include functionality to automatically remove obsoleted entries when itgendci208 occurs.

The percentage of 2.5% is configurable starting Data Replicator 20.1.358 (BETA) and 20.0.142 using the driver attribute max-percentage-trickle-loading.