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:

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:

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

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

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 PSAPLOAD carefully.

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

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.

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.

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

This shows the history of dbspace growth.

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

This shows tables that are in danger of filling.

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 R/3 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