Browse Prior Art Database

Online Table Move between Tablespaces Disclosure Number: IPCOM000227632D
Publication Date: 2013-May-10
Document File: 3 page(s) / 49K

Publishing Venue

The Prior Art Database


Disclosed is a method applied to data storage systems to enable efficient table and object movement performed in an online fashion. It uses the novel combination of the following techniques: bufferpool pages, background process, duplicate locks, catalog updates such that the new objects reside in the new tablespace, and system drains of the old transactions.

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

Page 01 of 3

Online Table Move between Tablespaces

Storage systems vary in read/write performance and cost. Usually, faster storage systems tend to cost (significantly) more (e.g., Solid State Disks cost roughly 10 times more than Hard Disks). In a large database system, storage becomes an important aspect of the overall system costs. Over time, the system becomes heterogeneous as new hardware is purchased to run along with the existing hardware. The customer's requirement is to make optimal use of the available storage system, such that higher performing storage systems are used for critical path tables (i.e., tables accessed more frequently or whose access performance is important to the business function), while less important historical data can reside on

less expensive, older, and slower storage systems.

If these large systems operate 24 hours a day, seven days a week, then the movement of tables should be done in an online fashion in order to minimize the impact to existing workload. In addition, the storage system would need to be associated with the tablespace, and tables exist within tables.

From a use case perspective, different tablespaces need to be created with different storage systems. Customers need a way to move tables in an efficient online fashion. There are multiple ways to accomplish this, but with the following limitations:

¡⁄ Unload/Load. This is (mainly) the fastest for table movement. Unload requires a table lock to get the more up-to-date version. Load is an offline utility. Alternatively, Import, which is fully online, can be used, but it has been shown to be 10-100 times slower than Load. (Essentially, the data being loaded is not fully available until the movement is complete, thus the data is unavailable -- not online.)

¡⁄ Online Table Move is "almost" online. In practical customer environments, this utility tends to have a large offline period, where it is performing the

  catch-up of the changes on the source table and applying to the target table . ¡⁄ Multi-Temperature tablespaces. This method allows the storage group to

contain a different speed storage system, which can then be applied to

tablespaces. This is fully online, using the rebalance utility. However, this is at the tablespace level, which may still require a data movement strategy to

organize the table in tablespace; hence, still depend on this disclosed invention.

No existing solution is fully online and at the table level or object level.

The disclosed solution enables efficient table and object movement performed in an online fashion. It uses the novel combination of techniques.

The first of the techniques is bufferpool pages , which are linked between the original

source and the new target objects. Running applications access and change pages on the source object, but any change is written to both the source and the target object. This keeps the object synchronized and keeps the system completely online .

The background process technique...