Migrating data in tables in a database

• US 9,436,724 B2
• Filed: 10/21/2013
• Issued: 09/06/2016
• Est. Priority Date: 10/21/2013
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP SE
• Inventors: Driesen, Volker, Haferkorn, Andre, Jordt, Nicolai, Specht, Juergen, Meissner, Steffen, Biewald, Lars-Eric
• Primary Examiner(s): Alam, Hosain
• Assistant Examiner(s): Ahmed, Saba
• Application Number:
  US14/059,380
• Publication Number:
  US 20150112923A1
• Time in Patent Office: 1,051 Days
• Field of Search: 707/661
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing

Abstract

Techniques for migrating data in a database include identifying an update to one or more data entries of a source table; determining that a status of a trigger associated with the source table indicates to update a logging table, and in response: updating, in a first transaction, (i) the one or more data entries of the source table, and (ii) one or more data entries of the logging table based on the user input, and copying content of the source table to a target table; and determining that the status indicates to update the target table, and in response: updating, in a second transaction, (i) the one or more data entries of the source table and (ii) one or more data entries of the target table based on the user input, and updating the content of the target table based on the one or more data entries of the logging table.

Claims

1. A computer-implemented method for migrating data in a database, comprising:

identifying a first update to one or more data entries of a source table;

in response to identifying the first update, determining that a status of a trigger associated with the source table indicates to update a logging table, and in response;

updating, in a first transaction and based on the first update, (i) the one or more data entries of the source table, and (ii) one or more data entries of the logging table, and

copying content of the source table to a target table;

identifying a second update to one or more data entries of the source table;

in response to identifying the second update, determining that the status of the trigger associated with the source table indicates to update the target table, and in response;

updating, in a second transaction and based on the second update, (i) the one or more data entries of the source table and (ii) one or more data entries of the target table,

updating the content of the target table based on the one or more data entries of the logging table, and

determining that the source table and the target table are in-sync based on the updates to the target table;

identifying a third update to one or more data entries of the source table; and

in response to identifying the third update and based on determining that the source table and the target table are in-sync, updating, in a third transaction and based on the third update, one or more data entries of the target table,

wherein the first transaction and the second transaction occur in parallel, and copying the content of the source table to the target table includes excluding one or more data entries from the source table to the target table that are being transferred during the second transaction.

2. The computer-implemented method of claim 1, wherein identifying the first update further comprises receiving a user input that comprises the first update, the first update comprising an insertion, deletion, or update to the one or more data entries of the source table.

3. The computer-implemented method of claim 1, wherein copying the content of the source table to the target table further comprises:

segmenting the content of the source table into one or more segments; and

copying each segment of the content from the source table to the target table.

4. The computer-implemented method of claim 3, wherein copying each segment of the content from the source table to the target table further comprises:

locking the segment of content currently being copied from the source table to the target table; and

restricting modification of the content associated with the segment of content of the source table based on the locking.

5. The computer-implemented method of claim 4, wherein locking the respective segment of content currently being copied from the source table to the target table further comprises locking one or more table rows of content associated with the segment of the source table content.

6. The computer-implemented method of claim 1, wherein copying content of the source table to the target table further comprises:

excluding the one or more data entries of the source table that were updated in the first transaction from being copied to the target table.

7. The computer-implemented method of claim 1, wherein the source table is associated with a first structure, the method further comprising:

creating the target table, the target table associated with a second structure differing from the first structure; and

associating the second structure with the content of the source table to be copied to the target table.

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

identifying a first table name associated with the source table;

replacing the first table name associated with the source table with a second table name; and

associating the first table name with the target table.

9. A computer program product encoded on a non-transitory storage medium, the product comprising non-transitory, computer readable instructions for causing one or more processors to perform operations comprising:

identifying a first update to one or more data entries of a source table;

in response to identifying the first update, determining that a status of a trigger associated with the source table indicates to update a logging table, and in response;

updating, in a first transaction and based on the first update, (i) the one or more data entries of the source table, and (ii) one or more data entries of the logging table, and

copying content of the source table to a target table;

identifying a second update to one or more data entries of the source table;

in response to identifying the second update, determining that the status of the trigger associated with the source table indicates to update the target table, and in response;

updating, in a second transaction and based on the second update, (i) the one or more data entries of the source table and (ii) one or more data entries of the target table,

updating the content of the target table based on the one or more data entries of the logging table, and

determining that the source table and the target table are in-sync based on the updates to the target table;

identifying a third update to one or more data entries of the source table; and

in response to identifying the third update and based on determining that the source table and the target table are in-sync, updating, in a third transaction and based on the third update, one or more data entries of the target table,

wherein the first transaction and the second transaction occur in parallel, and copying the content of the source table to the target table includes excluding one or more data entries from the source table to the target table that are being transferred during the second transaction.

10. The computer storage medium of claim 9, wherein copying the content of the source table to the target table further comprises:

segmenting the content of the source table into one or more segments; and

copying each segment of the content from the source table to the target table.

11. The computer storage medium of claim 10, wherein copying each segment of the content from the source table to the target table further comprises:

locking the segment of content currently being copied from the source table to the target table; and

restricting modification of the content associated with the segment of content of the source table based on the locking.

12. The computer storage medium of claim 11, wherein locking the respective segment of content currently being copied from the source table to the target table further comprises locking one or more table rows of content associated with the segment of the source table content.

13. The computer storage medium of claim 9, wherein copying content of the source table to the target table further comprises:

excluding the one or more data entries of the source table that were updated in the first transaction from being copied to the target table.

14. The computer storage medium of claim 9, wherein the source table is associated with a first structure, the method further comprising:

creating the target table, the target table associated with a second structure differing from the first structure; and

associating the second structure with the content of the source table to be copied to the target table.

15. The computer storage medium of claim 9, the operations further comprising:

identifying a first table name associated with the source table;

replacing the first table name associated with the source table with a second table name; and

associating the first table name with the target table.

16. A system of one or more computers configured to perform operations comprising:

identifying a first update to one or more data entries of a source table;

in response to identifying the first update, determining that a status of a trigger associated with the source table indicates to update a logging table, and in response;

updating, in a first transaction and based on the first update, (i) the one or more data entries of the source table, and (ii) one or more data entries of the logging table, and

copying content of the source table to a target table;

identifying a second update to one or more data entries of the source table;

in response to identifying the second update, determining that the status of the trigger associated with the source table indicates to update the target table, and in response;

updating, in a second transaction and based on the second update, (i) the one or more data entries of the source table and (ii) one or more data entries of the target table,

updating the content of the target table based on the one or more data entries of the logging table, and

determining that the source table and the target table are in-sync based on the updates to the target table;

identifying a third update to one or more data entries of the source table; and

in response to identifying the third update and based on determining that the source table and the target table are in-sync, updating, in a third transaction and based on the third update, one or more data entries of the target table,

wherein the first transaction and the second transaction occur in parallel, and copying the content of the source table to the target table includes excluding one or more data entries from the source table to the target table that are being transferred during the second transaction.

17. The system of claim 16, wherein copying the content of the source table to the target table further comprises:

segmenting the content of the source table into one or more segments; and

copying each segment of the content from the source table to the target table.

18. The system of claim 17, wherein copying each segment of the content from the source table to the target table further comprises:

locking the segment of content currently being copied from the source table to the target table; and

restricting modification of the content associated with the segment of content of the source table based on the locking.

19. The system of claim 18, wherein locking the respective segment of content currently being copied from the source table to the target table further comprises locking one or more table rows of content associated with the segment of the source table content.

20. The system of claim 16, the operations further comprising:

identifying a first table name associated with the source table;

replacing the first table name associated with the source table with a second table name; and

associating the first table name with the target table.