Browse Prior Art Database

Method and Apparatus for Segmentation of Data-Modifying SQL Queries

IP.com Disclosure Number: IPCOM000015200D
Original Publication Date: 2002-May-05
Included in the Prior Art Database: 2003-Jun-20
Document File: 2 page(s) / 43K

Publishing Venue

IBM

Abstract

In any managed DBMS (e.g.: DB2, MSSQL Server, Oracle, etc), all data-modifying transactions are logged into physical files called Transaction Logs, for several purposes, including crash recovery. These files have either logical or physical limits on their size, and given a large enough data set and a data-modifying query, the logging of a single query can easily exceed the limits of the Transaction Log(s). This generally causes the DBMS to either roll back the transaction (best case), or to crash (worst case). This invention provides a logical method and an implementation for segmenting any data-modifying single-key SQL query into N smaller queries that modify data in M records at a time, in order to avoid filling the Transaction Log(s). In this solution, the problem is solved by : Assigning an Interval value to determine the number of records to insert/update/delete in a single statement. Breaking down the query into component pieces (such as the table(s) involved,

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 52% of the total text.

Page 1 of 2

Method and Apparatus for Segmentation of Data-Modifying SQL Queries

In any managed DBMS (e.g.: DB2, MSSQL Server, Oracle, etc), all data-modifying transactions are logged into physical files called Transaction Logs, for several purposes, including crash recovery. These files have either logical or physical limits on their size, and given a large enough data set and a data-modifying query, the logging of a single query can easily exceed the limits of the Transaction Log(s). This generally causes the DBMS to either roll back the transaction (best case), or to crash (worst case). This invention provides a logical method and an implementation for segmenting any data-modifying single-key SQL query into N smaller queries that modify data in M records at a time, in order to avoid filling the Transaction Log(s).

In this solution, the problem is solved by :

Assigning an Interval value to determine the number of records to

insert/update/delete in a single statement. Breaking down the query into component pieces (such as the table(s) involved,

the selection criteria, the data-modifying clause, etc) Using the selection criteria portion of the original query to obtain an ordered list

(by key value) of the records to be modified, along with assigning a unique ascending record number for each row in the result set. The absolute minimum and maximum value for the key field is saved from this recordset. - Note that each iteration/segment of the data-modifying query must exclude one of the endpoints to avoid duplicating 1 record of work on each execution. For the purposes of the existing implementation, we chose to exclude the lower endpoint. Because of this choice, if the key field is defined as numeric, we subtract one from the initial minimum (creating an IterationMinimum) in order to include the first record but ignore the low value for later iterations. If the key field is defined as text, we set it to some value alphabetically lower, such as '0' in the case of the existing implementation. These implementation decisions are somewhat arbitrary choices, since the invention can work equally well by choosing to exclude the high endpoint (in which case, the operators in the SQL...