Use of temporary dbspaces for sorting and temporary tables

About this task

Even though the secondary database server is in read-only mode, it does write when it must sort or create a temporary table. Temporary dbspaces explains where the database server finds temporary space to use during a sort or for a temporary table.

To prevent the secondary database server from writing to a dbspace that is in logical-recovery mode, you must take the following actions:

Procedure

  1. Ensure that one or more temporary dbspaces exist.
    For instructions on creating a temporary dbspace, see Creating a dbspace that uses the default page size.
  2. Perform one of the following actions:
    • Set the DBSPACETEMP parameter in the onconfig file of the secondary database server to the temporary dbspace or dbspaces.
    • Set the DBSPACETEMP environment variable of the client applications to the temporary dbspace or dbspaces.

Results

Temporary tables created on secondary servers (SD secondary servers, RS secondary servers, and HDR secondary servers) must be created using the WITH NO LOG option. Alternatively, set the TEMPTAB_NOLOG configuration parameter to 1 or 2 on the secondary server to change the default logging mode for temporary tables to no logging. Tables created with logging enabled result in ISAM errors.

For SD secondary servers, set the SDS_TEMPDBS configuration parameter for configuring temporary dbspaces to be used by the SD secondary server.

For SD secondary servers, it is not necessary to explicitly add a temporary dbspace because the secondary server allocates the chunk specified by SDS_TEMPDBS when the server is started. It is only necessary to prepare the device that accepts the chunk.

If the primary server in a high-availability cluster fails and an SD secondary server takes over as the primary server, then the value set for the SDS_TEMPDBS configuration parameter on the SD secondary server is used for temporary dbspaces until the server is restarted. You must ensure that the value specified for the SDS_TEMPDBS configuration parameter on the SD secondary server is different than the value specified on the primary server. After the SD secondary server is restarted, the DBSPACETEMP configuration parameter is used.