TRANSFER OF DATA FROM TRANSACTIONAL DATA SOURCES TO PARTITIONED DATABASES IN RESTARTABLE ENVIRONMENTS

• US 20090187917A1
• Filed: 01/17/2008
• Published: 07/23/2009
• Est. Priority Date: 01/17/2008
• Status: Active Grant

• Current Assignee: ServiceNow Incorporated
• Original Assignee: International Business Machines Corporation
• Inventors: Spicer, Michael John Elvery, Mcardle, James Michael, JANG, Jing-Song
• Granted Patent: US 8,521,682 B2
• Time in Patent Office: Days
• US Class Current: 719/313
• 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 transactional data sources to partitioned databases, the method comprising:

reading a first set of one or more messages from a transactional data source, each message in the first set comprising one or more records to be inserted into a database comprising a plurality of partitions;

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

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

committing the first transaction only when insertion of the one or more records of each message in the first set into the first partition and deletion of the one or more messages in the first set from the transactional data source succeed.

2. The method of claim 1, further comprising:

rolling back the first transaction when insertion of the one or more records of each message in the first set into the first partition or deletion of the one or more messages in the first set from the transactional data source fails.

3. The method of claim 1, further comprising:

reading a second set of one or more messages from the transactional data source, each message in the second set 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 each message in the second set;

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

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

4. The method of claim 3, further comprising:

rolling back the second transaction when insertion of the one or more records of each message in the second set into the second partition or deletion of the one or more messages in the second set from the transactional data source fails.

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

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

7. The method of claim 1, wherein the transactional data source is a queue.

8. A system for transferring data from transactional data sources to partitioned database, the method comprising:

a feed handler reading a first set of one or more messages from a transactional data source, each message in the first set comprising one or more records to be inserted into a database comprising a plurality of partitions;

a sorter in communication with the feed handler, the sorter identifying a first of the plurality of partitions of the database in which to insert the one or more records of each message in the first set; and

a first flusher in communication with the sorter and the first partition, the first flusher

initiating a first transaction to insert the one or more records of each message in the first set into the first partition and to delete the one or more messages in the first set from the transactional data source, and

committing the first transaction only when insertion of the one or more records of each message in the first set into the first partition and deletion of the one or more messages in the first set from the transactional data source succeed.

9. The system of claim 8, wherein the first flusher further rolls back the first transaction when insertion of the one or more records of each message in the first set into the first partition or deletion of the one or more messages in the first set from the transactional data source fails.

10. The system of claim 8, wherein

the feed handler further reads a second set of one or more messages from the transactional data source, each message in the second set comprising one or more records to be inserted into the database,

the sorter further identifies a second of the plurality of partitions of the database in which to insert the one or more records of each message in the second set, and

the system further comprises a second flusher in communication with the sorter and the second partition, the second flusher

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

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

11. The system of claim 10, wherein the second flusher further rolls back the second transaction when insertion of the one or more records of each message in the second set into the second partition or deletion of the one or more messages in the second set from the transactional data source fails

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

13. The system of claim 10, wherein the second flusher is in communication with the second partition via at least one dedicated connection to the second partition.

14. The system of claim 8, wherein the first flusher is in communication with the first partition via at least one dedicated connection to the first partition.

15. A computer program product comprising a computer readable medium, the computer readable medium including a computer program for transferring data from transactional data sources to partitioned databases, wherein the computer program, when executed on a computer, causes the computer to:

read a first set of one or more messages from a transactional data source, each message in the first set comprising one or more records to be inserted into a database comprising a plurality of partitions;

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

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

commit the first transaction only when insertion of the one or more records of each message in the first set into the first partition and deletion of the one or more messages in the first set from the transactional data source succeed.

16. The computer program product of claim 15, wherein the computer program further causes the computer to:

roll back the first transaction when insertion of the one or more records of each message in the first set into the first partition or deletion of the one or more messages in the first set from the transactional data source fails.

17. The computer program product of claim 15, wherein the computer program further causes the computer to:

read a second set of one or more messages from the transactional data source, each message in the second set 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 each message in the second set;

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

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

18. The computer program product of claim 17, wherein the computer program further causes the computer to:

rolling back the second transaction when insertion of the one or more records of each message in the second set into the second partition or deletion of the one or more messages in the second set from the transactional data source fails.

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

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