IBM 000-8697 User Manual

IBM Informix OnLine Database Server Administrator’s Guide

Execute tbcheck -pr to obtain the chunk information that is stored in the root
dbspace reserved page. Refer to

for a detailed description of each

field in the chunk reserved page,

PCHUNK

.

Execute tbcheck -pe to obtain the physical layout of information in the
chunk. The chunk layout is sequential, and the number of pages dedicated to
each table is shown. The following information displays:

■

Dbspace name, owner, and number

■

Number of chunks in the dbspace

This output is useful for determining the extent of chunk fragmentation. If
OnLine is unable to allocate an extent in a chunk despite an adequate number
of free pages, the chunk might be badly fragmented.

Refer to

 for further information about the tbcheck -pe output.

Depending on the specific circumstances, you might be able to eliminate
fragmentation by using the

ALTER TABLE

statement to rebuild the tables. For

this tactic to work, the chunk must contain adequate contiguous space in
which to rebuild each table. In addition, the contiguous space in the chunk
must be the space that OnLine normally allocates to rebuild the table. (That
is, OnLine allocates space for the

ALTER TABLE

 processing from the

beginning of the chunk, looking for blocks of free space that are greater than
or equal to the size specified for the

NEXT EXTENT

. If the contiguous space is

located near the end of the chunk, OnLine could rebuild the table using
blocks of space that are scattered throughout the chunk.)

Use the

ALTER TABLE

 statement on every table in the chunk. Follow these

steps:

For each table, drop all the indexes except one.

Cluster the remaining index using the

ALTER TABLE

statement.

Re-create all the other indexes.

You eliminate the fragmentation in the second step, when you rebuild the
table by rearranging the rows. In the third step, you compact the indexes as
well because the index values are sorted before they are added to the B+ tree.
You do not need to drop any of the indexes before you cluster one but, if you
do, the

ALTER TABLE

 processing is faster and you gain the benefit of more

compact indexes.