While some amount of permanent space is used to store the definition of a global temporary table, temporary space is used to hold rows of materialized global temporary tables. Temporary space is allocated at the database or user level, but not the table level.
Define a temporary space limit with the TEMPORARY parameter of a CREATE/ MODIFY PROFILE or CREATE/MODIFY USER/DATABASE statement.
The maximum and default limits for temporary allocation are determined as described in the following table.
IF you … | AND a profile… | THEN the limit is inherited from the... |
---|---|---|
specify TEMPORARY in a CREATE/MODIFY USER/DATABASE statement | does not apply | immediate owner and may not exceed the limit of the immediate owner. |
applies | user who submitted the CREATE/MODIFY PROFILE statement. The limit may not exceed the limit of that user. The limit is determined as follows:
|
|
do not specify a TEMPORARY limit in a CREATE/MODIFY USER/ DATABASE statement | does not apply | immediate owner of the user. |
applies | profile specification. | |
applies but the SPOOL parameter is NULL or NONE | specification for the user who submitted the CREATE/MODIFY PROFILE statement, determined as follows:
|
When using the CREATE USER, CREATE DATABASE, or CREATE PROFILE statements to assign temporary space limits, keep your space limits in mind. Query the DBC.DiskSpaceV view to find the system levels for temporary space.
The following table describes the different types of temporary space.
Temporary Space Level | Description |
---|---|
CURRENTTEMP | This is the amount of space currently in use by Global Temporary Tables. This does not include the amount of permanent space required for the table header stored on each AMP for the base global temporary table definition.
|
PEAKTEMP | This is the maximum temporary space used since the last session. Temporary space is released when the session terminates. |
MAXTEMP | MaxTemp specifies the limit of space available for global temporary table rows. This does not include the amount of permanent space required for the table header stored on each AMP for the base global temporary table definition.
The value may not exceed the limit of the:
If you do not specify a value and the user is associated with a profile, MaxTemp defaults to the value of the profile, if defined. If the profile TEMPORARY is set to NULL or NONE, or the user is not associated with a profile, MaxTemp defaults to the value of the parent of the user. |