Abstract:
An invention is disclosed for performing reversal of selected Structured Query Language (SQL) operations within a database transaction by a database server. Specifically, a computer programming product, method and system is provided for enabling a software client to reverse (i.e., “undo” or “rollback”) one or more selected SQL statement(s) within the current transaction without having to reverse the entire database transaction.

Description:
TECHNICAL FIELD 
       [0001]    This invention relates to a method for performing reversal of Structured Query Language (SQL) operations within a database transaction. 
       BACKGROUND 
       [0002]    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. 
         [0003]    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. 
       SUMMARY OF THE INVENTION 
       [0004]    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. 
         [0005]    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. 
         [0006]    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. 
         [0007]    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)”. 
         [0008]    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. 
     
    
     
       BRIEF DESCRIPTION OF THE DETAILED DRAWINGS 
         [0009]      FIG. 1  is a flowchart illustrating the operation of a using program client of the present invention. 
           [0010]      FIG. 2  is a flowchart illustrating the operation of a database server of the present invention. 
       
    
    
     DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS 
       [0011]    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: 
         [0000]    
       
         
               
               
             
           
               
                   
                   
               
             
             
               
                   
                 EXEC SQL CONNECT TO DATABASE 1 
               
               
                   
                 INSERT INTO TABLE 1 
               
               
                   
                 INSERT INTO TABLE 2 
               
               
                   
                 INSERT INTO TABLE 3 
               
               
                   
                 .... 
               
               
                   
                 INSERT INTO TABLE 50 --- &gt; incorrect data entered 
               
               
                   
                 UPDATE TABLE 1 
               
               
                   
                 UPDATE TABLE 2 
               
               
                   
                 UPDATE TABLE 3 
               
               
                   
                 .... 
               
               
                   
                 UPDATE TABLE 25 --- &gt; incorrect data entered 
               
               
                   
                 .... 
               
               
                   
                 UPDATE TABLE 49 --- &gt; incorrect data entered 
               
               
                   
                 .... 
               
               
                   
                 UPDATE TABLE 50 --- &gt; statement fails with negative 
               
               
                   
                 SQLCODE due to incorrect inputs 
               
               
                   
                   
               
             
          
         
       
     
         [0012]    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: 
         [0000]    
       
         
               
               
             
           
               
                   
                   
               
             
             
               
                   
                 #50.  INSERT INTO TABLE 50 
               
               
                   
                 #75.  UPDATE TABLE 25 
               
               
                   
                 #99.  UPDATE TABLE 49 
               
               
                   
                   
               
             
          
         
       
     
         [0013]    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: 
         [0000]                                UNDO (50)       UNDO (75)       UNDO (99)                    
and the client can then “redo” only the incorrect operations:
 
         [0000]    
       
         
               
               
             
           
               
                   
                   
               
             
             
               
                   
                 INSERT INTO TABLE 50 
               
               
                   
                 UPDATE TABLE 25 
               
               
                   
                 UPDATE TABLE 49 
               
               
                   
                   
               
             
          
         
       
     
         [0014]    As illustrated in a preferred embodiment of  FIGS. 1 &amp; 2 , the SQL preprocessor (PREP)  12  examines (i.e., “parses” or “traverses”) the SQL UNDO (n) instruction statement issued by the using client program  10 , and the preprocessor  12  populates an internal data structure sent to the resource adapter program (RA)  14  so that it recognizes the request as an UNDO instruction. The resource adapter  14  receives the request and convert the UNDO statement into a suitable format so that the instruction/data stream sent to the database server  20  will accommodate the operative “host” variable(s) (n) in the SQL UNDO statement to be executed. This conversion can be accomplished with distributed relational data architecture (DRDA) or with the private protocol implemented by the particular database program in use (such as DB2). The database server  20  receives the UNDO instruction and decodes it by processing it in the same manner as any other received instruction/data stream. When the database server  20  recognizes a statement as an SQL UNDO (n) request, it traverses the database instruction execution record log  25  (preferably) “backwards” (i.e., from most to least recently-executed SQL statement) until the first log record for the current LUW is encountered and then locates the selected (n) statement(s) to be reversed. When an SQL UNDO statement is issued before any other database update has occurred in a LUW, the database server will return an SQL code to the using application program indicating that there is no SQL operation to “undo” yet. 
         [0015]    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. 
         [0016]    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. 
         [0000]    
       
         
               
               
               
             
           
               
                   
               
               
                 Offset 
                 Name 
                 Description 
               
               
                   
               
             
             
               
                 0(0) 
                 LOGHEAD 
                 BASED NOTE: RECTYPE 
               
               
                   
                   
                 MUST BE THE 1st FIELD 
               
               
                 0(0) 
                 RECTYPE 
                 TYPE OF LOG RECORD 
               
               
                 1(1) 
                 RECLTH 
                 LENGTH OF DATA PART 
               
               
                   
                   
                 (FOLLOWS HDR) 
               
               
                 4(4) 
                 TRANS 
                 LUW IDENTIFIER 
               
               
                 8(8) 
                 PREVREC 
                 RELATIVE ADDRESS IN 
               
               
                   
                   
                 LOG OF THE PREV LOG 
               
               
                   
                   
                 RECORD OF THIS LUW 
               
               
                 12(C)  
                 TIMESTMP 
                 TOD WHEN RECORD 
               
               
                   
                   
                 WAS STARTED 
               
               
                   
               
             
          
         
       
     
         [0017]    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. 
         [0000]    
       
         
               
               
             
               
               
               
               
               
               
             
           
               
                   
               
               
                   
                 Converse DELETE 
               
               
                 INSERT LOG RECORD 
                 RECORD written after UNDO 
               
             
          
           
               
                 Offset 
                 Name 
                 Description 
                 Offset 
                 Name 
                 Description 
               
               
                   
               
               
                 0(0) 
                 LINSERT 
                 BASED LOGDATA 
                 0(0) 
                 LDELETE 
                 BASED LOGDATA 
               
               
                   
                   
                 FOR INSERT 
                   
                   
                 FOR DELETE 
               
               
                 0(0) 
                 LINSHEAD 
                 HEADER OF 
                 0(0) 
                 LDELHEAD 
                 HEADER OF 
               
               
                   
                   
                 LOGGED DATA 
                   
                   
                 LOGGED DATA 
               
               
                 1(1) 
                 LINSSEG 
                 SEGMENTED ID 
                 1(1) 
                 LDELSEG(=LINSSEG) 
                 SEGMENTED ID 
               
               
                   
                   
                 INSERTED TUPLE 
                   
                   
                 DELETED TUPLE 
               
               
                 3(3) 
                 LINSTID 
                 TID OF INSERTED 
                 3(3) 
                 LDELTIDB(=LINSTID) 
                 BASE TID OF 
               
               
                   
                   
                 TUPLE 
                   
                   
                 DELETED TUPLE 
               
               
                 7(7) 
                 LINSRID 
                 RID INSERTED 
                 7(7) 
                 LDELRID(=LINSRID) 
                 RID DELETED 
               
               
                   
                   
                 TUPLE 
                   
                   
                 TUPLE 
               
               
                 9(9) 
                 LINSLTH 
                 TOTAL LENGTH 
                 9(9) 
                 LDELLTH(=LINSLTH) 
                 TOTAL LENGTH 
               
               
                   
                   
                 OF TUPLE 
                   
                   
                 OF TUPLE 
               
               
                     43(2B) 
                 LINSVAL 
                 FIELD VALUES 
                     43(2B) 
                 LDELVAL(=LINSVAL) 
                 FIELD VALUES 
               
               
                   
                   
                 INSERTED TUPLE 
                   
                   
                 DELETED TUPLE 
               
               
                   
               
             
          
         
       
     
         [0018]    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: 
         [0000]    
       
         
               
               
             
               
               
               
               
               
               
             
           
               
                   
               
               
                   
                 Converse INSERT 
               
               
                 DELETE LOG RECORD 
                 RECORD written after UNDO 
               
             
          
           
               
                 Offset 
                 Name 
                 Description 
                 Offset 
                 Name 
                 Description 
               
               
                   
               
               
                 0(0) 
                 LDELETE 
                 BASED LOGDATA 
                 0(0) 
                 LINSERT 
                 BASED LOGDATA 
               
               
                   
                   
                 FOR DELETE 
                   
                   
                 FOR INSERT 
               
               
                 0(0) 
                 LDELHEAD 
                 HEADER OF 
                 0(0) 
                 LINSHEAD 
                 HEADER OF 
               
               
                   
                   
                 LOGGED DATA 
                   
                   
                 LOGGED DATA 
               
               
                 1(1) 
                 LDELSEG 
                 SEGMENTED ID 
                 1(1) 
                 LINSSEG(=LDELSEG) 
                 SEGMENTED ID 
               
               
                   
                   
                 DELETED TUPLE 
                   
                   
                 INSERTED TUPLE 
               
               
                 3(3) 
                 LDELTIDB 
                 BASE TID OF 
                 3(3) 
                 LINSTID(=LDELTIDB) 
                 TID OF 
               
               
                   
                   
                 DELETED TUPLE 
                   
                   
                 INSERTED TUPLE 
               
               
                 7(7) 
                 LDELRID 
                 RID DELETED 
                 7(7) 
                 LINSRID(=LDELRID) 
                 RID INSERTED 
               
               
                   
                   
                 TUPLE 
                   
                   
                 TUPLE 
               
               
                 9(9) 
                 LDELLTH 
                 TOTAL LENGTH 
                 9(9) 
                 LINSLTH(=LDELLTH) 
                 TOTAL LENGTH 
               
               
                   
                   
                 OF TUPLE 
                   
                   
                 OF TUPLE 
               
               
                     43(2B) 
                 LDELVAL 
                 FIELD VALUES 
                     43(2B) 
                 LINSVAL(=LDELVAL) 
                 FIELD VALUES 
               
               
                   
                   
                 DELETED TUPLE 
                   
                   
                 INSERTED TUPLE 
               
               
                   
               
             
          
         
       
     
         [0019]    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.) 
         [0000]    
       
         
               
               
             
               
               
               
               
               
               
             
           
               
                   
               
               
                   
                 Converse UPDATE 
               
               
                 UPDATE LOG RECORD 
                 RECORD written after UNDO 
               
             
          
           
               
                 Offset 
                 Name 
                 Description 
                 Offset 
                 Name 
                 Description 
               
               
                   
               
               
                 0(0) 
                 lupdate 
                 BASED LOGDATA 
                 0(0) 
                 LUPDATE 
                 BASED LOGDATA 
               
               
                   
                   
                 FOR UPDATE 
                   
                   
                 FOR UPDATE 
               
               
                 0(0) 
                 lupdhead 
                 HEADER OF 
                 0(0) 
                 LUPDHEAD 
                 HEADER OF 
               
               
                   
                   
                 LOGGED DATA 
                   
                   
                 LOGGED DATA 
               
               
                 1(1) 
                 lupdseg 
                 SEGMENTED ID 
                 1(1) 
                 LUPDSEG(=lupdseg) 
                 SEGMENTED ID 
               
               
                   
                   
                 UPDATED TUPLE 
                   
                   
                 UPDATED TUPLE 
               
               
                 3(3) 
                 lupdtid 
                 TID OF UPDATED 
                 3(3) 
                 LUPDTID(=lupdtid) 
                 TID OF UPDATED 
               
               
                   
                   
                 TUPLE 
                   
                   
                 TUPLE 
               
               
                 7(7) 
                 lupdrid 
                 RID OF UPDATED 
                 7(7) 
                 LUPDRID(=lupdrid) 
                 RID OF UPDATED 
               
               
                   
                   
                 TUPLE 
                   
                   
                 TUPLE 
               
               
                 17(11) 
                 lupdlth1 
                 LENGTH OF OLD 
                 17(11) 
                 LUPDLTH1(=lupdlth2) 
                 LENGTH OF 
               
               
                   
                   
                 SUBTUPLE 
                   
                   
                 OLD SUBTUPLE 
               
               
                 19(13) 
                 lupdlth2 
                 LENGTH OF NEW 
                 19(13) 
                 LUPDLTH2(=lupdlth1) 
                 LENGTH 
               
               
                   
                   
                 SUBTUPLE 
                   
                   
                 OF NEW SUBTUPLE 
               
               
                 21(15) 
                 lupdbeg 
                 DISPLAY IN TUPLE 
                 21(15) 
                 LUPDBEG(=lupdbeg) 
                 DISPLAY IN 
               
               
                   
                   
                 OF 1ST UPDATED 
                   
                   
                 TUPLE OF 1 ST   
               
               
                   
                   
                 BYTE 
                   
                   
                 UPDATED BYTE 
               
               
                 24(18) 
                 lupddoms 
                 OLD TUPLE 
                 24(18) 
                 LUPDDOMS 
                 OLD TUPLE 
               
               
                   
                   
                 NEW SUBTUPLE 
                   
                 (calculated using 
                 NEW SUBTUPLE 
               
               
                   
                   
                   
                   
                 lupdlth1/lupdlth2/ 
               
               
                   
                   
                   
                   
                 lupdbeg/lupddoms) 
               
               
                   
               
             
          
         
       
     
         [0020]    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. 
         [0021]    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.