Kbase P19094: Points to note when using proutil tablemove with the database online
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
11/7/2008 |
|
Status: Verified
GOAL:
Points to note when using proutil tablemove with the database online
GOAL:
How proutil tablemove can affect performance
GOAL:
Do ROWIDs change during a tablemove?
FIX:
The PROUTIL TABLEMOVE qualifier can be used to move a table and optionally its associated indexes from one storage area to another while the database remains online.
What has to be taken into account is the space and time required when doing a tablemove, because of the various stages that the tablemove has to go through.
- Moving the records of a table from one area to another invalidates all the ROWIDs and indexes of the table. Therefore, the indexes are rebuilt automatically by the utility whether you move them or not. You can move the indexes to an application data area other than the one to which you are moving the table. If you want to move only the indexes of a table to a separate application data area, use the PROUTIL IDXMOVE utility.
- Moving a table?s indexes with the TABLEMOVE qualifier is more efficient than moving a table separately and then moving the indexes with the IDXMOVE utility. Moving a table separately from its indexes wastes more disk space and causes the indexes to be rebuilt twice, which also takes longer.
- Although PROUTIL TABLEMOVE operates in phases, it moves a table and its indexes in a single transaction. To allow a full recovery to occur when a 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. Therefore, before moving your table, determine if your available disk capacity is sufficient to support a variable BI extent that might grow to more than three times the size of the table and its indexes.
- While you can move tables online, no access to the table or its indexes is recommended during the move. The utility acquires an EXCLUSIVE lock on the table while it is moving. An application that reads the table with an explicit NO-LOCK might be able to read records, but in some cases might get the wrong results, since the table move operation makes many changes to the indexes. Progress Software recommends that you run the utility during a period when the system is relatively idle, or when users are doing work that does not access the table.
- No other administrative operation on any index of the moved table is allowed during the table move.
- There is a 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.
- Area location is not part of the CRC calculation therefore tables and indices locations can be changed without having to recompile the application.
For details on the syntax for tablemove see How to use the tablemove command