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.
Comma or colon (no white space)
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.


DBSPACETEMP can contain dbspaces with a non-default page size, but all of the dbspaces in the DBSPACETEMP list must have the same page size.

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, the database server does not use the newly created temporary dbspace until you set the DBSPACETEMP configuration parameter or environment variable and restart the server.

The DBSPACETEMP environment variable takes effect immediately and overrides the DBSPACETEMP configuration parameter.

Copyright© 2020 HCL Technologies Limited