15.10 - SPOOL - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Teradata Database
December 2015
Programming Reference

A keyword allowing you to specify the number of bytes allowed for spool files for profile_name. The default is null, which causes the system to use the setting defined for the individual user.

If no spool space is defined in the profile assigned to a user, Teradata Database uses the setting defined for the individual user. If spool space is not defined for either the user profile or the user, the system uses the spool space assigned to the owner of the space in which the user was created.

Profiles can be assigned to a proxy user in a trusted session so that the profile attributes of the permanent proxy user, such as perm space and spool, are used. If a permanent proxy user logs on directly and runs queries while also logged on through a middle tier application trusted session with queries being run as a proxy, the SPOOL and TEMP usage accumulation for that user includes both. The individual user’s query fails when the collective usage exceeds the individual user’s limits.

n cannot exceed the spool space parameter in the profile of the creator. If no spool space is defined for that profile, then the spool space limit defined for the user setting of the creator is used.
n and the evaluation of constant_expression both refer to bytes, whether or not the optional BYTES keyword is specified.
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.
BYTES is an optional keyword that redundantly specifies the unit for the amount of SPOOL space allowed.
A constant expression is any SQL expression that does not make any column references. Specifying an appropriate constant expression for the SPOOL size of a profile enables Teradata Database to assign an optimal quantity of SPOOL that scales to the size of your system.
When you specify a SPOOL size based on a constant expression, the assigned size does not automatically scale if, for example, you add more AMPs to your system.

Example: Using a Constant Expression to Specify the SPOOL Space for a Profile

The following request creates SPOOL with a size based on the constant expression 2,000,000 (HASHAMP()+1). The expression calculates the number of AMPs in the current system and scales the SPOOL space for the research_and_development profile to that size.

     CREATE PROFILE research_and_development AS
       DEFAULT DATABASE = it_dev,
       PASSWORD = (EXPIRE = 0),
       SPOOL = 2000000*(HASHAMP()+1);