15.10 - PERMANENT - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Teradata Database
Release Number
December 2015
Content Type
Programming Reference
Publication ID
English (United States)

A revised value for fixed space allocation for the specified database, in bytes.

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.
A constant expression is any SQL expression that does not make any column references. Specifying an appropriate constant expression for the PERM space size of a database enables Teradata Database to assign an optimal quantity of PERM space that scales to the size of your system by allocating PERM space on a per AMP basis.
When you specify a PERM space size based on a constant expression, the assigned size does not automatically scale if, for example, you add more AMPs to your system.
Optional keyword that redundantly specifies the unit for the amount of space allowed.

Example: Change the permanent space allocation

Change the permanent space allocation for the personnel database to 6,000,000 bytes.

     MODIFY DATABASE personnel AS
     PERMANENT = 6000000 BYTES;

Example: Using a Constant Expression to Modify the PERM and SPOOL Space for a Database

The following request modifies the PERM space for database production_development to a size based on the constant expression 3,000,000 (HASHAMP()+1) and SPOOL to a size based on the constant expression 3,000,000 (HASHAMP()+1). The expressions calculate the number of AMPs in the current system and scales the PERM and SPOOL space for the production_development database to that size.

This is the original definition of database production_development.

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

The following MODIFY DATABASE request alters the PERM and SPOOL space allocations for this database as follows.

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