How Informix Dynamic Server uses the temporary dbspace

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.

DBSPACETEMP config값 또는 환경변수에 명시한 dbspace를 사용하여 round-robin fragmented 테이블이 생성된다.

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.

DBSPACETEMP에 명시한 dbspace를 먼저 사용한다. 명시된 dbspace가 없다면 데이터베이스가 생성된 dbspace를 사용한다.

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.

PSORT_DBTEMP와 DBSPACETEMP 환경변수는 DBSPACETEMP config 설정값보다 우선한다.

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.

로깅 데이터베이스에서 TEMPTAB_NOLOG config 는 0이고, TEMP 테이블 생성시 WITH NO LOG 옵션을 사용하지 않는다고 가정

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.

How Informix Dynamic Server uses the temporary dbspace

http://www-01.ibm.com/support/docview.wss?uid=swg21292093

Preventing Informix Servers system overload from intensive I/O activity

http://www-01.ibm.com/support/docview.wss?uid=swg21567274