Transfer of data from transactional data sources to partitioned databases in restartable environments

• US 8,521,682 B2
• Filed: 01/17/2008
• Issued: 08/27/2013
• Est. Priority Date: 01/17/2008
• Status: Active Grant

• Current Assignee: ServiceNow Incorporated
• Original Assignee: International Business Machines Corporation
• Inventors: Jang, Jing-Song, Mcardle, James Michael, Spicer, Michael John Elvery
• Primary Examiner(s): AL HASHEMI, SANA A
• Application Number:
  US12/016,092
• Publication Number:
  US 20090187917A1
• Time in Patent Office: 2,049 Days
• Field of Search: 707/607, 707/687, 707/790, 707/964, 707/899
• US Class Current: 707/607
• CPC Class Codes: G06F 11/1474 in transactions G06F16/20 takes precedence
• CPC Class Codes: G06F 16/23 Updating
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity

Abstract

Method, system, and computer program product for transferring data from transactional data sources to partitioned databases are provided. One or more messages from a transactional data source are read. Each message includes one or more records to be inserted into a database comprising a plurality of partitions. One of the partitions of the database in which the one or more records of each message are to be inserted is then identified. A transaction is initiated to insert the one or more records of each message into the one partition and to delete the one or more messages from the transactional data source. The transaction is committed only when insertion of the one or more records of each message into the one partition and deletion of the one or more messages from the transactional data source succeed.

Claims

1. A method for transferring data from a transactional data source to a database, the database managed by a database management system (DBMS), the method comprising:

reading a message from the transactional data source, the message comprising one or more records to be inserted into the database comprising a plurality of partitions, wherein each of the plurality of partitions is a logical division of the database that is defined based on one or more partitioning keys in at least one column of a table of the database, and wherein the transactional data source comprises a queue of data packets that is independent and distinct from both the database and the DBMS;

initiating a first transaction, using a single thread, to both (i) insert the one or more records into a partition of the plurality of partitions and (ii) delete the message from the transactional data source, wherein the message comprising the one or more records remains in the transactional data source until the first transaction is committed;

upon determining that both (i) the insertion of the one or more records into the partition and (ii) the deletion of the message from the transactional data source have successfully completed, committing, using the single thread, both (i) the insertion into the partition and (ii) the deletion of the message from the transactional data source; and

upon determining that at least one of (i) the insertion of the one or more records into the partition and (ii) the deletion of the message from the transactional data source has failed, rolling back, using the single thread, both (i) the insertion into the partition and (ii) the deletion of the message from the transactional data source.

3. The method of claim 1, further comprising:

reading a second message from the transactional data source, the second message comprising one or more records to be inserted into the database;

identifying a second of the plurality of partitions of the database in which to insert the one or more records of the second message;

initiating a second transaction to insert the one or more records of the second message into the second partition and to delete the one or more messages in the second message from the transactional data source; and

committing the second transaction only when the insertion of the one or more records of the second message into the second partition and the deletion of the second message from the transactional data source both succeed.

4. The method of claim 1, wherein there is at least one dedicated connection to each partition of the database.

5. The method of claim 1, wherein the transactional data source is a queue storing a plurality of data packets, and wherein the message is one of the plurality of data packets.

6. The method of claim 1, further comprising:

identifying the partition of the plurality of partitions of the database in which to insert the one or more records of the message, comprising;

comparing at least one partitioning key within the one or more records to a partition key lookup table for the database; and

determining that an entry within the partition key lookup table associated with the partition corresponds to the at least one partitioning key within the one or more records.

7. The method of claim 1, wherein the transactional data source is a data store containing a plurality of messages, and wherein the message is one of the plurality of messages.

11. The method of claim 3, further comprising:

rolling back the second transaction when at least one of (i) insertion of the one or more records of the second message into the second partition or (ii) deletion of the second message from the transactional data source fails.

12. The method of claim 3, wherein the first transaction and the second transaction are running in parallel.

2. A computer program product comprising a computer readable medium, the computer readable medium including a computer program for transferring data from a transactional data source to a database, the database managed by a database management system (DBMS), wherein the computer program, when executed on a computer, causes the computer to:

read a message from the transactional data source, the message comprising one or more records to be inserted into the database comprising a plurality of partitions, wherein each of the plurality of partitions is a logical division of the database that is defined based on one or more partitioning keys in at least one column of a table of the database, and wherein the transactional data source comprises a queue of data packets that is independent and distinct from both the database and the DBMS;

initiate a first transaction, using a single thread, to both (i) insert the one or more records into a partition of the plurality of partitions and (ii) delete the message from the transactional data source, wherein the message comprising the one or more records remains in the transactional data source until the first transaction is committed;

upon determining that both (i) the insertion of the one or more records into the partition and (ii) the deletion of the message from the transactional data source have successfully completed, commit, using the single thread, both (i) the insertion into the partition and (ii) the deletion of the message from the transactional data source; and

upon determining that at least one of (i) the insertion of the one or more records into the partition and (ii) the deletion of the message from the transactional data source has failed, roll back, using the single thread, both (i) the insertion into the partition and (ii) the deletion of the message from the transactional data source.

8. The computer program product of claim 2, wherein the computer program further causes the computer to:

read a second message from the transactional data source, the second message comprising one or more records to be inserted into the database;

identify a second of the plurality of partitions of the database in which to insert the one or more records of the second message;

initiate a second transaction to insert the one or more records of the second message into the second partition and to delete the one or more messages in the second message from the transactional data source; and

commit the second transaction only when the insertion of the one or more records of the second message into the second partition and the deletion of the second message from the transactional data source both succeed.

9. The computer program product of claim 2, wherein there is at least one dedicated connection to each partition of the database.

10. The computer program product of claim 2, wherein the transactional data source is a data store containing a plurality of messages, and wherein the message is one of the plurality of messages.

13. The computer program product of claim 8, wherein the computer program further causes the computer to:

roll back the second transaction when at least one of (i) insertion of the one or more records of the second message into the second partition or (ii) deletion of the second message from the transactional data source fails.

14. The computer program product of claim 8, wherein the first transaction and the second transaction are running in parallel.