Copying data changes to a target database

Patent metadata

Field	Value
Publication number	US-11429641-B2
Application number	US-201916588147-A
Country	US
Kind code	B2
Filing date	Sep 30, 2019
Priority date	Nov 11, 2016
Publication date	Aug 30, 2022
Grant date	Aug 30, 2022

Abstract

A method, system and computer program product for copying data from a source database to a target database. A first database engine of the source database assigns a data-change-ID to each changed row in the source table. The data rows of the source table are stored in a plurality of source pages. Each source page comprises a source-page-ID and a highest-data-change-ID indicating its most recently changed data row. The target table comprises a source-page-ID-column. A second database engine of the target database maintains a metadata table comprising a source-page-ID column and a highest-data-change-ID column. The highest-data-change-IDs in the metadata table are compared with the highest-data-change-IDs of the source pages for selectively copying source table rows of source pages whose highest-data-change-ID is higher than their highest-data-change-ID in the metadata table to the target table.

First claim

The invention claimed is:

1. A computer program product for copying data from a source database maintained by a first database engine to a target database maintained by a second database engine, the computer program product comprising a computer readable storage medium having program code embodied therewith, the program code comprising the programming instructions for:
executing, by the first database engine, one or more transactions, thereby changing rows of a source table of the source database;
assigning, by the first database engine, a data-change-ID to each changed row in the source table, the data-change-ID being indicative of the time of changing said row;
maintaining, by the first database engine, a plurality of source pages, wherein each source page having a source-page-ID and comprising an up-to-date highest-data-change-ID, the highest-data-change-ID being the data-change-ID having been assigned most recently to one of the data rows comprised in the source page;
creating, by the second database engine, a target table, the target table comprising a source-page-ID-column for storing, for each data row copied from the source table into the target table, the source-page-ID of the source page comprising said data row;
maintaining, by the second database engine, a metadata table, the metadata table comprising a column for the source-page-IDs and a further column for the highest-data-change-IDs of the source pages identified by said source-page-IDs, the highest-data-change-IDs in the metadata table indicating the highest-data-change-IDs of the source pages at the time of previously copying rows of said source page to the target database; and
copying data from the source table to the target table.

2. The computer program product as recited in claim 1 , wherein the program code further comprises the programming instructions for:
in an initial copying procedure, copying all data contained in the source table to the target table; and
incrementally replicating changes in the source table to the target table by repeatedly performing the copying of the data contained in all source pages comprising at least one row that has been changed since they were copied to the target table in a previous copying operation.

3. The computer program product as recited in claim 1 , the copying of the data comprising locking each of the source pages individually while the data of said source page is copied to the target database, the data of a locked source page being inaccessible for the transactions.

4. The computer program product as recited claim 1 , wherein the program code further comprises the programming instructions for:
in response to having identified the source pages comprising at least one row that has been changed since they were copied to the target table in a previous copying operation, deleting, by the second database engine, all rows in the target table which comprise a source-page-ID of one of said identified source pages.

5. The computer program product as recited in claim 1 , wherein the first database engine is an OLTP engine.

6. The computer program product as recited in claim 1 , wherein the first database engine maintains a log, the log being indicative of when and in which table and table row a data change occurred in the source database, the log being free of the SQL statement that introduced the change.

7. The computer program product as recited in claim 1 , the copying comprising:
comparing the highest-data-change-IDs in the metadata table with the highest-data-change-IDs of the source pages for identifying all source pages whose highest-data-change-ID is higher than their highest-data-change-ID in the metadata table, wherein the comparison of the highest-data-change-IDs in the metadata table with the highest-data-change-IDs of the source pages comprises:
reading the highest-data-change-IDs to be compared directly from the source pages, not from a log maintained by the first database engine.

8. The computer program product as recited in claim 1 , wherein each of the rows copied from the source table to the target table comprises the row-ID of said row, the row-ID of each row of the source table being a combination of the source-page-ID of the source page comprising said row and an offset of said row within the source page.

9. The computer program product as recited in claim 1 , wherein the data-change-IDs are timestamps of a clock or log record sequence numbers.

10. The computer program product as recited in claim 1 , wherein a keeping of the highest-data-change-ID of each source table page up-to-date is a built-in functionality of a DBMS comprising the first database engine.

11. A system, comprising:
a memory for storing a computer program for copying data from a source database maintained by a first database engine to a target database maintained by a second database engine; and
a processor connected to said memory, wherein said processor is configured to execute the program instructions of the computer program comprising:
executing, by the first database engine, one or more transactions, thereby changing rows of a source table of the source database;
assigning, by the first database engine, a data-change-ID to each changed row in the source table, the data-change-ID being indicative of the time of changing said row;
maintaining, by the first database engine, a plurality of source pages, wherein each source page having a source-page-ID and comprising an up-to-date highest-data-change-ID, the highest-data-change-ID being the data-change-ID having been assigned most recently to one of the data rows comprised in the source page;
creating, by the second database engine, a target table, the target table comprising a source-page-ID-column for storing, for each data row copied from the source table into the target table, the source-page-ID of the source page comprising said data row;
maintaining, by the second database engine, a metadata table, the metadata table comprising a column for the source-page-IDs and a further column for the highest-data-change-IDs of the source pages identified by said source-page-IDs, the highest-data-change-IDs in the metadata table indicating the highest-data-change-IDs of the source pages at the time of previously copying rows of said source page to the target database; and
copying data from the source table to the target table.

12. The system as recited in claim 11 , wherein the program instructions of the computer program further comprise:
in an initial copying procedure, copying all data contained in the source table to the target table; and
incrementally replicating changes in the source table to the target table by repeatedly performing the copying of the data contained in all source pages comprising at least one row that has been changed since they were copied to the target table in a previous copying operation.

13. The system as recited in claim 11 , the copying of the data comprising locking each of the source pages individually while the data of said source page is copied to the target database, the data of a locked source page being inaccessible for the transactions.

14. The system as recited claim 11 , wherein the program instructions of the computer program further comprise:
in response to having identified the source pages comprising at least one row that has been changed since they were copied to the target table in a previous copying operation, deleting, by the second database engine, all rows in the target table which comprise a source-page-ID of one of said identified source pages.

15. The system as recited in claim 11 , wherein the first databas

Assignees

• IBM

Inventors

• Brodt Andreas
• Broll Bjoern
• Martin Daniel
• Mueller Jens P

Classifications

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F16/214

  Database migration support · CPC title

• G06F16/2358

  Change logging, detection, and notification (replication G06F16/27) · CPC title

• G06F16/275 Primary

  Synchronous replication · CPC title