Abstract:
A procedure optimizes command execution in a database system. According to one embodiment, the database system stores data records on data pages, and provides an identifier for each data page. The identifier indicates when any of the data records contained in the data page were last modified. A data record is selected from a data page and copied to a second storage area. Verification that the selected data record has not been modified since the time that is was copied to the second storage area is based upon the identifier, and then the command is executed.

Description:
FIELD OF THE INVENTION 
   The present invention is directed to an improvement in computing systems and in particular to optimizing command execution in computer database systems that provide support for updatable scrollable cursors. 
   BACKGROUND OF THE INVENTION 
   The Open Database Connectivity (ODBC) specification supports updatable scrollable cursors for Relational Database Management Systems (RDBMSs). This standard provides that multiple cursors may be defined for tables in relational databases and that positioned UPDATEs and DELETEs may be performed on the tables based on the scrollable cursor&#39;s location. The ODBC standard also provides for an attribute in the database to define an optimistic concurrency scheme. In the ODBC standard this attribute is referred to as SQL_CONCUR_VALUES. Use of the SQL_CONCUR_VALUES attribute provides that a positioned UPDATE or DELETE succeeds only if the record data to be modified has not been changed since it was last fetched by the user. 
   In certain relational database systems such as the DB2 UDB (trade-mark) RDBMS, for each scrollable cursor a temporary copy of record data is made when the data is fetched by the user. Where the SQL_CONCUR_VALUES attribute is applied, and a positioned UPDATE or DELETE is to be carried out, the temporary copy of record data must be compared to the current record data in the database to ensure that the record data has not changed since the time that it was copied to the temporary location. A comparison of the record in the temporary copy with the record in the current table may result in significant overhead cost for the UPDATE or DELETE where the records to compare are extensive. 
   It is therefore desirable to have a relational database system that will support the ODBC updatable scrollable cursors and the SQL_CONCUR_VALUES attribute in which it is possible to optimize the steps to carry out the positioned UPDATE or DELETE commands. 
   SUMMARY OF THE INVENTION 
   A system, method and computer readable medium containing programming instructions for optimizing command execution in a database system is disclosed. According to one embodiment of the present invention, the database system stores data records on data pages, and provides a log sequence number for each data page. The log sequence number indicates when any of the data records contained in the data page were last modified. A data record is selected from a data page and copied to a second storage area. The present invention verifies that the selected data record has not been modified since the time that is was copied to the second storage area based upon the log sequence number, and then executes the command. 
   Advantages of the present invention include a reduction in processing time of database UPDATE or DELETE operations based on scrollable cursors where there is support for optimistic concurrency. 

   
     BRIEF DESCRIPTION OF THE DRAWINGS 
       FIG. 1  is a block diagram representing example tables in a database subject to the optimization of the preferred embodiment. 
       FIG. 2  is a flowchart illustrating a method for optimizing command execution in a database system according to a preferred embodiment of the present invention. 
       FIG. 3  is a flowchart illustrating a process for verifying the accuracy of the selected row value according to a preferred embodiment of the present invention. 
   

   In the drawings, the preferred embodiment of the invention is illustrated by way of example. It is to be expressly understood that the description and drawings are only for the purpose of illustration and as an aid to understanding, and are not intended as a definition of the limits of the invention. 
   DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENT 
     FIG. 1  illustrates, in a block diagram, data which is subject to the optimization of the preferred embodiment.  FIG. 2  is a flowchart illustrating a method for optimizing command execution of such data according to a preferred embodiment.  FIG. 1  shows a portion of a relational database containing rows m and n, shown as records  12 ,  14  in the figure.  FIG. 1  also shows a temp table  16  in which data corresponding to rows m and n are shown as records  18 ,  20 . 
   In the preferred embodiment, records in the relational database are stored on pages. The process of  FIG. 2  begins when each page has an identifier, such as a log sequence number (LSN), associated with the page (step  200 ). In  FIG. 1 , data page  10  is shown with associated LSN  22 . The LSN for the data page includes information which effectively provides a time stamp of the last modification made to any table data (records) on that page. With reference to the example of  FIG. 1 , LSN  22  is updated when either record  12  or record  14 , or any other record stored on data page  10 , is modified in any way. 
   As is indicated in  FIG. 1  and  FIG. 2 , according to the preferred embodiment, a copy of the data page LSN is maintained in association with a record when that record is written to temp table  16 . Temp table  16  is used to copy row values when an updatable scrollable cursor is used to retrieve table record values for a user. Referring to  FIG. 1  and  FIG. 2 , a cursor has been used to select rows m and n (step  202 ) and therefore the row m value in record  12  in data page  10  is copied to record  18  in temp table  16  (step  204 ). Similarly the row n value in record  14  is copied to temp table  16  record  20  when a cursor in table is used to fetch row n for a user. 
   In the preferred embodiment, when record  18  having the value of row m is stored in temp table  16 , a copy of LSN  22  is made and stored in the temp table  16  in association with record  18  (step  206 ). This is shown in  FIG. 1  as LSN  24 . Similarly, a copy of LSN  22  is made in association with record  20  when the value of row n is copied to temp table  16 . This associated LSN value is shown as LSN  26  in  FIG. 1 . 
   Due to the concurrency available in the ODBC standard, it is possible for row m to be copied to temp table  16  from data page  10  with the then current value of a LSN  22  being copied to LSN  24  and to then have a subsequent modification to data page  10  before the cursor reaches row n (in the table record  14 ). As a result, row n values may be copied into record  20  in temp table  16  with LSN value  26  that differs from LSN  24 . This is due to a change to the value of LSN  22  when the data page  10  values are modified prior to copying the value of row n into temp table  16 . 
   In the preferred embodiment, where SQL_CONCUR_VALUES attribute is associated with the table containing data page  10 , and an UPDATE or DELETE operation is specified for, for example, row m or row n, based on the position of a cursor, it is necessary to ensure that the value of rows m and n as stored in temp table  16  are the same as the values as stored in data page  10 . Although this may be carried out by a direct comparison of the attribute values in the respective rows in temp table  16  and data page  10 , the preferred embodiment verifies that the selected row values have not been modified based on the identifier associated with the page (step  210 ). 
     FIG. 3  is a flowchart illustrating a process for verifying the accuracy of the selected row value according to a preferred embodiment of the present invention. The process begins by determining the current value of the LSN  22  of the data page  10  from which the selected value  18  was copied (step  300 ). The current LSN  22  is then compared to the LSN value  24  associated with the selected value  18  (step  302 ). If the values match (step  304 ), it is necessarily the case that record  12  has not been modified since a copy of the value of row m was copied into record  18  in temp table  16 . Thus, the selected record  18  is verified (step  306 ). If record  12  had been modified, the value of LSN  22  would have changed and there would therefore not be a match between the value of LSN  22  and the value of LSN  24 , and the selected record  18  would not be verified (step  308 ). In this manner, it is possible to avoid comparing all attribute values of record  12  and of record  18  by carrying out the simple comparison of the value of LSN  24  and the value of LSN  22 . Referring again to  FIG. 2 , where these values are different, i.e., are not verified (step  210 ), it will be necessary to carry out the direct comparison of record  12  and record  18  (step  214 ). However, this step may be avoided where the values of LSN  24  and LSN  22  are found to match (step  210 ). Where this is the case, the user will be able to directly execute a command (step  212 ). e.g. update the value of record  12 , and rely on the record  18  value as corresponding to record  12 . 
   In the implementation of the preferred embodiment, the retrieval and comparison of LSN values is carried out using an internal command referred to as FETCH SENSITIVE NO DATA. The operations carried out by the FETCH SENSITIVE NO DATA internal command result in the LSN for the data page and the LSN for the record in the temp table being retrieved and compared, as is described above. In this way, the RDBMS of the preferred embodiment is able to execute the FETCH SENSITIVE NO DATA command as a part of the steps taken by the RDBMS in optimizing execution of UPDATE or DELETE commands in response to a user request. 
   The preferred embodiment is described above with respect to the UPDATE and DELETE commands where a RDBMS supports optimistic concurrency for a scrollable cursor. The optimization of the preferred embodiment may also be used in implementing other commands in an RDBMS which include a temporary table copy of a record, and require a confirmation that the temp table copy is equivalent to the database copy. 
   An example of such an implementation of the optimization of the preferred embodiment is with respect to the FETCH SENSITIVE command in the DB2 UDB™ RDBMS. The FETCH SENSITIVE command is available to users (in contrast to the FETCH SENSITIVE NO DATA command referred to above which is used internally in the RDBMS, only). Execution of the command, without any optimization, results in the fetch of a record from the database table and the qualification of that record (its attribute values are compared with the SQL predicates associated with the command). Where the record qualifies, the temp table is updated and the record is returned to the user. 
   The optimization of the preferred embodiment makes it possible to use the value of the LSN stored in the temp table to avoid steps in carrying out the command. Where the row m, for example, has been previously fetched and is in temp table  16 , and the value of LSN  24  is equivalent to data page  10  LSN  22 , a FETCH SENSITIVE command carried out on row m may be implemented by positioning the cursor at the appropriate record and returning a flag to the user to indicate that the previously fetched values remain current. If the two LSN values are not equal, then the non-optimized steps to carryout the FETCH SENSITIVE command are followed. 
   Using the comparison of the LSN value associated with the temp table record and the LSN value of the data page, the copying of attribute values to the temp table may be avoided. In this manner, the optimization of the preferred embodiment may be used to increase efficiency in carrying out command execution in a database with updatable scrollable cursors. 
   Although a preferred embodiment of the present invention has been described here in detail, it will be appreciated by those skilled in the art, that variations may be made thereto, without departing from the spirit of the invention or the scope of the appended claims.