Start of Content Area

Process documentation Recognition of Space Problems and Errors  Locate the document in its SAP Library structure

Purpose

This section describes the following to help you recognize problems and errors in the space management of your Informix database:

Prerequisites

Familiarize yourself with the tools discussed in Database Growth Monitoring, as these help you to determine how the data in a dbspace is expanding and when to consider extending the dbspace.

Process Flow

  1. You identify tables that need reorganization.

    2. The main reason for reorganizing a table is that it will soon reach its maximum allowable number of extents. To find out if this is true, you can use SAPDBA to check the number of available extents for the table. Refer to Analyzing Tables by Fill Level, Size, and Extents with SAPDBA. If the table needs extending but can no longer be extended, an error has occurred. Refer to the section on common error conditions below.

    Therefore, you need only reorganize a table when:

    · The table already has a very large number of extents.

    · The number of extents is steadily increasing.

    Note

    For the following reasons, SAP in general recommends you not to reorganize tables unless a clear justification exists:

    · Where possible, the database server allocates new extents for a table directly adjacent to existing ones. Therefore, tables tend to remain stored in a compact area rather than being "interleaved" (that is, scattered through the dbspace). This assumes that large enough gaps are available in the dbspace to store new table extents.

    · SAP access normally uses indexes, so interleaved tables pose no performance problem. Note that interleaved tables only adversely impact performance with sequential table scans, because the disk head has to be frequently repositioned, and this takes time.

    For more information, see Reorganization of Tables, Indexes, and Dbspaces with SAPDBA.

  2. Identify dbspaces that need extending.

    3. The following SAPDBA reports help you to identify the dbspaces that are running out of space:

    · Listing Dbspaces with SAPDBA

    · Analyzing Tables for Critical Next Extent Size with SAPDBA. Consider extending the dbspace of any tables listed on this report, because such tables cannot be properly extended due to a shortage of space in their dbspace. This assumes that the next extent sizes for the tables on the report are reasonable.

    For more information if you decide to extend the dbspace, see Extension of a Dbspace.

  3. You identify I/O bottlenecks.

Performance problems in database systems are usually related to disk I/O bottlenecks. These in turn are caused by a less than optimal distribution of data across the available disk drives. This can arise in the normal course of database growth, as new applications come on-line or when you reconfigure your system (adding new disk drives for example). If your operating system tools reveal that a high proportion of process time has been spent idle (that is, waiting for I/O), you need to diagnose on which disk device the problem is occurring.

You proceed as follows:
    1. You check the AIO queues using the Informix tool onstat -g ioq . Pay particular attention to the maxlen (maximum queue length for I/O) and totalops (total number of disk accesses) columns. If these show very much higher (or very much lower) values for particular queues, this indicates an unbalanced situation.
    2. You check the I/O activity for each disk device using onstat -g iof . This shows the actual disk device associated with each queue. Identify the devices that show very high (or very low) values.
    3. You determine the dbspaces associated with the overloaded disk devices using onstat -d . You can also use Listing devices with SAPDBA to find this information.
  1. You resolve I/O bottlenecks:
    1. You consider fragmenting (available from Informix version 7.1) the high-use tables in the affected dbspaces so that some fragments reside in other dbspaces.
    2. You move high-use tables to other dbspaces. Refer to Reorganizing a Single Table with SAPDBA or Reorganizing a Group of Tables with SAPDBA.
    3. You create a new dbspace for selected tables. Refer to Creation of a New Dbspace.

If some disk devices appear to be "cold" (with much less than normal activity), consider using these devices when you want to relocate or fragment high-use tables.

Caution

Be aware that SAP System upgrades might assume a standard dbspace layout and make corresponding assumptions. These assumptions might concern the space requirements to complete the upgrade and the distribution of tables in dbspaces. If you alter the standard configuration in the ways suggested here, you might need to adjust the space requirements accordingly. Also be aware that tables might possibly be moved back to the standard dbspace during the upgrade. In any case, note the names and sizes of any tables that you move or fragment.
  1. You identify common error conditions.

The following error messages appear when an application tries to write to a table but the database server cannot satisfy the request:

· Full dbspace

ISAMerr -131, ISAM error: no free disk space

In this case, you need to add a chunk to extend the dbspace, which you can do using SAPDBA. Refer to Adding a Chunk with SAPDBA.

· Table has reached maximum number of extents

ISAMerr -136, ISAM error: no more extents

In this case, you need to set up the table again using a larger storage area. Refer to Reorganizing a Single Table with SAPDBA.

Result

By recognizing problems before they become serious, you can optimize database management and so avoid downtime for your SAP System.

For more information about using SAPDBA to fix a space problem or error that you have identified, see:

 

See also:

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