Modify...to Relocate Option
To move the data without changing the number of locations or storage structure, specify relocate. 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 on "area1" is moved to "area4", the data on "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 without change to the corresponding area in the newlocation list. You can only use the oldlocation and newlocation options in the with clause when you specify relocate.
To change some but not all locations, specify only the locations to be changed. For example, move only the data on "area1" of the employee table:
modify employee to relocate
with oldlocation = (area1),
newlocation = (area4);
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 32 kilobyte) 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: 01/30/2023