Replication System with Methodology for Replicating Stored Procedure Calls

• US 20080034014A1
• Filed: 08/03/2006
• Published: 02/07/2008
• Est. Priority Date: 08/03/2006
• Status: Active Grant

• Current Assignee: Sybase Incorporated (SAP SE)
• Original Assignee: Sybase Incorporated (SAP SE)
• Inventors: Reiger, Derek G., Beck, Wanda
• Granted Patent: US 7,953,698 B2
• Time in Patent Office: Days
• US Class Current: 707/204
• 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 replication system with methodology for replicating stored procedure calls is described. In one embodiment, for example, in a database system without support for logging stored procedure invocations, a method of the present invention is described for replicating invocation of a stored procedure from a primary database to a replicate database, the method comprises steps of: inserting table data into the primary database for storing information sufficiently characterizing the procedure'"'"'s invocation to allow its replication, the table data being logged to a log file; reading the log file, for determining changes to apply to the replicate database for replicating the procedure'"'"'s invocation; and reconstructing the procedure'"'"'s invocation at the replicate database based on the determined changes.

Claims

1. In a database system without support for logging stored procedure invocations, a method for replicating invocation of a stored procedure from a primary database to a replicate database, the method comprising:

inserting table data into the primary database for storing information sufficiently characterizing the procedure'"'"'s invocation to allow its replication, the table data being logged to a log file;

reading the log file, for determining changes to apply to the replicate database for replicating the procedure'"'"'s invocation; and

reconstructing the procedure'"'"'s invocation at the replicate database based on said determined changes.

2. The method of claim 1, wherein said inserting table data step includes:

modifying the procedure to include code for inserting table data indicating when the procedure is executing.

3. The method of claim 2, wherein said modifying step includes:

instrumenting the procedure to include code for inserting table data when the procedure begins executing.

4. The method of claim 3, wherein said modifying step includes:

instrumenting the procedure to include code for removing table data when the procedure stops executing.

5. The method of claim 1, wherein said inserting table data step includes:

modifying the procedure to include code for inserting table data indicating procedure parameter values that the procedure is invoked with.

6. The method of claim 1, further comprising:

receiving user input for selectively marking stored procedures for replication.

7. The method of claim 1, wherein said inserting table data step includes:

creating a shadow table storing information about the procedure'"'"'s parameters, said shadow table including a column for each parameter present in the procedure.

8. The method of claim 7, wherein changes to the shadow table are logged to the log file, so that such changes may be read by a log reader employed for replicating.

9. The method of claim 8, wherein the log reader includes program logic for recognizing log information in the log file corresponding to table data inserted into the shadow table as special purpose information supporting stored procedure replication.

10. The method of claim 7, wherein said shadow table includes a column for transactional integrity.

11. The method of claim 1, wherein said inserting table data step includes:

creating a table storing information indicating a transactional context for the procedure'"'"'s invocation.

12. The method of claim 11, wherein said creating step includes storing information indicating any active transactions that are in progress when the procedure is invoked.

13. The method of claim 1, wherein said table data is used to filter out effects of the procedure'"'"'s invocation from replication, until the procedure has completed execution.

14. The method of claim 1, wherein the primary database itself does not include direct support for logging invocation of stored procedures to the log file.

15. The method of claim 1, wherein said table data includes procedure parameter information stored in a shadow table and includes transactional context information stored in a procedure active table.

16. The method of claim 1, wherein the procedure comprises a database function having a return value.

17. A computer-readable medium having processor-executable instructions for performing the method of claim 1.

18. A system for replicating invocation of a stored procedure, the system comprising:

a primary database storing the procedure;

a replicate database for receiving replication of the procedure'"'"'s invocation;

at least one special purpose table created in the primary database for storing information sufficiently characterizing the procedure'"'"'s invocation to allow its replication;

a log file storing log records that record changes to the primary database, including changes to said at least one special purpose table; and

a log reader supporting replication of the procedure'"'"'s invocation to the replicate database based on changes recorded in the log file.

19. The system of claim 18, further comprising:

a module for modifying the procedure to include code for inserting table data into said at least one special purpose table indicating when the procedure is executing.

20. The system of claim 19, wherein the module for modifying instruments the procedure to include code for inserting table data when the procedure begins executing.

21. The system of claim 20, wherein the module for modifying instruments the procedure to include code for removing table data when the procedure stops executing.

22. The system of claim 18, further comprising:

a module for modifying the procedure to include code for inserting table data into said at least one special purpose table indicating procedure parameter values that the procedure is invoked with.

23. The system of claim 18, further comprising:

a module for receiving user input for selectively marking stored procedures for replication.

24. The system of claim 18, wherein said at least one special purpose table comprises a shadow table storing information about the procedure'"'"'s parameters, said shadow table including a column for each parameter present in the procedure.

25. The system of claim 24, wherein changes to the shadow table are logged to the log file, so that such changes may be read by a log reader employed for replicating.

26. The system of claim 25, wherein the log reader includes program logic for recognizing log information in the log file corresponding to table data inserted into the shadow table as special purpose information supporting stored procedure replication.

27. The system of claim 24, wherein said shadow table includes a column for transactional integrity.

28. The system of claim 18, wherein said at least one special purpose table comprises a table storing information indicating a transactional context for the procedure'"'"'s invocation.

29. The system of claim 28, wherein said information indicating transactional context includes information about any active transactions that are in progress when the procedure is invoked.

30. The system of claim 18, wherein said at least one special purpose table is used to filter out effects of the procedure'"'"'s invocation from replication, until the procedure has completed execution.

31. The system of claim 18, wherein the primary database itself does not include direct support for logging invocation of stored procedures to the log file.

32. The system of claim 18, wherein said at least one special purpose table includes procedure parameter information stored in a shadow table and includes transactional context information stored in a procedure active table.

33. The system of claim 18, wherein the procedure comprises a database function having a return value.