How Are Storage Parameter Tables Used?

Multiple Index Locations

When you use the Database Space Definition form to define a database space, you also define a separate location to hold the indexes in that database space. This method restricts you to one location for all indexes on a table.

If a table has multiple indexes that are frequently accessed simultaneously, putting them on different disks could speed up physical input and output. Also, a large table with many indexes can make it impossible to put all indexes on that table on a single disk drive. In either of these cases, it helps to separate the indexes on different drives.

update LAW_DBA_TABLE
    set initial_extent = 24,
    next_extent = 12
    where table_name = ‘APCOMMENTS';

The law_dba_index table lets you override the settings on the Database Space Definition form and separate the indexes from a single table to different file groups. For example, to move the first index on the APCOMMENTS table to a new Microsoft SQL Server file group, issue the following command from a SQL utility provided by the database vendor.

After you change the value in law_dba_index, run the bldmsf2000ddl utility to drop and rebuild the APCSET1 index using the new definition. At the command prompt, type:

bldmsf2000ddl -URI dataarea apcomments

All indexes on APCOMMENTS now reside in the table spaces specified in law_dba_index. If an index has no value for the table space field in law_dba_index, the index defaults to the location specified on the Database Space Definition form.

Fragmented Table or Index Repair

A fragmented table or index can cause performance problems, primarily because the disk head must search over multiple sectors to retrieve data. If a fragmented table or index occurs because of sequentially loaded data, simply rebuild the index using the bldmsf2000ddl utility to defragment the index.