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

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,