Managing Dbspaces (Informix)

Use

You need to manage the dbspaces of your Informix database to avoid "dbspace overflow". This occurs when there is no more freespace in the chunks allocated to the dbspace. Therefore, downtime of the SAP system is caused because it is impossible to allocate new extents to objects in the dbspace, such as tables or indexes. You normally correct the problem by extending the dbspace (that is, adding a chunk). You can do this with SAPDBA while the database is online.

Another aspect to managing dbspaces is to make sure that the distribution of dbspaces across disk drives is optimal. This is best done during the installation phase.

Before dbspace overflow is reached, it is likely that extents cannot be allocated normally, that is, with the normal extent size required by larger objects. Therefore, extension of such objects leads to a large number of small extents as space in the dbspace becomes increasingly fragmented. You can detect and fix the problem at this stage before actual downtime occurs.

The following factors make dbspace overflow more likely:

·        Operations that greatly extend a table

Certain operations (for example, client copy or batch input) might extend a table excessively. Therefore, plan these operations with care.

·        Unused space in a table

An indirect cause of dbspace overflow is if tables in a dbspace contain a lot of unused space (this occurs, for example, after a table has many inserts followed by many deletes). If you really need to reclaim the space, the best solution is to reorganize the affected table(s) with SAPDBA. Refer to Reorganization of Tables, Indexes, and Dbspaces with SAPDBA.

Procedure

...

       1.      Set up dbspaces optimally.

Locate the three critical dbspaces (logdbs, physdbs, and rootdbs) on separate disk drives to reduce contention. Also, mirror the critical dbspaces to increase their availability. This is best done during the installation phase.

       2.      Use the SAP disk space configuration program.

This helps you to estimate table and dbspace growth for each business application. Therefore, you can configure your disks optimally from an early stage.

       3.      Monitor freespace in dbspaces.

When freespace in a dbspace is approaching zero, increase the size of the dbspace in time to accommodate further growth. Refer to Listing Dbspaces with SAPDBA. The Computing Center Management System (CCMS) in the SAP system also displays information on freespace.

       4.      Monitor dbspaces for chunks with inadequate gaps.

To make sure that dbspaces have chunks with adequate gaps, regularly monitor the dbspaces with SAPDBA. Refer to Listing Dbspaces with SAPDBA.

       5.      Use the alert monitor in the CCMS to identify when a dbspace needs to be extended.

Refer to Extending a Dbspace with CCMS (Informix). This lets you extend the dbspace immediately.

       6.      Monitor dbspaces for rapidly growing objects, that is, objects that need more and more extents in a short time.

Refer to Analyzing Tables by Fill Level, Size, and Extents with SAPDBA.

       7.      Monitor disk space at operating system level.

In addition to using the CCMS and SAPDBA, monitor available disk space at the operating system level. Plan for additional disks in time to accommodate dbspace growth.

       8.      Take the following actions if necessary to fix space problems in dbspaces:

...

                            a.      Add a chunk with SAPDBA (this extends the dbspace)

Additional disk space is required, but the database remains fully online. This is preferable to the next action, reorganization.

However, note that this does not resolve the problem of tables with a high number of extents. If you have this problem, you have to reorganize either individual tables or the dbspace and all its tables.

                            b.      Reorganize a dbspace and its tables with SAPDBA

This lets you reorganize the dbspace and all its tables, and allocate more space to the dbspace if necessary.

With Informix databases, you do not need to reorganize regularly. Make sure you have a good reason to reorganize. For more information, see Reorganizing Objects (Informix).

Result

You avoid dbspace overflow and so avoid downtime for your Informix database and the SAP system.

See also:

Dbspaces with SAPDBA