Browse Prior Art Database

Read-Only Online Table Move

IP.com Disclosure Number: IPCOM000195116D
Publication Date: 2010-Apr-21
Document File: 2 page(s) / 26K

Publishing Venue

The IP.com Prior Art Database

Abstract

Disclosed is an extension to existing methods to move tables online to a new storage location. The new approach reduces access to the table to read-only access to avoid problems that may occur if the data in the table is modified while the table is moved.

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

Page 1 of 2

Read-Only Online Table Move

Disclosed is an extension to existing methods to move tables online to a new storage location. The new approach reduces access to the table to read-only access to avoid problems that may occur if the data in the table is modified while the table is moved.

A "table move" operation moves data from a source table to a target table. The idea described here suggests a new table move operation that only provides read access to the source table while it is moved. We refer to this new mode of table move as "read-only" online table move. Existing "table move" operations either work offline (the data can not be accessed while moved) or online (the data can be accessed and modified while moved). Compared to existing online table move operations it has lower hardware resource requirements. Compared to offline table move it provides read-access and avoids potential data inconsistencies.

The drawbacks of existing table move operations for IBM DB2 for Linux, Unix and Windows and for other database platforms:

The online table move operation needs to capture all changes on the source table, while the table is moved. This can cause a significant increase of the database workload. In the case of IBM DB2 (for Linux, Unix and Windows), triggers are used to capture changes on the source table into a staging table. Other database platforms provide similar mechanisms for online table move operations with similar drawbacks. For example ORACLE provides on "Online Object Redefinition" which captures changes that occur during the copy process into a materialized view log.

The offline table move operation allows no access to the source table while it is moved and can therefore not be used if access to the data is required.

Typical use cases for table move operations are:

Modifying the storage characteristics of a table (e.g. different page size, extent size or the like)

Compressing tables
Redistributing tables to a different number of partitions
Lowering the HWM of a table space

In summary the new approach comprises the following new ideas:

Only allow read access to the source table to reduce resource consump...