SPOOL - Advanced SQL Engine - Teradata Database

Maximum number of bytes allowed for spool files in profile_name. The default is null, which uses the setting defined for the individual user assigned to the profile.

Changes to spool space limits in a profile take effect immediately upon submitting the MODIFY PROFILE request.

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 Vantage 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 size of a database enables Vantage 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.

BYTES

Optional keyword that redundantly specifies the unit for the amount of space allowed.

Example: Modify profile spool space

Assume user anderson has a spool setting of 500,000 bytes and user brewster has a spool setting of 800,000 bytes.

User anderson can create a profile named finance with a spool setting of 500,000 bytes.

     CREATE PROFILE finance AS 
     SPOOL = 500000;

User anderson can assign this profile to user brewster, effectively reducing the spool limit from 800,000 to 500,000 bytes.

     MODIFY USER brewster AS 
     PROFILE = finance;

If the owner of anderson later increases the spool allotment to 1,000,000, anderson can also increase the finance profile spool setting up to 1,000,000 bytes. All users assigned that profile, including brewster, automatically get 1,000,000 bytes of spool space.

     MODIFY PROFILE finance AS
     SPOOL = 1000000;

If the owner of anderson later decreases the spool allotment back to 500,000 bytes, the finance profile and the allotment for brewster are not affected.

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

The following request modifies the SPOOL space for the profile research_and_development to a size based on the constant expression 3,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.

This is the original definition of profile research_and_development.

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

The following MODIFY PROFILE request alters the SPOOL space allocation for this profile as follows.

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