DB2 Version 9.7 for Linux, UNIX, and Windows

Resizing DMS containers

Containers in a database managed (DMS) table space can be resized as storage needs change. If you use the auto-resize capabilities for DMS containers, the database manager handles this for you. If you did not enable the auto-resize option, you can also make adjustments manually.

About this task

To increase the size of one or more containers in a DMS table space by a specified amount, use the EXTEND option of the ALTER TABLESPACE command; To reduce the size of existing containers, use the REDUCE option. When you use EXTEND or REDUCE, you specify the amount by which you want to the size to increase or decrease from whatever it is currently. In other words, the size is adjusted relative to the current size.

You can also use the RESIZE option on the ALTER TABLESPACE statement. When you use RESIZE, the specify a new size for the affected containers. In other words, the size is interpreted as an absolute size for the specified containers. When using the RESIZE option, all of the containers listed as part of the statement must either be increased in size, or decreased in size. You cannot increase some containers and decrease other containers in the same statement.

The addition or modification of DMS containers (both file and raw device containers) is performed in parallel through prefetchers. To achieve an increase in parallelism of these create or resize container operations, you can increase the number of prefetchers running in the system. The only process which is not done in parallel is the logging of these actions and, in the case of creating containers, the tagging of the containers.

Note: To maximize the parallelism of the CREATE TABLESPACE or ALTER TABLESPACE statements (with respect to adding new containers to an existing table space) ensure the number of prefetchers is greater than or equal to the number of containers being added. The number of prefetchers is controlled by the num_ioservers database configuration parameter. The database has to be stopped for the new parameter value to take effect. In other words, all applications and users must disconnect from the database for the change to take affect.

Restrictions

Each raw device can only be used as one container.
The raw device size is fixed after its creation.
When you are considering to use the RESIZE or EXTEND options to increase a raw device container, you should check the raw device size first to ensure that you do not attempt to increase the device container size larger than the raw device size.
In DMS table spaces, a container must be at least two times the extent size pages in length. The maximum size of a container is operating system dependent.

Example

Example 1: Increasing the size of file containers. The following example illustrates how to increase file containers (each already existing with 1 000 pages) in a table space on a Windows-based system:

   ALTER TABLESPACE PERSNEL
      EXTEND (FILE 'e:\wrkhist1'  200
              FILE  'f:\wrkhist2'  200)

Following this action, the two files have increased from 1 000 pages in size to 1 200 pages. The contents of the table space might be rebalanced across the containers. Access to the table space is not restricted during the re-balancing.

Example 2: Increasing the size of device containers. The following example illustrates how to increase two device containers (each already existing with 1 000 pages) in a table space on a Linux and UNIX system:

    ALTER TABLESPACE HISTORY
      RESIZE (DEVICE '/dev/rhd7'  2000,
              DEVICE  '/dev/rhd8' 2000)

Following this action, the two devices have increased from 1 000 pages in size to 2 000 pages. The contents of the table space might be rebalanced across the containers. Access to the table space is not restricted during the rebalancing.

Example 3: Reducing container size using the REDUCE option. The following example illustrates how to reduce a file container (which already exists with 1 000 pages) in a table space on a Windows-based system:

    ALTER TABLESPACE PAYROLL
      REDUCE (FILE 'd:\hldr\finance'  200)

Following this action, the file is decreased from 1 000 pages in size to 800 pages.