This invention relates to a method for performing reversal of Structured Query Language (SQL) operations within a database transaction.
Relational database structured query language (SQL) ROLLBACK statements act on the transaction or logical unit of work (LUW) level to reverse (or “undo”) all of the SQL statements that constitute the database transaction when existing computer programming methods are used. The ROLLBACK statement in SQL reverses the changes made by the current database transaction and is typically used to cancel the entire transaction, i.e., the transaction can be “rolled back” completely by specifying the ROLLBACK statement. The alternative to “rolling back” a transaction is to utilize the COMMIT command to make the proposed changes part of the relational database. However, use of COMMIT and ROLLBACK statements should be minimized due to the amount of processing time and/or resources they require for completion.
Current solutions can only perform a “rollback” of an entire transaction or a “rollback” of operations up to a specified savepoint in the log, and thus do not address the problem solved by this invention. There is currently no programming method where any single SQL statement in a LUW can be reversed (or “undone”) without the using software program client having to perform a reversal of all the SQL statements executed to that point in the transaction. This invention enables a client to reverse (or “undo”) one or more (but less than all) selected structured query language (SQL) statement(s) within a single logical unit of work (LUW) database transaction. The invention therefore minimizes the number of COMMIT and/or ROLLBACK statements needed to selectively reverse (or “undo”) portion(s) of a database transaction, and thus provides a tool for the software developer to permit creation of robust applications that allow increased flexibility when programming a database application.
An invention is disclosed for performing a reversal of selected Structured Query Language (SQL) operation(s) within a database transaction. Specifically, a computer programming product, method and system is provided for enabling a using software client to reverse (i.e., “undo” or “rollback”) one or more selected SQL statement(s) within a database transaction. This invention provides the client with an SQL statement UNDO (n) which allows a database server to reverse only those specific SQL operation(s) in a transaction that are defined by the parameter (n) “host variable(s)” without having to reverse the entire database transaction.
It is therefore an object of the present invention to perform a reversal of selected Structured Query Language (SQL) operation(s) within a database transaction.
It is another object of the present invention to provide a computer programming product, method and system for enabling a using software client to reverse (i.e., “undo” or “rollback”) one or more selected SQL statement(s) within a logical unit of work (LUW) database transaction.
It is another object of the present invention to provide the client with an SQL UNDO statement which allows a database server to reverse only those specific SQL operation(s) in a database transaction that are defined by the operative “host variable(s)”.
The subject matter which is regarded as the invention is particularly pointed out and distinctly claimed in the concluding portion of the specification. The invention, however, together with further objects and advantages thereof, may best be understood by reference to the following description taken in conjunction with the accompanying drawings.
To illustrate how the invention can be used to solve this problem, consider the following prior art example where a using software application performs the following operations on an SQL relational database included as part of a computer system:
In the event that UPDATE TABLE 50 fails with a negative SQL code, then the using client must UNDO the following SQL statements in order to consistently maintain the logic used to construct and operate the database:
With existing prior art programming techniques, the client must issue a ROLLBACK statement in order to reverse (or “undo”) all fifty database INSERT(s) and UPDATE(s) that were previously performed unless more COMMIT statements are issued, since there is no current programming mechanism that allows only the foregoing three SQL statements to be specifically reversed and re-executed. Now with a preferred implementation of the invention, if UPDATE TABLE 50 fails then instead of performing a ROLLBACK operation of all the previously-executed SQL statements, the following SQL UNDO (n) statements can be executed by the client in order to “undo” the erroneous statements:
and the client can then “redo” only the incorrect operations:
As illustrated in a preferred embodiment of
To implement these features in a preferred embodiment of the invention, the SQL preprocessor (PREP) 12 must (a) correctly parse the SQL UNDO statement requested by the using client program 10; and (b) correctly populate the RDIIN (or other data structure) passed to the resource adapter 14 so that the request is recognized as an UNDO statement. The resource adapter (RA) 14 must (a) receive the SQL UNDO request from the executing client program 10; (b) convert the UNDO statement into a suitable format for processing by the database server 20; and (c) send the UNDO statement to the database server 20 and receive the response from the server indicating the outcome of its execution. The database server 20 must (a) understand the SQL UNDO request sent to it by the resource adapter 14; (b) read the database instruction execution log 25 to locate the operation in the current logical unit of work (LUW) that is specified by the “host variable” number “n” identified in each UNDO (n) statement; (c) perform a reversal of the identified transaction; and (d) send a response indicating the outcome of execution of the UNDO statement back to the client 10 (optionally) via the resource adapter 14. The version of Structured Query Language (SQL) used in programming the invention must preferably include a standardized definition for the UNDO (n) statement and SQLCODE(s) must be allocated to signify the following error conditions: (a) issuance of the UNDO statement before any other database update is executed in the current LUW; (b) an indication that the operation performed by the UNDO statement was unsuccessful.
The following program chart illustrates a sample database instruction execution log header 25. Upon receipt of an UNDO (n) request, the database server 20 decodes (or “reads”) the “previous record” (PREVREC) field and traverses the log 25 backwards until the value for PREVREC is set to “null” (signifying the first record in the current LUW). Since the length of each database record can be calculated in its number of bytes, the server 20 then passes over (or skips”) (n) records in the log until it reaches the subject nth SQL statement to be reversed, counting only modifications to data (i.e., INSERT/UPDATE/DELETE operations) as eligible for being “skipped”. The database server 20 then reads the subject nth log record for the LUW to construct a corresponding converse SQL statement that reinstates the data existing in the database record prior to execution of the nth selected SQL operation being “undone” (in a manner similar to execution of an SQL ROLLBACK statement). After construction of the converse operation, the database server 20 performs this operation on the database and writes only the converse record on the log 25 by using appropriate “Before” and “After” “images” of the data to construct it.
When a using client 10 issues an SQL UNDO (n) statement and the nth record in the database transaction log 25 for the LUW is an INSERT operation, the converse operation (a DELETE statement) is constructed by reversing the “Before” and “After” image of the INSERT operation. The converse DELETE record will then be written to the log after execution of the constructed DELETE operation. Below is an example INSERT log and the corresponding DELETE log written after execution of the UNDO statement.
43(2B)
43(2B)
When a using client 10 issues an SQL UNDO (n) statement and the nth record in the database transaction log 25 for the LUW is a DELETE operation, the converse operation (an INSERT statement) is constructed by reversing the “Before” and “After” database image of the DELETE operation. The converse INSERT record will then be written to the log after execution of the constructed INSERT operation. Below is an example DELETE log and the corresponding INSERT log written after execution of the UNDO statement:
43(2B)
43(2B)
When a using client 10 issues an SQL UNDO (n) statement and the nth record in the database transaction log 25 for the LUW is an UPDATE operation where the log contains a full “Before” database image and a partial “After” database image for the UPDATE operation, the converse operation will be another UPDATE statement. For the converse UPDATE operation, the complete “Before” image is constructed using the partial “After” image, while the partial “After” image is built using a portion (i.e., the modified part) of the “Before” image. A new UPDATE record is then written to the log after execution of the constructed UPDATE statement. Below is an example UPDATE log and the corresponding converse UPDATE log created after execution of the UNDO statement. (The field names of the original record are in small letters and the converse record in capitals.)
In all cases, the LUW identifier for the new log record is the same as the LUW identifier of the operation for which the SQL UNDO statement was issued and no “rollback record” is written into the log after the SQL UNDO operation is performed; instead log records are written for each of the converse operations performed. When a COMMIT or ROLLBACK statement is executed, the UNDO statement is treated as one or more database updates and the semantics followed for INSERT/UPDATE/DELETE operations is preserved. The UNDO statement therefore behaves like an INSERT/UPDATE/DELETE statement executed as part of the database transaction and (unlike ROLLBACK and COMMIT statements) does not signify the end of the transaction. As a result, the UNDO operation ensures that the LUW remains atomic to guarantee the consistency and integrity of the database.
While certain preferred features of the invention have been shown by way of illustration, many modifications and changes can be made that fall within the true spirit of the invention as embodied in the following claims, which are to be interpreted as broadly as the law permits to cover the full scope of the invention, including all equivalents thereto.