Start of Content Area

Procedure documentation Analyzing Tables for Critical Next Extent Size with SAPDBA  Locate the document in its SAP Library structure

Use

You can use SAPDBA for Informix to analyze tables for which the next extent size is critical. If such tables need to be extended, the database server cannot meet the demand for the next extent in full.

Note

If a table cannot be fully extended, an alert is raised in the Release 4 alert monitor in the Computing Center Management System (CCMS) of the R/3 System. For more information, see Monitoring the Database with the Release 4 Alert Monitor (Informix)

Prerequisites

Procedure

  1. Choose Reorganization → Analyze Table Reports → Table with Critical Next Extent Size in SAPDBA for Informix.
  2. In the field dbspace, you specify the dbspace that SAPDBA searches for tables to include in the report.

    3. The default is ALL, that is, tables from all dbspaces are included in the report. If you do not know the name of the dbspace you want, you can use a partial entry (for example, ps*) and SAPDBA lists the corresponding dbspaces.

  3. Execute the report.

    4. SAPDBA displays the report, listing tables in descending order of next extent size.

    If you specify ALL for the dbpsace, the report looks like the following example:

    table

    dbspace

    next_extent [KB]

    largest_free_seg [KB]

    1) d010s

    psapsource

    10240

    2

    2) d301s

    psapsource

    10240

    2

    3) d311m

    psapsource

    10240

    2

    4) adrg

    psapstab

    10240

    2

    .....

    		      

    If you specify a dbspace, the report looks like the following:

    dbspace:

    psapsource

    largest free space segment: 3928 KB

    table

    next_extent [KB]

    1) d010s

    10240

    2) d301s

    10240

    3) d311m

    10240

    .....

    		  

    The entries on the report have the following meanings:

    Field or Column

    Meaning

    dbspace

    Shows where the dbspace is stored.
    This only appears when you specify ALL for the dbspace.

    next_extent

    Shows how large the table’s next extent ought to be, in kilobytes.

    largest_free_seg or largest free space segment

    Shows the size of the largest free contiguous area of storage in the dbspace, in kilobytes. If a table needs to be extended, this is the largest single piece of storage available for the extension.

  4. To see more detailed storage information for a single table, choose one of the numbers on the left of the report. For more information, see Analyzing Table Information with SAPDBA.

    5. Caution

    You must extend dbspaces for tables with a critical next extent size. If tables in the dbspace are expanding, soon the requirement for extra storage cannot be met at all and you get an error message, "SQL error -271".

    If you do not extend dbspaces containing tables with a critical next extent size, the tables might end up with a large number of very small extents. The result is performance loss, especially on sequential table scans.

  5. For more information about printing the report, see Printing a SAPDBA Report.

Result

You now know which tables have a critical next extent size. Be sure to extend dbspaces containing tables with a critical next extent size. Refer to Adding a Chunk with SAPDBA. For more information if you want to reallocate the dbspace and reorganize all its tables, see Reorganizing a Dbspace and Its Tables with SAPDBA.

 

See also:

Informix documentation