Analyzing database fragmentation

To determine the degree of fragmentation for tables in a database, use PROUTIL TABANALYS as shown: 

 proutil db-name -C tabanalys

You can run PROUTIL TABANALYS while the database is in use; however, PROUTIL generates only approximate information.

In the TABANALYS display, check the following fields:

  • Count — The total number of record fragments found for each table in the database.
  • Fragments Factor — The degree of record fragmentation for each table. If the value is 2.0 or greater, dumping and reloading will improve disk space usage and performance. If the value is less than 1.5, dumping and reloading is unnecessary.
  • Scatter Factor — The degree of distance between records in the table. The optimal value for this field varies from database to database. To determine the optimal value for your database, run the TABANALYS on a freshly loaded database.

The following sample shows an excerpt from a PROUTIL TABANALYS display:

RECORD BLOCK SUMMARY FOR AREA "Info Area" : 7
-------------------------------------------------------
                                -Record Size (B)- ---Fragments--- Scatter
Table        Records      Size   Min   Max  Mean   Count  Factor     Factor
Customer          83     12.1K   118   222   149      83    1.0        1.5
Invoice          147     5613B    32    45    38     147    1.0        1.2
Item              55     5092B    39   229    92      55    1.0        1.3
Local-Default     10      704B    55    82    70      10    1.0        1.2
Ref-Call          13     2480B    81   328   190      13    1.0        1.4
Salesrep           9      746B    79    87    82       9    1.0        1.2
State             51     1755B    29    40    34      51    1.0        1.0

RECORD BLOCK SUMMARY FOR AREA "Order Area" : 8
-------------------------------------------------------
                                -Record Size (B)- ---Fragments--- Scatter
Table        Records      Size   Min   Max  Mean   Count  Factor     Factor
Order           207      11.5K    53   61    56     207     1.0        1.3
Order-Line      873      30.3K    33   38    35     873     1.0        1.2
          ----------------------------------------------------------------
Totals:        4190      431.1K    6  1156   105    4263    1.0        2.5