Asynchronous data replication using an external buffer table
Embodiments of the present invention provide, systems, methods, and computer program products for asynchronously replicating data from source tables of a source computer system to target tables of a target computer system. Embodiments of the present invention implement an external buffer table (EBT) from which changed data statements can be selectively applied to target tables, which can reduce the number of statements applied to the target tables of the target computer system.
1. A computer program product for asynchronously replicating data, comprising:
one or more non-transitory computer readable storage media and program instructions stored on the one or more non-transitory computer readable storage media, the program instructions executed by a processor comprising:
program instructions to access one or more statements in an internal buffer table of a capture agent of a source computer, wherein the capture agent is configured to monitor, process, and transmit data changes on one or more source tables of the source computer to the target computer via a network;
program instructions to, responsive to identifying an insert statement in an accessed entry of the internal buffer table, insert a row image and a key image value into an insert map;
program instructions to, responsive to determining that the accessed entry does not contain the insert statement, identify a delete statement;
program instructions to, responsive to identifying the delete statement, delete preceding entries from the insert map that have a same named target table and key image value of the accessed entry and inserting the key image value into a delete set;
program instructions to, responsive to determining that the accessed entry does not contain the delete statement, determine whether a specified threshold is met;
program instructions to receive from the source computer the one or more statements that describe the data changes on the one or more source tables of the source computer to be replicated on one or more target tables of the target computer;
program instructions to, responsive to determining that the specified threshold is met, apply the one or more statements to an external buffer table of the target computer; and
program instructions to selectively apply the one or more statements from the external buffer table to the one or more target tables to replicate the data changes on the one or more target tables, wherein the applying the one or more statements comprises:
creating, by the target computer, another insert map including entries for insert statements, wherein each entry in the other insert map references the one or more target tables, data values to be inserted into a row of the named target table, and a row identifier for the row which the data values are to be inserted;
program instructions to, responsive to receiving, at the target computer, a query referencing the one or more target tables, apply the one or more statements corresponding to the referenced one or more target tables from the external buffer table to the one or more referenced target tables; and
program instructions to create at the target computer a view of the one or more statements referenced by the query to execute the query.
2. The computer program product of claim 1 , wherein the program instructions to apply statements corresponding to the one or more referenced target tables from the external buffer table to the one or more referenced target tables, comprise:
program instructions to replicate data changes described by the applied statements on the one or more referenced target tables; and
program instructions to, delete from the external buffer table the applied statements.
3. The computer program product of claim 1 , wherein the program instructions to selectively apply the one or more statements from the external buffer table to the one or more target tables to replicate the data changes on the one or more target tables, comprise:
program instructions to, responsive to receiving, at the target system, the query referencing one or more target tables, select statements in the external buffer table that correspond to the referenced one or more target tables and have an insert operation; and
program instructions to select entries in the one or more target tables that correspond to the referenced one or more target tables, except entries having a named target table and row identifier that match a named target table and row identifier of a statement in the external buffer table having a delete operation.
4. The computer program product of claim 1 , wherein the program instructions to selectively apply the one or more statements from the external buffer table to the one or more target tables to replicate the data changes on the one or more target tables, comprise:
program instructions to, responsive to detecting that a size of the external buffer table has exceeded a specified threshold, apply statements from the external buffer table to one or more target tables.
5. A computer system for asynchronously replicating data, comprising:
one or more computer processors;
one or more computer readable storage media;
program instructions stored on the one or more computer readable storage media, the program instructions executed by at least one of the one or more processors comprising:
program instructions to access one or more statements in an internal buffer table of a capture agent of a source computer, wherein the capture agent is configured to monitor, process, and transmit data changes on one or more source tables of the source computer to the target computer via a network;
program instructions to responsive to identifying an insert statement in an accessed entry of the internal buffer table, insert a row image and a key image value into an insert map:
program instructions to, responsive to determining that the accessed entry does not contain the insert statement, identify a delete statement;
program instructions to, responsive to identifying the delete statement, delete preceding entries from the insert map that have a same named target table and key image value of the accessed entry and inserting the key image value into a delete set;
program instructions to, responsive to determining that the accessed entry does not contain the delete statement, determine whether a specified threshold is met;
program instructions to receive from the source computer the one or more statements that describe the data changes on the one or more source tables of the source computer to be replicated on one or more target tables of the target computer;
program instructions to, responsive to determining that the specified threshold is met, apply the one or more statements to an external buffer table of the target computer; and
program instructions to selectively apply the one or more statements from the external buffer table to the one or more target tables to replicate the data changes on the one or more target tables, wherein the applying the one or more statements comprises:
creating, by the target computer, another insert map including entries for insert statements, wherein each entry in the other insert map references the one or more target tables, data values to be inserted into a row of the named target table, and a row identifier for the row which the data values are to be inserted;
program instructions to, responsive to receiving, at the target computer, a query referencing the one or more target tables, apply the one or more statements corresponding to the referenced one or more target tables from the external buffer table to the one or more referenced target tables; and
program instructions to create at the target computer a view of the one or more statements referenced by the query to execute the query.
6. The system of claim 5 , wherein the program instructions to apply statements corresponding to the one or more referenced target tables from the external buffer table to the one or more referenced target tables, comprise:
program instructions to replicate data changes described by the applied statements on the one or more referenced target tables; and
program instructions to delete from the external buffer table the applied statements.
7. The system of claim 5 , wherein the program instructions to selectively apply the one or more statements from the external buffer table to the one or more target tables to replicate the data changes on the one or more target tables, comprise:
program instructions to, responsive to receiving, at the target computer, the query referencing one or more target tables, select statements in the external buffer table that correspond to the referenced one or more target tables and have an insert operation; and
program instructions to select entries in the one or more target tables that correspond to the referenced one or more target tables, except entries having a named target table and row identifier that match a named target table and row identifier of a statement in the external buffer table having a delete operation.
8. The system of claim 5 , wherein the program instructions to selectively apply the one or more statements from the external buffer table to the one or more target tables to replicate the data changes on the one or more target tables, comprise:
program instructions to, responsive to detecting that a size of the external buffer table has exceeded a specified threshold, apply statements from the external buffer table to one or more target tables.