DBSPACETEMP configuration parameter

Use the DBSPACETEMP configuration parameter to specify a list of dbspaces that the database server uses to globally manage the storage of temporary tables.

DBSPACETEMP improves performance by enabling the database server to spread out I/O for temporary tables efficiently across multiple disks. The database server also uses temporary dbspaces during backups to store the before-images of data that are overwritten while the backup is occurring.

onconfig.std value
Not set. Temporary tables are stored in the root dbspace.
separators
Comma or colon (no white space)
values
One or more dbspace names. Dbspaces can be standard dbspace, temporary dbspaces, or both. Separate dbspace names with a colon or comma. The length of the list cannot exceed 254 bytes.
takes effect
After you edit your onconfig file and restart the database server.
When you reset the value dynamically in your onconfig file by running the onmode -wf command.
When you reset the value in memory by running the onmode -wm command.

Usage

DBSPACETEMP can contain dbspaces with a non-default page size and dbspaces in the DBSPACETEMP list can have different page sizes.

If a client application needs to specify an alternative list of dbspaces to use for its temporary-table locations, the client can use the DBSPACETEMP environment variable to list them. The database server uses the storage locations that the DBSPACETEMP environment variable specifies only when you use the HIGH option of UPDATE STATISTICS.

If both standard and temporary dbspaces are listed in the DBSPACETEMP configuration parameter or environment variable, the following rules apply:
  • Sort, backup, implicit, and nonlogging explicit temporary tables are created in temporary dbspaces if adequate space exists.
  • Explicit temporary tables created without the WITH NO LOG option are created in standard (rather than temporary) dbspaces.
When you create a temporary dbspace with the onspaces utility or the SQL administration API admin() or task() function, the database server does not use the newly created temporary dbspace until you modify the DBSPACETEMP configuration parameter to include the new temporary dbspace or set the DBSPACETEMP environment variable and restart the session.
Note: The DBSPACETEMP configuration parameter may be modified dynamically with the onmode -wf or onmode -wm commands.

When set in a session, the DBSPACETEMP environment variable overrides the DBSPACETEMP configuration parameter.