Informix temporary tablespace

Informix temporary tablespace

In Informix databases, we often create temporary tables to process Temporary Information in applications. You can create a temporary table in either of the following ways:
Use the select into temp statement to implicitly create a temporary table
Use the create temp TABLE statement to create a temporary table
If the database adopts non-log mode, after dbspacetemp environment variables or configuration parameter settings, the temporary table is automatically created in the data space specified by dbspacetemp environment variables or configuration parameters; if the database adopts the log mode, the created temporary table is logged by default and will not be created in the data space specified by the dbspacetemp environment variable or configuration parameter, select... the temporary table created by the into temp statement is created in the root dbspace. The temporary table created by the create temp TABLE statement is created in the data space of the database. If you want to create a temporary table in the data space specified by the dbspacetemp environment variable or configuration parameter, we need to use the select into temp with no log statement or the create temp table with no log statement to create a temporary table.

Temporary tables are created in the corresponding data space in the following order of priority:
Data Space specified by dbspacetemp environment variable
Data Space specified by dbspacetemp configuration parameters
If the dbspacetemp environment variable is set, the temporary table is created in the data space specified by the dbspacetemp environment variable. If the dbspacetemp environment variable is not set, the temporary table is created in the data space specified by the dbspacetempp configuration parameter.

For performance considerations, we generally recommend that you create multiple temporary tablespaces on multiple physical disks. In this way, when creating a temporary table, it will be sliced to all temporary tablespaces to improve the concurrent processing efficiency.
In a database that uses the log mode, logs must be recorded for all DML operations on the temporary table without the with no log option, temporary tables are not created in the temporary data space specified by dbspacetemp environment variables or configuration parameters. Data is often written to the root dbspace, which affects system performance, when creating a temporary table, you often forget the with no log option. To solve the problem above, Informix 11 began to provide a method to Disable Logging On the temporary table. In this way, even if the with no log option is not added during the creation of the temporary table, the temporary table is also created in the temporary data space specified by the dbspacetemp environment variable or configuration parameter.

You can use either of the following methods to Disable Logging on a temporary table:
Modify the onconfig Configuration Parameter temptab_nolog 1
Dynamically changed using the onmode command
Onmode-WF "temptab_nolog = 1"
Onmode-WM "temptab_nolog = 1"
The-WM option takes effect immediately after the parameter value is changed;-WF option takes effect immediately after the parameter value is changed, and the new parameter value is written to the onconfig configuration file.
Use the temptab_nolog parameter to Disable Logging On the temporary table. This parameter can improve the performance of applications, especially in data replication environments with HDR secondary servers, RS secondary servers, or SD secondary servers, because it prevents Informix from transmitting temporary tables over the network.