CONTROLLING A MULTI-DATABASE SYSTEM

• US 20170322993A1
• Filed: 12/03/2015
• Published: 11/09/2017
• Est. Priority Date: 12/08/2014
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Brodt, Andreas, Lueck, Einar, Martin, Daniel, Perathoner-Tschaffler, Sabine
• Granted Patent: US 10,552,451 B2
• Time in Patent Office: Days
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27
• CPC Class Codes: G06F 16/252 between a Database Management System and a front-end application
• CPC Class Codes: G06F 16/273 Asynchronous replication or reconciliation

Abstract

A computer-implemented method for controlling a system is provided. The system includes a source database and a target database. The system can asynchronously replicate data changes of at least one part of the source database to the target database. This asynchronous replication includes evaluating, by a capture engine, a log of the source database for identifying any un-replicated data changes of the at least one part of the source database, transferring the identified data changes to an apply engine, and applying, by the apply engine, the transferred data changes to the target database.

Claims

1. A computer-implemented method for controlling a system, the system comprising a source database and a target database,

the system being configured to asynchronously replicate data changes of at least one part of the source database to the target database, the asynchronous replication comprising:

evaluating, by a capture engine of the system, a log of the source database for identifying un-replicated data changes of the at least one part of the source database,

transferring the un-replicated data changes identified by the evaluation to an apply engine of the system, and

applying, by the apply engine, the un-replicated data changes of the at least one part of the source database to the target database,

the computer-implemented method comprising;

detecting a trigger event for initiating a loading of the complete data content of the at least one part of the source database to the target database;

in response to the trigger event and regardless of a number of log records that the capture engine has not evaluated, triggering the apply engine to stop the applying of the un-replicated data changes of the at least one part of the source database to the target database;

in response to stopping the applying of the un-replicated data changes of the at least one part of the source database to the target database by the apply engine, triggering an insert of a start load signal into the log of the source database at a current head of log position;

in response to the insertion of the start load signal and regardless of the number of the log records that the capture engine has not evaluated, initializing the loading of the complete data content of the at least one part of the source database to the target database,

wherein during the loading of the complete data content, concurrent write statements are performed and permitted on the at least one part of the source database;

after the loading of the complete data content is complete, triggering an inserting of an end load signal into the log at a current head of log position;

while the loading is performed, triggering the capture engine to store all write statements which are executed, according to the log of the source database , on the at least one part of the source database after the start load signal and before the end load signal in a queue;

after the loading has completed, triggering the apply engine to apply the write statements stored in the queue to the complete data content of the at least one part of the source database having been loaded completely into the target database,

wherein the applying of the write statements comprises identifying and skipping all write statements of which resulting data changes were already transferred to the target database by the loading.

2. The computer-implemented method of claim 1, the computer-implemented method comprising:

in response to the trigger event and before the inserting of the start load signal, invalidating all start load signals and end load signals previously inserted into the log of the source database.

3. The computer-implemented method of claim 1, the computer-implemented method further comprising:

in response to the trigger event, causing the capture engine and the apply ending to agree upon an earliest possible moment in time when the applying of the un-replicated data changes of the at least one part of the source database can be stopped,

wherein the start load signal is inserted into the log of the source database at or after a log record that reflects said agreed-upon earliest possible moment.

4. The computer-implemented method of claim 1, the computer-implemented method comprising:

in response to the trigger event, instructing the capture engine to stop evaluating the log of the source database for identifying unreplicated data changes of the at least one part of the source database; and

delaying the inserting of the start load signal until a confirmation message is received, the confirmation message indicating that the evaluating of the log of the source database for identifying un-replicated data changes of the at least one part of the source database was stopped successfully.

5. The computer-implemented method of claim 1, wherein the applying of the write statements in the queue is performed regardless of the log record and is performed asynchronously from any replication process being concurrently performed for other parts of the source database.

6. The computer-implemented method of claim 1, wherein the loading of the complete data content is performed such that only a database table row of the source database that is currently read by the load process is locked against concurrent write statements, while all other rows of the source databases are unlocked during the loading of the complete data content.

7. The computer-implemented method of claim 1, the computer-implemented method further comprising:

providing a control module configured to control the capture engine, the apply engine, a signal injection module insert the start load signal and the end load signal, and a loader module; and

controlling, by the control module, a current state of the at least one part of the source database, the current state being one of a set of states, the set of states comprising two or more of the following states;

IDLE, ACTIVE, REFRESHING, SPILLING and DRAINING.

8. The computer-implemented method of claim 1, the computer-implemented method further comprising:

receiving, by a control module, a request of an application program for a current state of the at least one part of the source database; and

identifying, by the control module, the current state of the at least one part of the source database;

returning, by the control module, the identified current state to the application program.

9. The computer-implemented method of claim 8, the computer-implemented method further comprising:

comparing, by the application program, the received state with a first requirement, wherein the first requirement indicates whether a referential integrity of the data in the target database is required.

10. The computer-implemented method of claim 1, wherein the at least one part of the source database beingcomprises:

an individual database table of the source database;

or

an individual column of a database table of the source database;

or

a subset of database tables of the source database;

or

a totality of database tables of the source database.

11. The computer-implemented method of claim 1,wherein the trigger event being one efcomprises:

an event of receiving, via an interface of a control module, a request to initialize the loading of the complete data content;

an event of automatically determining, by the control module that the number of the log records in the log of the source database that have not been evaluated by the capture engine exceed a threshold value;

an event of restarting an environment that hosts the target database;

an event of automatically determining, for the first time, that the at least one part has been created in the source database;

or

an event of determining that at least a fraction the log is no longer available for performing the replication, said fraction comprising un-replicated data changes.

12. The computer-implemented method of claim 1, wherein the source database comprises a signal table, wherein the insertion of the start load signal in the log comprises:

performing, by a signal injection module, a first write statement on the signal table;

logging, by a logging module, the first write statement on the signal table as the write statements on other tables of the source database are logged to create a log record being indicative of the first write statement to the signal table; and

using said log record being indicative of the write statement to the signal table as the start load signal.

16. The computer-implemented method of claim 7, wherein the ACTIVE state indicating that the at least one part is currently replicated from the source database to the target database and that the referential integrity of the at least one part having already been replicated to the target database is intact.

17. The computer-implemented method of claim 7, wherein the IDLE state indicating that replication of the at least one part has been stopped, that the data of the at least one part that was already replicated to the target database lacks data changes which have been applied to the source database more recently than the start load signal and that the referential integrity of the at least one part having already been replicated to the target database may be violated.

18. The computer-implemented method of claim 7, wherein the SPILLING state indicating that replication of the at least one part has been stopped, that the data changes applied later than the start load signal and before the end load signal are being queued, that the data of the at least one part may or may not have been loaded to the target database already, and in case it was already loaded, it lacks data changes which have been applied to the source database more recently than the end load signal and that the referential integrity of the at least one part having already been loaded to the target database may be violated.

19. The computer-implemented method of claim 7, wherein the DRAINING state indicates that replication of the at least one part has been stopped, that the loading of the at least one part to the target database has completed, that the write statements stored in the queue are currently executed on the target database, that the data of the at least one part that was already loaded to the target database comprises at least all data changes which have been applied to the source database prior to the start load signal and may comprise the data changes imposed by the queued write statements having been already executed on the target database, and that the referential integrity of the at least one part having already been loaded to the target database may be violated.

13. (canceled)

14. A control system operatively coupled to a system, the system comprising a source database and a target database, the system being configured such that data changes of at least one part of the source database are asynchronously replicated to the target database, wherein the asynchronous replication comprises:

evaluating, by a capture engine of the system, a log of the source database for identifying un-replicated data changes of the at least one part of the source database,

transferring the un-replicated data changes identified by the evaluation to an apply engine of the system, and

applying, by the apply engine, the un-replicated data changes in-of the at least one part of the source database to the target database,

the control system comprising;

a control module, the control module being operatively coupled to a signal injection module and a loader module;

wherein the control module is configured to;

detecting a trigger event for initiating a loading of complete data content of the at least one part of the source database to the target database;

in response to the trigger event and regardless of a number of log records that the capture engine has not evaluated yet, triggering the apply engine to stop the applying of the un-replicated data changes of the at least one part of the source database to the target database;

in response to stopping the applying of the un-replicated data changes of the at least one part of the source database to the target database by the apply engine, triggering an inserting of a start load signal into the log of the source database at a current head of log position;

in response to the insertion of the start load signal and regardless of the number of the log records that the capture engine has not evaluated, initializing the loading of the complete data content of the at least one part of the source database to the target database,

wherein during the loading of the complete data content, performing concurrent write statements are performed and permitted on the at least one part of the source database;

after the loading of the complete data content is complete, triggering an inserting of an end load signal into the log at a current head of log position;

while the loading is performed, triggering the capture engine to store all write statements which are executed, according to the log of the source database, on the at least one part of the source database after the start load signal and before the end load signal in a queue;

after the loading has completed, triggering the apply engine to apply the write statements stored in the queue to the complete data content of the at least one part of the source database having been loaded completely into the target database,

wherein the applying of the write statements comprises identifying and skipping all write statements of which resulting data changes were already transferred to the target database.

15. The control system of claim 14, wherein the loader module comprises:

a generic loader module, the generic loader module using a “

SELECT *”

command for retrieving the complete data content of the at least one part of the source database in a single operation;

or

a source-database-type specific module which interfaces with a database management system operating the source database, the source-database-type specific module being configured for using techniques provided by said database management system for creating a copy of the at least one part, the copy being loaded during the loading to the target database.

20. A computer program product for controlling a system, the computer program product comprising a computer readable storage medium having program instructions embodied therewith, the system comprising a source database and a target database,

the system being configured to asynchronously replicate data changes of at least one part of the source database to the target database, the asynchronous replication comprising:

evaluating, by a capture engine of the system, a log of the source database for identifying un-replicated data changes of the at least one part of the source database, transferring the un-replicated data changes identified by the evaluation to an apply engine of the system, and

applying, by the apply engine, the un-replicated data changes of the at least one part of the source database to the target database, the program instructions executable by a processor to cause the processor to;

detecting a trigger event for initiating a loading of complete data content of the at least one part of the source database to the target database;

in response to the trigger event and regardless of a number of log records that the capture engine has not evaluated, triggering the apply engine to stop the applying of the un-replicated data changes of the at least one part of the source database to the target database;

in response to stopping the applying of the un-replicated data changes of the at least one part of the source database to the target database by the apply engine, triggering an insert of a start load signal into the log of the source database at a current head of log position;

in response to the insertion of the start load signal and regardless of the number of the log records that the capture engine has not evaluated, initializing the loading of the complete data content of the at least one part of the source database to the target database,

wherein during the loading of the complete data content, concurrent write statements are performed and permitted on the at least one part of the source database;

after the loading of the complete data content is complete, triggering an insert of an end load signal into the log at a current head of log position;

while the loading is performed, triggering the capture engine to store all write statements which are executed, according to the log of the source database, on the at least one part of the source database after the start load signal and before the end load signal in a queue;

after the loading has completed, triggering the apply engine to apply the write statements stored in the queue to the complete data content of the at least one part of the source database having been loaded completely into the target database,

wherein the applying of the write statements comprises identifying and skipping all write statements of which resulting data changes were already transferred to the target database.

21. The computer program product of claim 20, wherein the program instructions are further executable by the processor to cause:

in response to the trigger event and before the inserting of the start load signal, invalidating all start load signals and end load signals previously inserted into the log of the source database.