MODIFY DATABASE Syntax Elements - Teradata Vantage

database_name

Name of the database to be modified.

PERMANENT

PERM

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

SPOOL

The maximum number of bytes allowed for spool files in database_name.

TEMPORARY

Disk usage for a materialized global temporary table is charged to the temporary space allocation of the user who referenced the table. Temporary space is reserved before spool space for any user defined with this characteristic.

If default temporary space is not defined for a database, then the space allocated for any materialized global temporary tables created in that database is set to the maximum temporary space allocated for its immediate owner.

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.

With PERMANENT or PERM, the value of bytes cannot exceed the permanent space of the owner.

With SPOOL, the value of bytes 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.

A constant expression is any SQL expression that does not make any column references. Specifying an appropriate constant expression for bytes enables Vantage to assign an optimal quantity of bytes that scales to the size of your system by allocating space on a per AMP basis.

When bytes is a constant expression, the assigned size does not automatically scale if, for example, you add more AMPs to your system.

skew

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.

Specify skew as a 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

This is set by the system.

PERCENT

Optional keyword that you can include for readability to indicate that the skew or DEFAULT keyword specifies a percentage of allowable skew.

account_string

The account to be charged for the space used by this database. If not specified, the account defaults to the default account of the immediate owner of the database.

Each account_string must follow Vantage object naming rules. See Object Names.

When specifying an account string for a database, consider that a user session may default to the account_string specified for the database that owns the user if no other user or profile based account applies.

DEFAULT MAP

You can specify an existing contiguous or sparse map as the default map for the database. You can also remove a default map that is defined for the database by setting the default map to null.

You must have been granted the specified map unless the map is the same as the default map of the database modifier.

map_name

Name of an existing contiguous or sparse map.

You cannot specify TD_DataDictonaryMap or TD_GlobalMap.

OVERRIDE ON ERROR

Use the default map if an error occurs when the MAP clause is specified for a CREATE TABLE or CREATE JOIN INDEX statement.

This is the default.

OVERRIDE NOT ON ERROR

Do not use the default map if an error occurs when the MAP clause is specified for a CREATE TABLE OR CREATE JOIN INDEX statement.

NULL

A default map is not associated with the database. You can specify NULL to remove the default map that is set for the database.

DEFAULT STORAGE

You can specify default storage and an override option for a user or database.

If you omit this clause, the user has no default storage or override option.

NULL

User has no default storage or override option.

storage_name

Name of user default storage. The storage_name must exist.

OVERRIDE [NOT] ON ERROR

Determines what happens when the user issues a CREATE TABLE statement with a STORAGE clause that causes an error.

With OVERRIDE ON ERROR, CREATE TABLE does the following and warns the user that it has done so:

• Uses the user default storage.
• Ignores clauses that are invalid for the user default storage.

  For example, if the default storage is NOS, CREATE TABLE ignores the PRIMARY INDEX clause.

With OVERRIDE NOT ON ERROR, CREATE TABLE does not use the user default storage and one or more errors occur.

Default: OVERRIDE ON ERROR

[NO] FALLBACK [PROTECTION]

A new default for duplicate copy protection of each data table subsequently created in the database. The current fallback setting for existing data tables remains unchanged.

The FALLBACK keyword used alone implies PROTECTION.

NO sets the default to not provide duplicate copy protection for data tables created in the database.

For Object File System tables, NO FALLBACK is ignored.

You cannot use the NO FALLBACK option and the NO FALLBACK default on platforms optimized for fallback.

[ NO | DUAL ] [BEFORE] JOURNAL

A new default for the number of before change images to be maintained for data tables subsequently created in the database.

This option can appear twice in the same request: once to specify a BEFORE or AFTER image, and again to specify the alternate type. If only one type is specified, then the current default is modified only for that type.

Journal options are not supported for tables with row sizes greater than 64KB.

NO

Terminates any current journaling default.

DUAL

If the JOURNAL keyword is specified without NO or DUAL, single copy journaling is implied. If journaling is specified, a DUAL journal is maintained for data tables with FALLBACK protection.

Existing images are not affected until the corresponding table is updated.

BEFORE

The JOURNAL keyword without BEFORE or AFTER indicates that both types of images are to be maintained. The current default for either or both types is modified accordingly. For example, if only AFTER JOURNAL is specified, the current default for before change images remains in effect.

If BEFORE and AFTER are specified, the two must not conflict.

[ NO | DUAL | [NOT] LOCAL ] AFTER JOURNAL

The type of image to be maintained for the table. Any existing images are not affected until the table is updated.

NO

After-change images are not maintained for the table.

DUAL

Dual after-change images are maintained for the table.

LOCAL

Single after-image journal rows for non-fallback data tables are written on the same virtual AMP as the changed data rows.

NOT LOCAL

Single after-image journal rows for non-fallback data tables are written on another virtual AMP in the compute cluster.

DEFAULT JOURNAL TABLE

A redefinition for the current journal table.

Specifying this option does not change the status of existing data tables in the modified database.

journal_table_name

Name of the default journal table. The journal_table_name parameter is required if this clause is specified without the DROP keyword.

database_name

If a database name is not specified, then the database being modified is assumed.

If the database being modified does not have a journal table, the system creates journal_table_name by default.

If you specify a different database, it must exist and journal_able_name must have been defined as its default journal table.

DROP DEFAULT JOURNAL TABLE

The DROP keyword removes the default status of the journal table defined as the default for the database being modified. If the journal table resides in the database being modified, DROP also deletes the table from the system.

If active data tables use the journal table, trying to drop it is an error.

[NO] AUTODBA [WITH CHECK]

The AUTODBA option marks a database as having its owning objects eligible for automated tuning from system service. Adding the WITH CHECK sub-option requires end user approval of recommended tuning actions prior to applying them. The NO AUTODBA option explicitly a database's objects as ineligible for automated tuning.