Statement logging in databases

• US 8,676,749 B2
• Filed: 07/31/2008
• Issued: 03/18/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, Sarapuk, Andrzej, Lora-Gallardo, Elena Isabel
• Primary Examiner(s): Wong, Leslie
• Application Number:
  US12/183,843
• Publication Number:
  US 20100030824A1
• Time in Patent Office: 2,056 Days
• Field of Search: 707/610, 707/611, 707/633, 707/999.202, 707/999.204, 707/615, 707/999.107, 707/999.203
• US Class Current: 707/610
• CPC Class Codes: G06F 11/1471 involving logging of persistent data for recovery
• CPC Class Codes: G06F 11/2094 Redundant storage or storage space G06F11/2056 takes precedence
• CPC Class Codes: G06F 11/2097 maintaining the standby controller/processing unit updated initialisation or re-synchronisation thereof G06F11/1658 and subgroups

Abstract

System, method, computer program product embodiments and combinations and sub-combinations thereof for logging of data manipulation language (DML) statements are described herein. An embodiment includes a database engine, a replication agent and a replication server which replicates data from the database engine to one or more replicate databases. The database engine writes parsed query information into a transaction log so that a process of replication agent may read the parsed query information and forward it to the replication server. The database engine writes all log records associated with a statement in a manner that allows the replication server to distinguish if those log records belong to same statement. In another embodiment, replication agent selectively sends SQL statement information to the replication server based on the replication server architecture.

Claims

1. A method for replicating a data manipulation language (DML) statement, comprising:

receiving a DML statement for replication;

associating one or more components of said DML statement with corresponding parameters of a stored procedure;

selectively logging information associated with said DML statement when a number of rows affected by said DML statement in a log record reaches a configurable threshold; and

transmitting said stored procedure to a replication server for execution, said stored procedure including one or more executable parameters, said parameters associated with respective components of said DML statement, said execution of said stored procedure causing replication of said DML statement.

2. The method of claim 1, further comprising:

logging one or more database operations associated with said DML statement in a database transaction log; and

encapsulating said one or more database operations associated with said DML statement using at least a begin record marker and an end record marker to enable said replication server to distinguish said one or more database operations from other database operations in said database transaction log.

3. The method of claim 2, further comprising:

transmitting said database transaction log to said replication server.

4. The method of claim 2, wherein said one or more database operations or individual statements are selectively applied at a replicate database.

5. The method of claim 1, wherein said logging step comprises:

placing a marker to identify a first log record logged for said DML statement.

6. A system for replicating a data manipulation language (DML) statement, comprising:

a first module configured to receive a DML statement for replication;

a second module configured to associate one or more components of said DML statement with corresponding parameters of a stored procedure;

a third module configured to selectively log information associated with said DML statement when a number of rows affected by said DML statement in a log record reaches a configurable threshold; and

a fourth module configured to transmit said stored procedure to a replication server for execution, said stored procedure including one or more executable parameters, said parameters associated with respective components of said DML statement, said execution of said stored procedure causing replication of said DML statement.

7. The system of claim 6, further comprising:

a fifth module configured to log one or more database operations associated with said DML statement in a database transaction log; and

a sixth module configured to encapsulate said one or more database operations associated with said DML statement using at least a begin record marker and an end record marker to enable said replication server to distinguish said one or more database operations from other database operations in said database transaction log.

8. The system of claim 7, further comprising:

a seventh module configured to transmit said database transaction log to said replication server.

9. The system of claim 7, further comprising:

a seventh module configured to selectively apply said one or more database operations or individual statements at a replicate database.

10. The system of claim 8, further comprising a eighth module configured to place a marker to identify a first log record logged for said DML statement.

11. A computer program product including a non-transitory computer-readable medium having instructions stored thereon that, when executed by a computing device, cause the computing device to perform operations comprising:

receiving a DML statement for replication;

associating one or more components of said DML statement with corresponding parameters of a stored procedure;

selectively logging information associated with said DML statement when a number of rows affected by said DML statement in a log record reaches a configurable threshold; and

transmitting said stored procedure to a replication server for execution, said stored procedure including one or more executable parameters, said parameters associated with respective components of said DML statement, said execution of said stored procedure causing replication of said DML statement.

12. The computer program product of claim 11, the operations further comprising:

logging one or more database operations associated with said DML statement in a database transaction log; and

encapsulating said one or more database operations associated with said DML statement using at least a begin record marker and an end record marker to enable said replication server to distinguish said one or more database operations from other database operations in said database transaction log.

13. The computer program product of claim 12, the operations further comprising:

transmitting said database transaction log to said replication server.

14. The computer program product of claim 12, wherein said one or more database operations or individual statements are selectively applied at a replicate database.

15. The computer program product of claim 11, said logging comprising:

placing a marker to identify a first log record logged for said DML statement.