Managing Table Spaces (DB2 UDB for UNIX and Windows)

You manage table spaces in your DB2 UDB for UNIX and Windows database to avoid full table spaces, which is when a table space runs out of free space in the allocated container or containers. This happens when an object requires a new extent but there is insufficient free space in the table space.

Prerequisites

Space is allocated in the containers of a table space in multiples of the page size, called "extents." Extents are contiguous regions of data within a container. The size of the extents is defined when the table space is created. You cannot change the extent size for a table space once it has been defined.

The extent size for a table space also denotes the number of pages that are written to a container before skipping to the next container. The database manager cycles repeatedly through the containers of a table space as data is stored. This ensures proper "striping" of data pages across several containers. Striping is advantageous if each container resides on a different physical disk since the disks can be accessed in parallel. DB2 UDB for UNIX and Windows always allocates new pages in a table space in units of one extent.

Procedure

  1. Regularly monitor free space and rapidly growing objects in the database, using the CCMS.

If free space in a table space decreases continuously, extend the table space in time to accommodate further growth.

Also monitor the database for rapidly growing objects, that is, objects that allocate more and more space, using the CCMS

  1. Monitor disk space.

You also need to monitor available disk space using CCMS or at the operating system level. Plan for additional disks in time to accommodate table space growth.

  1. If necessary, make more space available in a table space using one of the following approaches:
    • Resizing a container to extend the table space while the database is online to avoid downtime

The fastest way to extend table spaces is to resize all containers, which belong to the table space. To ensure proper "striping" of extents across all containers, resize all containers to the same size. If no "rebalancing" (see next point below) is necessary the additional space is available immediately.

You can resize table spaces using the Computing Center Management System (CCMS) or DB2CC.

    • Add a container to extend the table space while the database is online to avoid downtime

Adding a container to a table space triggers a rebalance in the DB2 UDB for UNIX and Windows database. This task moves extents from the existing containers to the new container to ensure proper striping of extents across all containers. It is important to define the new container with the same size as the existing containers of the table space to guarantee equal distribution.

Avoid putting multiple containers of the same table space on the same physical disk for performance reasons. The additional space is available after rebalancing has finished, but this can take a long time. It is not possible to stop rebalancing. Therefore, we only recommend it when space is not needed immediately.

You can add containers using the Computing Center Management System (CCMS) or DB2CC.

    • Reorganize the table space or single objects

It might be possible to free up space in a table space by reorganizing the whole table space or single objects in that table space. Depending on the version of DB2 UDB for UNIX and Windows version, it is not possible to access objects that are currently being reorganized. Therefore, applications might have to wait a long time.

You can reorganize objects using the Computing Center Management System (CCMS) or DB2CC.

Result

By managing the table spaces in your DB2 UDB for UNIX and Windows database, you can avoid unplanned downtime due to database objects filling up.