MODIFY...TO RELOCATE
To move the data without changing the number of locations or storage structure, specify MODIFY...TO RELOCATE.
Syntax to move a table:
MODIFY tablename|indexname TO RELOCATE
WITH OLDLOCATION = (locationname {, locationname}),
NEWLOCATION = (locationname {, locationname}),
For example, to relocate the employee table to three different areas:
MODIFY employee TO RELOCATE
WITH OLDLOCATION = (area1, area2, area3),
NEWLOCATION = (area4, area5, area6);
The data in area1is moved to area4, the data in area2 is moved to area5, and the data on area3 is moved to area6. The number of areas listed in the oldlocation and newlocation options must be equal. The data in each area listed in the oldlocation list is moved “as is” to the corresponding area in the newlocation list. The oldlocation and newlocation options can only be used when relocate is specified.
To change some but not all locations, specify only the locations to be changed. For example, you can move only the data in area1 of the employee table:
MODIFY employee TO RELOCATE
WITH OLDLOCATION = (area1),
NEWLOCATION = (area4);
Areas 2 and 3 are not changed.
The DBMS Server is very efficient at spreading a table or index across multiple locations. For example, if a table is to be spread over three locations:
CREATE TABLE large (wide VARCHAR(2000),
WITH LOCATION = (area1, area2, area3);
rows are added to each location in turn, in 16-page (approximately 32K for the 2K page size) chunks. If it is not possible to allocate 16 full pages on an area when it is that area's turn to be filled, the table is out of space, even if there is plenty of room in the table's other areas.
Last modified date: 04/26/2024