System, method, and computer program product for determining SQL replication process

• US 8,751,441 B2
• Filed: 07/31/2008
• Issued: 06/10/2014
• Est. Priority Date: 07/31/2008
• Status: Active Grant

• Current Assignee: Sybase Incorporated (SAP SE)
• Original Assignee: Sybase Incorporated (SAP SE)
• Inventors: Shang, Heping, Lora-Gallardo, Elena, Sarapuk, Andrzej
• Primary Examiner(s): Hwa, Shyue Jiunn
• Application Number:
  US12/183,505
• Publication Number:
  US 20100030730A1
• Time in Patent Office: 2,140 Days
• Field of Search: 707/999.2, 707/609, 707/610, 707/626, 707/628
• US Class Current: 707/609
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor

Abstract

A system, method, and computer program product are provided for replicating a table in a source database to a target database. In accordance with an embodiment of the present invention, a subscription to the table is defined and a query is executed on the source database. The query is then normalized, and it is determined whether the query would cause semantically appropriate changes to the target database. If so, the normalized query is sent to the target database for execution, in accordance with an additional embodiment of the present invention.

Claims

1. A method for replicating a table in a source database to a target database, comprising:

defining a subscription for replicating data from the table in the source database to the target database;

normalizing a query executed on the source database;

determining, based on parameters of the subscription, that the normalized query would cause changes to the target database that are semantically correct by comparison to changes made to the source database by the query executed on the source database;

determining that sending the normalized query to the target database is computationally preferable to propagation of log-based replication change data; and

sending the normalized query to the target database for execution responsive to the determinations that the sending is computationally preferable and that the changes would be semantically correct.

2. The method of claim 1, further comprising:

sending the change data, the change data comprising atomic changes, to the target database for execution based on a determination that the normalized query would not cause changes to the target database that are semantically correct relative to changes made to the source database by the query executed on the source database.

3. The method of claim 1, wherein determining whether the normalized query would cause semantically correct changes to the target database is performed when the subscription is defined.

4. The method of claim 1, wherein sending the normalized query comprises sending the normalized query as part of a transaction log.

5. The method of claim 4, wherein the transaction log comprises one or more transactions, the normalized query associated with one of the one or more transactions.

6. The method of claim 5, wherein the target database processes the transaction log by processing the one or more transactions in commit order.

19. The method of claim 1, further comprising:

sending change data, the change data comprising atomic changes, to the target database for execution based on a determination that usage of the change data would be less computationally expensive than usage of the normalized query by the target database.

7. A system for replicating a table in a source database to a target database, comprising:

a defining module configured to define a subscription for replicating data from the table in the source database to the target database;

a normalizing module configured to normalize a query executed on the source database;

a determining module configured to determine, based on parameters of the subscription, that the normalized query would cause changes to the target database that are semantically correct by comparison to changes made to the source database by the query executed on the source database, and configured to determine that sending the normalized query to the target database is computationally preferable to propagation of log-based replication change data; and

a sending module configured to send the normalized query to the target database for execution responsive to the determinations that the sending is computationally preferable and that the changes would be semantically correct.

8. The system of claim 7, wherein the sending module is further configured to send the change data, the change data comprising atomic changes, to the target database for execution based on a determination that the normalized query would not cause changes to the target database that are semantically correct relative to changes made to the source database by the query executed on the source database.

9. The system of claim 7,

wherein determining whether the normalized query would cause semantically correct changes to the target database is performed when the subscription is defined.

10. The system of claim 7, wherein the sending module is further configured to send the normalized query as part of a transaction log.

11. The system of claim 10, wherein the transaction log comprises one or more transactions, the normalized query associated with one of the one or more transactions.

12. The system of claim 11, wherein the target database processes the transaction log by processing the one or more transactions in commit order.

20. The system of claim 7, wherein the sending module is further configured to send change data, the change data comprising atomic changes, to the target database for execution based on a determination that usage of the change data would be less computationally expensive than usage of the normalized query by the target database.

13. A computer-usable medium having computer program logic recorded thereon for replicating a table in a source database to a target database, execution of which, by a computing device, causes the computing device to perform operations comprising:

defining a subscription for replicating data from the table in the source database to the target database;

normalizing a query executed on the source database;

determining, based on parameters of the subscription, that the normalized query would cause changes to the target database that are semantically correct by comparison to changes made to the source database by the query executed on the source database;

determining that sending the normalized query to the target database is computationally preferable to propagation of log-based replication change data; and

sending the normalized query to the target database for execution responsive to the determinations that the sending is computationally preferable and that the changes would be semantically correct.

14. The computer-usable medium of claim 13, the operations further comprising:

sending the change data, the change data comprising atomic changes, to the target database for execution based on a determination that the normalized query would not cause changes to the target database that are semantically correct relative to changes made to the source database by the query executed on the source database.

15. The computer-usable medium of claim 13, wherein determining whether the normalized query would cause semantically correct changes to the target database is performed when the subscription is defined.

16. The computer-usable medium of claim 13, wherein sending the normalized query comprises sending the normalized query as part of a transaction log.

17. The computer-usable medium of claim 16, wherein the transaction log comprises one or more transactions, the normalized query associated with one of the one or more transactions.

18. The computer-usable medium of claim 17, wherein the target database processes the transaction log by processing the one or more transactions in commit order.

21. The computer-usable medium of claim 13, the operations further comprising:

sending change data, the change data comprising atomic changes, to the target database for execution based on a determination that usage of the change data would be less computationally expensive than usage of the normalized query by the target database.