All databases use work files for sorting, which can occur when queries are executed (SELECT or FETCH statements with ORDER BY clauses) or when tables are restructured (for example, using the MODIFY statement or equivalent operation in VDBA). While small sorts are performed in memory, larger sorts use temporary sort files. Depending on the size of the tables involved in the sort, the temporary disk space requirements can be large (see Space Requirements for Sorts on page 403).

You specify the initial, or primary, location (or use the default location) for work files when you create a database. The area mapped to this location is used for all work files.

To use additional locations, extend a database. When you extend a database in this manner, sort space can be spread among multiple work locations.

When extending a database to use additional work locations, classify them as follows:

After a database has been extended to use an additional work location, you can subsequently modify the work area’s classification using accessdb or the Alter Database dialog in VDBA.

A session automatically uses all defaultable work locations to which the database has been extended (including the initial work location). In addition, the session can issue SET WORK LOCATIONS statements to specify auxiliary work locations to use. Using this statement, a session can dynamically add and drop work locations and replace the set of locations currently in use. The set work locations statements affect the current session only—their effects disappear when the session ends.

For more information on using set work locations, see the entry for the SET statement in the SQL Reference Guide.