16.20 - SPOOL - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Content Type
Programming Reference
Publication ID
B035-1144-162K
Language
English (United States)
Last Update
2019-05-24

Number of bytes allowed for spool files.

The default is the largest value that is not greater than the owner spool space, and that is a multiple of the number of AMPs on the system.

The default is the amount of spool space allocated to the owner of this user. As a general guideline, specify a minimum of 20 percent of the permanent space allocated for this user. See Teradata Vantage™ - Database Design, B035-1094 for details.

Neither n nor the evaluation of constant_expression can exceed the size of the owner spool space.

If you specify a PERMANENT space value of 0 bytes for a user, you must still specify some minimum number of bytes for its SPOOL space.
n
You can enter the number of bytes as an integer, decimal, or floating point value or as a constant expression whose evaluation determines the number of bytes. You can also enter the value using exponential notation. For example, you can write one thousand as either 1000 or 1E3.
n cannot exceed the spool space parameter in the profile of the creator. If no spool space is defined for that profile, then Teradata Database uses the spool space limit defined for the individual user-creator.
constant_expression
A constant expression is any SQL expression that does not make any column references. Specifying an appropriate constant expression for the SPOOL space size of a user enables Teradata Database to assign an optimal quantity of spool that scales to the size of your system by allocating SPOOL space on a per AMP basis.
When you specify a SPOOL space size based on a constant expression, the assigned size does not automatically scale if, for example, you add more AMPs to your system.
BYTES
Optional keyword that redundantly specifies the unit for the amount of space allowed.
SKEW
Keyword that you use to specify a skew limit for SPOOL space. You can specify a skew limit percentage that allows the maximum AMP space usage to be above the per-AMP quota, which is the system maximum space limit divided by the number of AMPs.
constant_expression
Constant expression or constant from 0 through 10000. Specify a value from 1 to 9999 to indicate an AMP-level limit, which is the per-AMP quota * (1+spoolskewlimit/100). Specify 0 to set space accounting to the per-AMP level, that is, no skew. A value of 10000 indicates unlimited skew, up to the system maximum space limit.
DEFAULT
Use the value of DBS Control DefaultSpoolSkewLimitPercent.
PERCENT
Optional keyword that you can include for readability to indicate that the constant_expression or DEFAULT keyword specifies a percentage of allowable skew.

Example: Creating a User with a Spool Space Skew Limit

On a system with 4 AMPs, you create a user with 1 gigabyte of spool space. The per-AMP quota of spool space is 250 megabytes. If you set the spool space skew limit to 20%, any AMP is allowed a skew limit of 50 megabytes over the per-AMP quota of spool space. An AMP can use up to 300 megabytes of spool space as long as total spool space used does not exceed the 1 gigabyte global limit for spool space.
     CREATE USER user1 AS
       PASSWORD = (EXPIRE = 0), 
       PERM = 1e9
       SPOOL = 1e9 SKEW = 20 PERCENT;