Number of bytes to be reserved for permanent storage for database_name. The space is taken from unallocated space in the database or user of the immediate owner.
You must specify a value for this option. There is no default.
- 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.
- 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.
- 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 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.
- Use the value of DBS Control DefaultPermSkewLimitPercent.
- Optional keyword that you can include for readability to indicate that the constant_expression or DEFAULT keyword specifies a percentage of allowable skew.
Example: Using a Constant Expression to Specify the PERM, SPOOL, and TEMPORARY Space for a Database
The following statement creates PERM space for database production_development with a size based on the constant expression 2,000,000 * (HASHAMP()+1), SPOOL with a size based on the constant expression 2,000,000 * (HASHAMP()+1), and TEMPORARY with a size based on the constant expression 2,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.
CREATE DATABASE production_development AS PERM = 2000000*(HASHAMP()+1), SPOOL = 2000000*(HASHAMP()+1), TEMPORARY = 2000000*(HASHAMP()+1);
Example: Creating a Database with a Permanent Space Skew Limit
CREATE DATABASE d1 AS PERM = 1e9 SKEW = 10 PERCENT;