The XDB Server allows a single database to span multiple physical devices, as long as no single object of the database spans more than one physical device. Thus, while no database object (table or index) can exceed the size of its physical storage device, objects can be spread among many devices to reduce I/O contention.
For example:
Ideally, each table should be stored on a different disk drive so queries requiring access to multiple tables will not create I/O contention on one disk. If there are more tables than disk drives, tables can be arranged such that those least likely to be accessed in the same query would be grouped together.
Generally, indexes should not be placed on the same disk drive as other information (tables or indexes) that will likely be accessed by a query that uses the index. Some indexes, however, can be placed with any other object without creating disk I/O contention.
If an index is used for list prefetch or an index contains coverage for a particular table (all columns referenced for a particular table are within the index), the index will not be accessed at the same time as the base table. See the section Optimizing Query Processing for more information on list prefetch. Thus, all indexes used for list prefetch can be placed on any physical device because these indexes are accessed once, and only once, during the processing of a particular query. Likewise, an index which contains full coverage and is not involved in a join will be the only object accessed in a particular query, and thus could be placed on any physical device as well.
Temporary files should be written to or read from a disk drive other than the one used to store data and indexes, or a drive containing data that is little used by most queries. Temporary files are created when the SQL engine performs a sort that overflows the configured Sort Space Size (as specified in the Configuration Utility). Operations that perform sorts include CREATE INDEX statements and SELECTS that involve DISTINCT, ORDER BY, UNION or GROUP BY.
The guiding principle in determining object location is to spread the workload evenly among the drive controllers and disk heads. If disk head movement can be kept to a minimum and drive controller usage evenly dispersed, optimal performance can be obtained. Table and index caching can improve performance even more. See the section Improving Production Processing for more information.
Given that the server stores database objects in separate files, tuning of database object locations can be done at any time. By simply copying the database object file to a different physical device and updating the stogroup or environment variable pathname for that particular database object, different space allocation strategies can be tested.
Paths for stogroups are maintained in the SYSIBM.SYSVOLUMES table. If a table space was created without specifying a stogroup, the table space's path will be that of the database (either ..\loc-path\dbname (Windows) or ../loc-path/dbname (UNIX) or the path of the stogroup that was used to create the database). For greatest flexibility, each table should be associated with its own stogroup (drive and path) when it is created.
Paths for indexes and other objects can be specified in environment variables in the server machine's configuration file. (See the chapter Environment Variables for more information.)
Guidelines on how to position tables, indexes and temporary files are provided on the following pages.