5 Space
5.4 Space: Tablespaces
5.4.1 Maintaining Tablespaces
Using the tablespace list on the Tablespace screen, you can maintain tablespaces as follows: Change tablespace and container settings
Add new tablespaces Delete tablespaces
As of DB2 V9.1, you can convert a regular tablespace to tablespace of type LARGE As of DB2 V9.7, you can start and stop the reduction of the high-water mark as well
as rebalance the tablespace.
Changing a Tablespace
1. On the Tablespace screen, select a tablespace and choose the Change pushbutton. The Change Tablespace dialog box appears where you can change the following settings:
Field Description
Technical Settings
Prefetch Size Number of pages to be prefetched
Overhead
I/O controller overhead and disk seek and latency time in milliseconds You can use this value to determine the cost of I/O during query optimization.
Transfer Rate
Time to read one page in milliseconds
You can use this value to determine the cost of I/O during query optimization.
Buffer Pool
Name of the allocated buffer pool Note
You can enter only buffer pools that match the page size of the tablespace.
File System Caching
You can activate or deactivate file system caching.
Note
As long as neither the FILE SYSTEM CACHING nor the NO FILE SYSTEM CACHING clause has been specified, the default caching options of the underlying file system type are used. The first use of any of these options
explicitly sets this value. You cannot fall back to the default behavior.
End of the note.
Dropped Tables Selecting this checkbox enables to you recover dropped tables using the
RECOVER TABLE ON option of the ROLLFORWARD command.
Storage Parameters
AUTORESIZE Enabled
Enable this option if you want to allow DB2 to automatically enlarge tablespace containers using the file system where the containers are located.
Maximum Size
You can enter one of the following values:
o NONE
There is no maximum size limit. In this case, you allow DB2 to extend containers until they occupy all file systems where the containers are located
o An absolute value in KB
This value defines an upper threshold that shall not be exceeded by automatic extensions
Note
This field is valid only if the AUTORESIZE Enabled option is selected.
End of the note.
Increase Size
Size in KB or in percent by which the tablespace is extended if it has become full
You can specify an absolute value in KB or a relative percentage.
Note
This field is valid only if the AUTORESIZE Enabled option is selected. End of the note.
Containers
(Optionally available)
If a tablespace is not managed by automatic storage management, you can add or delete containers as follows:
o To add containers, choose the Add Container pushbutton. The system automatically suggests a default path where the
container is located. However, you can modify that path by manually editing the line.
Caution
Adding or changing containers might result in rebalancing, which has a heavy impact on system performance.
End of the caution.
At least one container must be available for each partition. If you are using a multi partition database, you need to add containers for all partitions of the corresponding partition group. If you have to change container sizes, we recommend that you enter an appropriate value in the Resize all containers to field to ensure a balanced distribution of data on the different containers.
Caution
Different container sizes might result in bad performance of the database.
End of the caution.
o To delete containers, select one or more lines in the table and choose Delete.
2. To confirm your entries, choose the Execute pushbutton.
Adding a Tablespace
1. On the Tablespace screen, choose the Add pushbutton.
2. In the Add Tablespace dialog box, specify a name and a partition group.
Recommendation
We recommend that you use uppercase letters for the tablespace name. Using lowercase letters or special characters makes accessing the selected tablespace with the DB2 command line processor less comfortable.
End of the recommendation.
3. Enter the technical settings. By default, the system displays SAP's recommendations. 4. Add containers.
If you are using a multi partition database, you must add containers for all partitions of the corresponding database partition group.
Caution
This step does not apply to tablespaces managed by automatic storage
management.
End of the caution.
5. To confirm your entries, choose the Execute pushbutton.
Deleting a Tablespace
On the Tablespace screen, select a tablespace and choose the Delete pushbutton. Caution
You cannot delete tablespaces that are still used by the SAP system, that is, if they are related to some data class. You must delete the data class before deleting the tablespace. End of the caution.
Converting a Regular Tablespace to LARGE
By default, DB2 V9.1 uses large object tablespaces. If you upgraded your database from DB2 UDB Version 8 to DB2 V9.1, you can convert your regular tablespaces to large object
tablespaces. To do so, select a tablespace in the overview table of the Tablespace screen and choose the Convert to Large pushbutton.
After having confirmed the conversion, the conversion job is scheduled as a background job in the DBA Planning Calendar.
Reducing the High-Water Mark (HWM) of a Tablespace
After major table reorganizations or deleting and archiving operations, free space in a tablespace is not necessarily released directly to the file system.
To release free space, you can lower the high-water mark as follows:
1. On the Tablespace screen, select a line in the overview table and choose the
Reduce HWM pushbutton.
2. In the Reduce High-Water Mark dialog box, you can do one of the following:
o To have DB2 automatically determine the minimum size that can be reached, choose the Reduce Until Minimum radio button.
o To manually specify the size of the free space of a tablespace to be released, choose the Reduce radio button.
By default, the unit of reduction is KB. You can, however, switch to MB, GB or to %.
3. Choose the Execute pushbutton.
The reduction process starts asynchronously in the background.
To monitor the progress of reduction, you can check the TS State column in the overview table. If the column shows the value High-water mark reduction in progress, select the column to see a detail view of the reduction progress.
Stopping the High-Water Mark Reduction
To stop an ongoing high-water mark reduction, select the appropriate tablespace in the overview table of the Tablespace screen and choose the Stop HWM pushbutton.
Rebalancing a Tablespace
Regular and large tablespaces that are managed by automatic storage management have to be rebalanced if the following applies:
You created new containers on recently added storage paths.
A container and its content has to be distributed to the remaining containers. If a database was converted to automatic storage by adding storage paths, the tablespaces still reside in the old containers and do not yet have containers in the automatic storage database.
For the first two cases, the rebalancing process is automatically started. If a database was converted to automatic storage, however, you have to manually start the rebalancing process by selecting a tablespace in the overview table and choosing the Rebalance pushbutton. After the rebalancing process has been started, data is moved from the containers of the dropped storage paths to the containers on new storage paths. The rebalancing process runs asynchronously in the background and does not affect the availability of data.
To monitor the rebalancing process, check the TS State column in the overview table. If this column shows any of the values Reverse Rebalance, Forward Rebalance or DMS rebalancer
is active, select the TS State column to see a detail view of the rebalancing progress.
For each partition, the following data is displayed:
Column Description
Partition Partition number
Tablespace
Name Name of the tablespace
Mode
The rebalancer mode can be one the following
No Rebalance
The rebalancer process has finished on this partition.
Reverse Rebalance
Data movement starts at the high-water mark extent and moves in reverse order through the tablespace, ending with the first extent in the tablespace.
Forward Rebalance
Data movement starts with the first extent in the tablespace and ends with the high-water mark extent.
Extents Processed
Number of tablespace extents that have already been moved to the new location
Extents
Remaining Number of tablespace extents that still need to be moved Last Extent
Moved Number of the last extent that was moved
Priority Priority with which the rebalancing process is running in the database
Restart Time Time when the rebalancing process was restarted after it had been paused
or stopped
Start Time Time when the rebalancing process was first started
Progress (%) Displays the progress of the reduction process in percent
More Information
Configuration: Data Classes [page 224]