16.20 - PERMANENT - 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

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

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.
The value of n or the number of bytes determined from the evaluation of constant_expression cannot exceed the permanent space of the owner.
constant_expression
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.
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 PERMANENT space. You can specify a skew limit percentage that allows the maximum AMP space usage to be above the per-AMP quota, that 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+permskewlimit/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 DefaultPermSkewLimitPercent.
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: 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, SPOOL, and TEMPORARY Space for a Database

The following statement modifies the PERM space for database production_development to a size based on the constant expression 3,000,000 * (HASHAMP()+1), SPOOL to a size based on the constant expression 3,000,000 * (HASHAMP()+1), and TEMPORARY 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 scale the PERM, SPOOL, and TEMPORARY 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),
       TEMPORARY = 2000000*(HASHAMP()+1);

The following MODIFY DATABASE request changes the PERM, SPOOL, and TEMPORARY 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),
       TEMPORARY = 3000000*(HASHAMP()+1);

Example: Modifying a Database to Add a Permanent Space Skew Limit

Assume a system with 4 AMPs and a database with 1 gigabyte of permanent space. The per-AMP quota of permanent space is 250 megabytes. If you set the permanent space skew limit to 10%, any AMP is allowed a skew limit of 25 megabytes over the per-AMP quota of permanent space. An AMP can use up to 275 megabytes of permanent space as long as total permanent space used does not exceed the 1 gigabyte global limit for permanent space.
     MODIFY DATABASE d1 AS 
       PERM = 1e9 SKEW = 10 PERCENT;