17.10 - Creating the Spool Space Reserve - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Advanced SQL Engine Security Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Administration
Security
Publication ID
B035-1100-171K
Language
English (United States)

You should create a spool space reserve from the space available to user DBC, to ensure that there is adequate space to execute queries.

System-wide use of spool can exceed the spool reserve, if necessary, as long as there is other uncommitted DBC perm space.

The following procedure sets the spool space reserve at 20% of total available DBC space, which you may need to adjust later, depending on the number of simultaneous queries and the size of the tables in the database.

  1. Determine the perm space owned by user DBC, for example:
    SELECT SUM(MAXPERM) FROM DBC.DISKSPACE WHERE DataBaseName = 'dbc';
  2. Calculate a value in bytes equal to 20% of the DBC space (the figure obtained in step 1) to set aside as spool space reserve, for example:

    (6347.75 GB) *(.20) = 1269.55 GB

  3. Create the Spool Reserve database and allocate perm space according the figure you calculated, for example:
    CREATE DATABASE Spool_Reserve FROM DBC
    AS PERM = 1269.55 GB
    NO FALLBACK
    NO BEFORE JOURNAL
    NO AFTER JOURNAL ;
    Specifying spool space in a CREATE USER or CREATE PROFILE statement defines the portion of the overall available spool that is available to users or profile members.

    For information on peak spool space utilization (Peak Spool), see Teradata Vantage™ - Data Dictionary, B035-1092.