The PROUTIL TABLEMOVE qualifier is used to move a table and optionally its associated indexes from one Storage Area to another. This operation can be run while the database remains online. The following outlines points for consideration when using tablemove online:
1. Moving records of a table from one Area to another invalidates all the ROWIDs and indexes of the table.
- The rowid for these records change with the tablemove operation
- For each index, an existing entry that points to the source row has to be deleted and a new one that points to the new row in the target Area has to be inserted.
- Indexes are rebuilt automatically by the utility, whether they are moved at the same time as the table or not and therefore have to be active for the operation.
2. Moving indexes with tablemove or not.
a. While it's unusual to leave the index in the existing Area and only move the table, indexes will remain in their current storage area when the "Index Area Name" is not included in the tablemove command line and will be rebuilt in the current area as the records are moved to the new area.
$ proutil dbname -C tablemove <tablename> <DataAreaName>
b. The PROUTIL IDXMOVE qualifier can be used instead, when only the indexes of a table need to be moved to a separate application data Area. Each index is moved in a separate command line. This is useful when indexes need to be separated into distinct Storage Areas, 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.
$ proutil dbname -C indexmove <tablename>.<index-name> <IndexAreaName>
c. All of a table's indexes can be moved to the same or another application data Area other than the one to which the table is being moved, by specifying the related index area in the tablemove command line. Another IDXMOVE operation will be needed if for example when the database structure designs a specific storage area for all primary indexes.
$ proutil dbname -C tablemove <tablename> <DataAreaName> < IndexAreaName>
Why moving a table’s indexes with the TABLEMOVE qualifier is better?It is more efficient to move the indexes at the same time, than moving a table separately and then moving the indexes with the IDXMOVE utility.
- Moving a table an indexes in separate operations causes the indexes to be rebuilt twice, which wastes more disk space and takes longer.
- When the indexes are moved with the table these records are already in shared memory, which means that they only need to be read in from disk once and are built as the table's records are moved.
- When only the indexes are moved they need to be re-built in the new area. In order to do this the associated table's records first need to be read to accomplish this.
- When only the table is moved, all the indexes need to be rebuilt in their existing location as the rowid for these records has changed. An additional 64 database blocks are initially needed to accommodate the rebuilding of these indexes in their current location.
- All the table's indexes are moved at the same time as the table's records, unlike idxmove which works on one index at a time.
3. Tablemove acquires an EXCLUSIVE lock on the table while it is moving records
- As a consequence, there is the possibility that the utility will have to wait for all the necessary locks to be available before it can start. This might take some time in a high transaction environment.
- An application that reads the table with an explicit NO-LOCK might be able to read records, but in some cases may get the wrong results returned since the table move operation makes changes to the indexes.
- No other Administrative operation on any index of the moved table is allowed during the table move.
- When tables are moved online with tablemove, no access to the table or its indexes is recommended during the move. We recommend that the tablemove operation is run during a period when the system is relatively idle, or when users are doing work that does not access that table.
4. Although PROUTIL TABLEMOVE operates in phases, it moves a table and its indexes in a single transaction
- To allow a full recovery if the transaction is interrupted, every move and delete of each individual record is logged.
- As a result, moving a table requires the BI Recovery Area to be several times larger than the combined size of the table and its indexes.
- Before moving a table, determine if available disk capacity is sufficient to support BI extent space that could grow to more than three times the size of the table and its indexes. For further information refer to Article:
Interaction between PROUTIL -C tablemove, before-image and after-imaging
5. Since indexes need to be active for a tablemove operation, another approach is to effectively reduce the transaction scope of the tablemove by dropping indexes outlined as follows:
Dump the table.df to edit later.
Delete all indexes except the smallest index. For large tables an index on a logical field would result in the least index build activity if the order of the records is not important.
eg: DROP INDEX "Name" ON "Customer"
$ proutil dbname -C tablemove Customer "Cust_DataNew" "Cust_IndexNew"
ADD INDEX "Name" ON "Customer"
AREA "New Index Area"
INACTIVE
INDEX-FIELD "Name" ASCENDING
6. Other TABLEMOVE Considerations
- Running "PROUTIL -C IDXCOMPACT" for the associated index(es) prior to the tablemove to the designed data area is recommended.
- After using tablemove, the high-water-mark of this area remains. While this means there's more space for the remaining tables' record data before the area needs to be extended again, it also means you're going to need more backup media space. If all object have been moved out, area extents can be removed with PROSTRCT REMOVE (offline). These steps are outlined in Article How to restructure the database using TABLEMOVE and PROSTRCT REMOVE
- Area location is not part of the CRC calculation, therefore tables and indices locations can be changed without having to recompile the application.
- When a tablemove strategy is used to re-organise a Type I storage area that is fast approaching it's maxarea limit, move the indexes at the same time as the tables otherwise it may fail if there's not enough room to extend the Schema Area to rebuild the remaining indexes
TABLEMOVE from Type I Storage Area fails with error 13517 - LOB objects associated with the table are not moved as part of the operation, they will remain in their current location. Caution should be exercised before truncating the area as these LOBs will no longer be available but the moved records will still have reference to them.
How to move a LOB to another Storage Area?
7. Space and time requirements during the various stages that tablemove goes through need to be taken into account to determine if this is the right methodology for the operation.
Alternate approaches are traditional dump and load methodologies, however these are not necessarily 100% online. For example, If loading all content of an existing area to a new Storage area(s) in an existing database:
a. Binary dump the data.
b. Add the new Storage Area(s)
c. Truncate the current Storage Area offline (instead of having to wait using online methods to delete this data)
d. Index build (fast, there's no data to build indexes against)
e. Tablemove with indexes (fast, there's no data to build and saves editing .df files)
f. Binary load (which will load to the new storage area) then IDXBUILD.
g. Remove the area with PROSTRCT REMOVE
