Margaret Wood

• Jan 18th, 2015

 

Question

How does Informix use temporary dbspaces according to database logging mode, SQL statement WITH NO LOG, temp dbspace parameters settings, etc.?

Answer


INTRODUCTION

IBM® Informix® Dynamic Server (IDS) uses two types of temporary objects to store temporary data:temporary files and temporary tables. How Informix server use the temporary dbspace depends on whether temporary objects are logged or not.

IDS has many factors that effect this, such as database logging mode, create temp table WITH NO LOG, DBSPACETEMP or PSORT_DBTEMP setting, how a temporary space is created, and so on.

Temporary tables in the server can be automatically routed to a dbspace. The DBSPACETEMP environment variable can be set to one or more dbspaces. If the DBSPACETEMP environment variable is not set, the server uses the value of the DBSPACETEMP configuration parameter. Temporary tables are fragmented across the dbspaces listed in either the environment variable or the configuration parameter. This occurs regardless of whether the query is a PDQ query or not.

If DBSPACETEMP is not specified, the temporary table is placed in either the root dbspace or the dbspace where the database was created. SELECT...INTO TEMP statements place the temporary table in the root dbspace. CREATE TEMP TABLE statements place the temporary table in the dbspace where the database was created.

Temporary files can be created in either dbspaces or in file-system space. If PSORT_DBTEMP is set to one or more directories, temporary files are created in round-robin fashion across all the directories listed (the first file in one directory and the second file in the next) regardless of whether the sort is a parallel sort or not. If the PSORT_DBTEMP environment variable is not set, temporary files are fragmented across the dbspaces listed in the DBSPACETEMP environment variable. This means space is allocated and utilized in all the dbspaces for a single temporary file regardless of whether the query is a PDQ query or not. If the DBSPACETEMP environment variable is not set, the dbspaces listed in the DBSPACETEMP configuration parameter are used to store temporary files.

RECOMMENDATION

It is recommended that you create multiple temporary dbspaces on different devices in your server instance. A performance benefit results because temporary dbspaces are not logged nor are they archived. Also, when temporary files and tables are created they are fragmented across the available temporary dbspaces, therefore enabling parallel access.

Temporary tables should be created in a dbspace that is specifically designated for temporary tables within the Informix system. A temporary dbspace does not accommodate logging. Therefore, temporary tables created in a temporary dbspace must be from an unlogged database or be created using the syntax WITH NO LOG.

If your database is not logged and DBSPACETEMP exists, then temporary tables are created automatically in DBSPACETEMP. In databases that are logged, temporary tables are logged by default and are not created in DBSPACETEMP. To utlize the DBSPACETEMP feature it is necessary to append the SQL phase WITH NO LOG to either the SELECT...INTO TEMP, or the CREATE TEMP TABLE statements.


EXAMPLES

Assuming the databases has logging:

1. If we have created a dbspace named tmpdbs,but we could not see it was marked as T in the result of onstat -d. We set DBSPACETEMP configuration parameter to tmpdbs.
On this condition, tmpdbs will be used for logged temporary tables.That means if a temp table is created with WITH NO LOG option, the server will not use it.

2. If we have created a temporary dbspace (it was marked as T in the result of onstat -d). However, we have not set the DBSPACETEMP configuration parameter to this tmpdbs.
On this condition,the tmpdbs dbspace will not be used when creating temporary tables unless you specify the "in dbspace" clause when creating the temporary table.