Show TOC

Procedure documentationManaging Tablespaces (DB2 for Linux, UNIX, and Windows) Locate this document in the navigation structure

 

You manage tablespaces in your DB2 for Linux, UNIX, and Windows database to avoid full tablespaces, which is when a tablespace 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 tablespace.

Prerequisites

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

The extent size for a tablespace 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 tablespace 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 for Linux, UNIX, and Windows always allocates new pages in a tablespace in units of one extent.

Procedure

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

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

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

  2. Monitor disk space.

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

  3. If necessary, make more space available in a tablespace using one of the following approaches:

    • Resizing a container to extend the tablespace while the database is online to avoid downtime

      The fastest way to extend tablespaces is to resize all containers, which belong to the tablespace. 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 tablespaces using the DBA Cockpit.

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

      Adding a container to a tablespace triggers a rebalance in the DB2 for Linux, 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. You need to define the new container with the same size as the existing containers of the tablespace to guarantee equal distribution.

      Avoid putting multiple containers of the same tablespace 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 DBA Cockpit.

    • Reorganize the tablespace or single objects

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

      You can reorganize objects using the DBA Cockpit.

Result

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

More Information

Database Administration Using the DBA Cockpit: IBM DB2 for Linux, UNIX, and Windows at:

  https://service.sap.com/instguidesnw   <Your SAP NetWeaver Release>   Operations   Database-Specific Guides