Create a Spool Reserve Database | Teradata Vantage - Creating a Spool Reserve Database - Advanced SQL Engine - Teradata Database

Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
upb1600054424724.ditamap
dita:ditavalPath
upb1600054424724.ditaval
dita:id
B035-1093
lifecycle
previous
Product Category
Teradata Vantage™

Teradata strongly recommends that you give sufficient space under user DBC to be used as spool. There are two ways to do this: set aside permanent space to remain unused or create an empty database to hold the space. The advantage of allocating space under DBC to be used for spool is that the space will also be available for other system uses. However, there is also a risk that you may forget to maintain this reserved space and inadvertently assign it to other users and databases for PERM space usage. This could then result in a physical out-of-spool condition if there is no spool space left when processing queries.

An alternative is to create a database under DBC and allocate permanent space to be unused to hold the reserved spool space. Assigning permanent space to DBC to be permanently unused is reserving this physical space for spool usage. The advantage of creating a database to hold spool space means that space will always be set aside and available for spool usage.

A specific example of why it is important to reserve space for DBC is the use of spool space by system tables. Spool limits are allocated for each user or profile and actual space must be available for spool usage when a query is being processed. Sometimes, however, system tables like Transient Journal are allowed to use more space than is available in DBC. When DBC tries to use more space than it has, the system prevents new logons and refuses any data definition language SQL statements. This happens because logons and DDL statements require the system to add additional rows to DBC tables.

To avoid this situation, reserve plenty of space in DBC. This is even more necessary if you are using access logging or database query logging because the tables produced can use up a lot of space. Monitor the space usage for the logging functions often and regularly.

Be sure to not only allocate enough space to DBC but also to regularly clean up old tables by deleting rows from them or archiving them instead if you need the data for later.

To create a spool reserve database, submit a CREATE DATABASE statement and specify the amount of space you want to keep in reserve as the PERM parameter.

Do not create objects or store data in this database. As long as the reserve database remains empty, its PERM allocation remains available for use as spool space.

For example, assume you created an administrative user named DBADMIN. Since the space of this user is under your control, you can use it to create a child database named Spool_Reserve that never contains tables, as follows:

  1. Log on to the database as user DBC.
  2. Submit the following statement:
    CREATE DATABASE Spool_Reserve FROM DBADMIN AS PERM =  n ;

    where n is the number of bytes that is a specific percentage you have decided is appropriate.

  3. Quit the session and log off.

If your system is using global space accounting, define the spool reserve database with zero skew.

If your system allows a database or user to exceed space limits (the GlobalSpaceSoftLimitPercent field in the DBS Control utility is set to a non-zero value), a DBA must ensure that the system keeps this extra space available. When creating the spool reserve database, a DBA should factor in the soft limit percent and set the PERM limit for the spool reserve database higher accordingly. This way, when users or databases exceed their space limits, out-of-physical-space errors are less likely to occur.

For more information on determining the allocation of spool space, see “Sizing Spool Space” in Teradata Vantage™ - Database Design, B035-1094. For more information on determining perm space requirements, see “Calculating Total PERM Space Requirements” in Teradata Vantage™ - Database Design, B035-1094. For more information on global soft limits, the SKEW option, and global space accounting, see About Global Space Accounting.