Replication system with methodology for replicating stored procedure calls

• US 7,953,698 B2
• Filed: 08/03/2006
• Issued: 05/31/2011
• 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
• Primary Examiner(s): LU, CHARLES EDWARD
• Application Number:
  US11/309,387
• Publication Number:
  US 20080034014A1
• Time in Patent Office: 1,762 Days
• Field of Search: 707/200, 707/610, 707/667
• US Class Current: 707/610
• 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 employing a log file to record database changes but without support for logging stored procedure invocations, a method for providing support for replicating invocation of a user-created stored procedure from a primary database to a replicate database, the method implemented on at least one computer having a processor and a memory, the method comprising:

receiving from a user Structured Query Language (SQL) statements that define a stored procedure to be invoked at the primary database;

receiving from the user subsequent SQL statements that invoke said stored procedure at the primary database;

inserting into the stored procedure new code that tracks the stored procedure'"'"'s invocation, wherein when the stored procedure is invoked the new code inserted into the stored procedure also executes, and wherein said new code includes code that inserts table data when the procedure begins executing, code that removes table data when the procedure stops executing, and code that inserts table data indicating with which procedure parameter values the stored procedure is invoked;

responsive to execution of said new code inserted into the stored procedure, recording descriptor information characterizing the stored procedure'"'"'s invocation, including information about when the stored procedure begins and exits execution, and information about any parameter values passed to the stored procedure, said descriptor information characterizing the stored procedure'"'"'s invocation without recording information about data values in the primary database that are affected as a result of the stored procedure'"'"'s invocation;

propagating the stored procedure'"'"'s invocation through a log file of the primary database, wherein when the stored procedure'"'"'s invocation is propagated said descriptor information is stored as table data inserted into the primary database;

reading the log file at the replicate database, for interpreting the descriptor information propagated through the log file; and

replicating the stored procedure'"'"'s invocation at the replicate database based on said descriptor information.

2. The method of claim 1, further comprising:

receiving user input for selectively marking stored procedures for replication.

3. The method of claim 1, further comprising:

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

4. The method of claim 3, 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.

5. The method of claim 4, 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.

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

7. The method of claim 1, further comprising:

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

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

9. 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.

10. 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.

11. 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.

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

13. The method of claim 1, further comprising:

downloading a set of processor-executable instructions for performing the method of claim 1.