Show TOC

Space Management with IBM DB2 for iLocate this document in the navigation structure

Use

This section reviews space management (including reorganization) of database objects, in particular tables. If you neglect space management, this can lead to downtime due to normal database growth when the available disk space fills up.

Prerequisites

On IBM i, disk units are grouped into Auxiliary Storage Pools (ASPs). There is always the system ASP, also known as ASP 1. However, you can configure optional user ASPs or independent ASPs. Temporary storage always resides in the system ASP, your database libraries can reside in the system ASP, a user ASP, or an independent ASP. The storage management component of the Licensed Internal Code distributes the data in an optimal way over the disk units of the ASP where the data resides.

If a user ASP fills up, additional data will overflow into the system ASP. If an independent ASP fills up, any operation to add more data to the independent ASP fails. If the system ASP fills up, the logical partition or server stops working. An IPL is required to clean up this situation.

Process

  1. You monitor storage by using the following tools:

    • SAP application

      To display the state on disk in the SAP application , choose Start of the navigation path Computing Center Management System (CCMS) Next navigation step Control/Monitoring Next navigation step Performance Next navigation step Database Next navigation step Tables/Indexes End of the navigation path (transaction DB02).

      • The function Space Statistics provides you with information about table growth over the past days, weeks, and months.

      • The function Deleted Rows provides you with information about wasted space by deleted rows.

        Note

        Usually space that was occupied by deleted rows is reused when adding new rows to the table. However, in some cases - for example after archiving or a client delete operation - you might have much space wasted by deleted rows that will not be reused soon.

    • IBM i commands

      • To display the size of the system ASP and how much of it is in use, enter the command WRKSYSSTS .

        The unprotected storage indicates how much of the storage in the system ASP is used by temporary data.

      • To display the percentage used of other ASPs besides the system ASP, enter the command WRKDSKSTS .

    • IBM iSeries Navigator

      Instead of the IBM i commands, you can also use the IBM iSeries Navigator (included in IBM iSeries Access for Windows).

      To display the percentage used of the configured ASPs, choose Start of the navigation path Configuration and Service Next navigation step Hardware Next navigation step Disk Units Next navigation step Disk Pools End of the navigation path.

      Note

      You need a service tools user and password to use this function.

    • System operator message queue ( QSYSOPR)

      On IBM i, you should monitor the system operator message queue ( QSYSOPR) for the message CPF0907 "Serious storage condition may exist" .

      Note

      When the system ASP reaches a threshold (90 % per default) then this message is sent. To change the threshold, you use the system service tools (SST).

  2. You reorganize tables that waste too much space for deleted rows.

    If you identify tables that waste a lot of space because of deleted rows in step 1, you can reorganize the tables to free up space by entering the command RGZPFM . For more information about reorganization in online or offline mode and automation of the reorganization, see SAP Note 84081 Information published on SAP site.

  3. You clean up your system by deleting unused data.

    To identify non-SAP data that occupies space and can be deleted, you can use the following commands:

    • RTVDSKINF (for data collection)

      The RTVDSKINF command can run for several hours and must be submitted to batch.

    • PRTDSKINF (for evaluation of the collected data)

    Note

    When archiving data from an SAP system, the occupied space in the database tables only gets released after reorganizing the tables as described in step 2.

  4. You add disk units to the ASP that is running out of space.

    After installing disk units to your server, you can add the units to an ASP by using system service tools (SST). Even though the new disk units can be used immediately, the data distribution over old and new disk units in the ASP is unbalanced.

    To balance the data for best performance and to start background tasks that distributes data evenly over all disk units in the ASP, enter the following command

    STRASPBAL TYPE(*CAPACITY) ASP(...) TIMLIT(...)