Dismiss
InnovationQ will be updated on Sunday, Oct. 22, from 10am ET - noon. You may experience brief service interruptions during that time.
Browse Prior Art Database

Altering Data Partition Limit Key Values With Maximized Availability

IP.com Disclosure Number: IPCOM000234825D
Publication Date: 2014-Feb-10
Document File: 5 page(s) / 55K

Publishing Venue

The IP.com Prior Art Database

Abstract

Disclosed is a method applied to a relational database system that virtually removes the outage window for alter limit key materialization. The method begins with an online alter limit key process that builds on top of the existing alter limit key behavior, and then improves it with a concept of pending definition schema changes. The method is further extended to make alter limit key a truly online schema change with minimal data unavailability.

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

Page 01 of 5

Altering Data Partition Limit Key Values With Maximized Availability

In a relational database system, the method of altering data partition limit key value has been described in prior art as 'Repartitioning Data', with the flow of events roughly as

follows:

1. User has a partitioned by range table space where each partition is defined with a high ending limit key value

2. Due to skewed data distribution or capacity planning, user executes an ALTER TABLE ALTER PARTITION ENDING AT statement to change the limit key values of a few selected partitions

3. The affected data partitions are put into a hard reorg pending status (REORP) immediately after the aforementioned alter execution, which restricts all read and write access to the affected data partitions

4. A data-restructuring agent, such as the REORG or LOAD REPLACE utility, is then run to redistribute or discard the existing data, reloading the data records based on the new limit key values boundary, and remove the restrictive REORP status on successful completion

5. The affected data partitions become fully available again for application and any other agents to access

In the aforementioned alter limit key scenario, there is an extended window of unavailability from the time of the alter limit key execution to the time of the data-restructuring agent completion, be that a LOAD/REORG utility or some other data movements method. Depending on the size of the table space and the amount of data involved, the window of unavailability can range from minutes to hours, or potentially days. With the requirement to support online transactions 24/7 today, this window of unavailability is unacceptable and database administrators can often find it impossible to perform alter limit key, forcing administrators into consideration of other data replication or replacement technique to achieve this schema change.

The method disclosed herein addresses this availability issue by virtually removing the outage window for alter limit key materialization. An embodiment of the disclosed

method is in at least two parts, beginning with an online alter limit key process, which

builds on top of the existing alter limit key behavior, and further adding a concept of pending definition schema changes. The method is further extended to make alter limit key a truly online schema change with minimal data unavailability.

The disclosed method minimizes user impact. There are no changes to the existing alter limit key command syntax used by existing users, thereby avoiding any Structured Query Language (SQL) incompatibility issue or user intervention to modify existing

workloads. The method provides a solid foundation, as it builds on top of the known and working pending definition schema change materialization model, with confidence

in its existing behavior. In addition, the method provides flexibility for future expansion/extension. The pending definition schema changes can be easily extended to include other diffe...