Start of Content Area

Procedure documentation Analyzing Tables by Fill Level, Size, and Extents with SAPDBA  Locate the document in its SAP Library structure

Use

You can use SAPDBA for Informix to analyze tables by fill level, size, number of extents, and number of extents left.

Note

If a table has very few extents left, 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 in SAPDBA for Informix.
  2. Choose one of the following:

    3. - Tables by Fill Level

    - Tables by Size

    - Tables by Number of Extents

    - Tables by Number of Extents left

  3. In the field dbspace, you specify the dbspace that SAPDBA searches for tables to include in the report.

    4. 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 matching dbspaces.

  4. In the field threshold value, you specify a minimum value that SAPDBA uses to filter the tables for the report.

    5. The threshold varies according to the report you have selected, as follows:

    Selection Criterion

    Report title

    Table size KB

    Tables by Fill Level

    Table size KB

    Tables by Size

    Number of extents

    Tables by Number of Extents

    Number of extents

    Tables by Number of Extents Still Available

    Note

    If possible, set a sensible threshold value to restrict reports to a single dbspace. This means that the average processing time to produce a report is usually between a few seconds and a few minutes. If you set low thresholds and include all dbspaces then the run times are significantly longer.

  5. In the field sort order, you specify how the report is sorted, either ascending or descending.
  6. Execute the report.

SAPDBA displays the report with entries similar to the following example (this report is for all dbspaces by size):

table

fill [%]

size [KB]

extents

dbspace

1) ddxtf

71

25280

1

psapddic

2) ddprs

71

20480

1

psapprot

3) d331l

-

19480

1

psapload

4) ce2s001

-

17760

1

psapstab

5) ddlog

74

15000

1

psapprot

.....

        

The tables are listed in order of the report title. The entries on the report have the following meaning:

Column

Meaning

fill [%]

Indicates approximately how full a table is. Consider the following when reading this value:

  • Fill level might be out of date in some cases because the statistics for the table have not recently been updated. If you choose the action to display more information about such a table, you can request that SAPDBA updates statistics to calculate a current fill level.
  • Fill level is not displayed if the table contains blobpage information (because this would take too long). Instead, you see a hyphen. SAPDBA can calculate the fill level in such cases when you view table information. Therefore, such tables do not appear in the Tables by Fill Level report. However, they are included in the other table reports and appear with a hyphen in the fill level field (for example, table d331l in the previous example).
    For information about blobpages, see the Informix documentation.

size [KB]

Shows the total storage used by the table, in kilobytes. This includes all space currently allocated to the table (that is, data, index and blobpages). The size given here is not necessarily full with data. See the fill [%] field for how full the table is.

extents

Shows the number of physical storage units occupied by the table. It is not advisable for a table to have a large number of extents.

dbspace

Shows where the table is stored. If the table is fragmented (possible with Informix version 7.1 or later), it is stored in a number of different dbspaces instead of in a single dbspace.

This only appears when you specify ALL for the dbspace.

  1. 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.
  2. For more information about printing the report, see Printing a SAPDBA Report.

Result

You now have a better picture of the storage situation for the tables in question. For more information if you want to perform a reorganization, see:

 

See also:

Informix documentation