TABLEMOVE from Type I Storage Area fails with error 13517

TABLEMOVE from Type I Storage Area fails with error 13517.

TABLEMOVE crashes with maxarea limit on the TYPE I that data are being moved from.

TABLEMOVE operation is only moving data, not indexes.

The current high water mark for the Storage Area being moved from is within the Storage Area reserved space.

The Extend blocks in error 13517 would take the Type I Storage area over the maximum Area Blocksize.

 

As part of any TABLEMOVE operation, the related indexes (in this and any other Storage Area that indexes for this Table have been defined) have to be rebuilt to reflect the new rowids that this table's records now have in the new Storage Area.

 

The reason that the TABLEMOVE operation is causing the current Storage Area to extent (aka: grow and cannot extend): 

Is because only the table is being moved to a new Storage Area, not the indexes which are also in this Storage Area.

eg: PROUTIL -C TABLEMOVE <tablename> <newStorageAreaname>

 

When building the indexes that are left in the current Storage Area, there's not enough room to extend the area as it is already near the maxarea limit for a Type I Storage Area. By the default 64 database blocks are initially needed to accommodate the rebuilding of indexes. 

 

When needing to re-organise a database where the current Storage Area is near the maxarea limit,

move the indexes at the same time in order that there's no need to rebuild in the current area.
 

eg: PROUTIL -C TABLEMOVE <tablename> <newStorageAreaname> <newStorageAreaname2>

Where:

<newStorageAreaname>: The first storage area name specified is where the table's data will be moved.

<newStorageAreaname2>: The second storage area name specified is where the table's indexes will be moved. It can be the same area as the data or a different storage area (recommended), depending on the re-organisation design.

 

An additional consideration is that when moving indexes with the table as part of a TABLEMOVE, this will move all the related indexes to the specified Storage Area for indexes in the TABLEMOVE command. If indexes then need to be separated into distinct Storage Areas, another IDXMOVE operation will be needed. For example when the database design is such that the Primary Index is housed in the same Storage Area as the table and other indexes in a separate Storage Area.

 

P19094, Considerations when using proutil tablemove with the database online   
P144801, What to do when you hit the record limit for a Type I area?   
When does the 2 billion rowid limit per Storage Area apply?   
 How to re-org a LOB database when objects are in the Schema Area?