Database Growth Monitoring 

Purpose

It is important to monitor the space situation in your Informix database. Otherwise, dbspaces and tables might fill and cause processing to stop. Problems with database space occur mainly due to:

·        Natural database growth

·        Addition of new hardware

·        Addition of new applications

All of these can cause the distribution of data in the database to become unbalanced, with negative effects on performance. The purpose of monitoring is to locate problems – if possible anticipating them before they occur – and decide when and how to intervene to bring the database back into balance.

Process Flow

You use the following features of SAPDBA and CCMS to monitor the growth of dbspaces and tables in your database:

·        SAPDBA (Informix database administration tool from SAP)

You can view storage information in SAPDBA at the dbspace, table, or index level.

¡        Dbspaces

SAPDBA offers you reports showing space usage by dbspace, chunk, or device. These are as follows:

§         Listing Dbspaces with SAPDBA shows the free space in each dbspace.

§         Listing Chunks with SAPDBA shows similar information at the chunk level.

§         Listing Devices with SAPDBA shows information about the physical devices, including numbers of disk accesses to each chunk of a device.

Be sure to monitor dbspaces PSAPBTAB, PSAPSTAB, PSAPPOOL, PSAPPROT carefully, as they can grow rapidly PSAPCLU.

If you work with client computers based on different hardware platforms, be sure to also monitor PSAPLOADcarefully.

Changes to default values for dbspaces are possible, especially when SAP's EarlyWatch has determined other values to be more effective for your system. For more information on EarlyWatch, see SAP Safeguarding.

¡        Tables

Analyzing Tables for Critical Next Extent Size with SAPDBA shows the tables that could not be properly extended (due to space shortage) if the need arose. Be sure to extend the dbspaces for any tables on this report as soon as possible, assuming that the next extent size specified for the table is reasonable. Refer to Extension of a Dbspace.

Analyzing Tables by Fill Level, Size, and Extents with SAPDBA offers you different ways to view table information – by size, fill level, number of extents or number of extents still available.

Analyzing Table Information with SAPDBA lets you look in detail at a single table to see if it needs reorganizing.

¡        Indexes

Analyzing Indexes by Fill Level, Leaves, and Levels with SAPDBA offers you different ways to view index information – by fill level, number of extents or number of extents still available.

Analyzing Indexes of a Table with SAPDBA shows all indexes of a single table.

Analyzing Index Information with SAPDBA lets you look in detail at a single index to see if it needs reorganizing.

·        Computing Center Management System (CCMS) in the SAP System

Reports are available at three separate levels, database, dbspace, and table level. Refer to Checking State on Disk (Informix). The following options are particularly useful for managing database growth:

¡        Space statistics for dbspaces

This shows the history of dbspace growth.

¡        Free space statistics for dbspaces

This shows the available freespace in each dbspace and which objects in each dbspace are in danger of filling.

¡        Space critical objects for tables

This shows tables that are in danger of filling.

¡        Space statistics for tables

This shows the size (in KB or extents) of individual tables. You can use the historical analysis in this report to see how much a table has been extended over a certain period of time, such as a day, week, or month.

Result

By monitoring database growth closely you can anticipate problems and so take corrective action to avoid downtime for your SAP System.

Deciding when to intervene is sometimes difficult and is often a matter of judgment. Refer to Recognition of Space Problems and Errors for hints on what you need to look for. If you decide to take action, see Reorganization of Tables, Indexes, and Dbspaces with SAPDBA.

In general, when thinking about how to accommodate database growth in relation to the available disk drives, you can use the same guidelines as for setting up your database. Refer to Database Setup for Production.

 

See also:

Informix documentation at http://www.informix.com