Allocating Space for External Stored Procedure and User-Defined Function Bodies
The code for both stored procedures and user-defined functions is stored outside the database on system disk, or, in the case of UDFs, possibly on client disk, so you do not need to account for it when you undertake database capacity planning.
Allocating PERM, TEMP, and Spool Space
Use the CREATE USER statement to allocate all the PERM space required by the applications that support your enterprise to user SysAdmin. To determine these space requirements, use the equations provided in Calculating Total PERM Space Requirements.
The quantity of space specified is allocated from the current PERM space of user DBC. DBC becomes the owner of the SysAdmin user and of all users and databases subsequently created. Be sure to leave enough space in DBC to accommodate the growth of system tables and logs and the WAL log.
Some guidelines for estimating temporary spool and WAL log space requirements are explained in the following subsections.
Note that the containing database or user of a global temporary table must also have a minimum of 512 bytes of PERM space free for the GTT table header.
Estimating Administrative Spool Space Requirements
As with PERM space, spool space is allocated from the available space of the owning user. The SPOOL specification in the CREATE request is only a maximum limit.
During query processing, spool space is dynamically allocated from any free space, including the free PERM space of the user who submitted the query.
- Reserve 25% to 35% of total space for spool space and spool growth buffer.
When you create user SysAdmin, you can leave the SPOOL parameter unspecified, so it defaults to the maximum allocation of the owning user, user DBC.
- Allow an extra 5% of PERM space in user DBC.
- Each time a new user or database is created, you can specify the maximum amount of spool space that a query submitted by that user can consume.
Details and formulas for estimating user spool space requirements are provided in Tables Area: User Spool Space Requirements, Rules for Using the Spool Space Equations , Field Mode Spool Space Sizing Equation, and Record and Indicator Mode Spool Space Sizing Equation.
Estimating Administrative TEMP Space Requirements
Allocate the TEMP space required by the applications that support your enterprise to user SysAdmin.
Like PERM space, TEMP space is allocated from the available space of the owning user. Make an estimate of temporary space requirements.
The TEMPORARY=n clause in the CREATE DATABASE and CREATE USER statements permits you to define how many bytes are to be allocated to a database or user for creating global temporary tables. Note that temporary space is reserved prior to spool space for any user defined to have this attribute.
Disk usage for materialized global temporary tables is charged to the temporary space allocation of the user who referenced the table.
Global temporary tables also require a minimum of 512 bytes from the PERM space of the containing database or user. This space is used for the GTT table header.
If no temporary space is defined for a user, then the space allocated for any global temporary tables referenced by that user is set to the maximum temporary space allocated for the immediate owner.
Estimating WAL Log Space Requirements
Vantage creates and manages a WAL log that includes the transient journal to store the before-change image of every data row involved in every SQL transaction. This log is used to recover data tables when transactions are aborted.
As transactions are processed, the WAL log grows and shrinks. While transactions are in progress, the WAL log grows according to the total number of data rows being updated or deleted.
The applicable journal rows are purged at intervals (but not before the transaction is completed or, if it was aborted, not before the affected rows are recovered).
Space for the WAL log is acquired from the current PERM space of user DBC. Therefore, it is important that you leave enough PERM space in DBC to accommodate the growth of the largest foreseeable WAL log.
To estimate the maximum size of the WAL log, follow this procedure:
- Determine the length of the longest row in your production database. Use this figure as your maximum row length.
- Multiply the maximum row length by the total number of rows in your application programs, batch jobs, and ad-hoc queries that users are likely to update and delete in concurrent transactions.
- Double the resulting value.
This is the space needed for the WAL log on your system.
WAL Log Data Block Size
When you know the maximum row length, double the value to calculate the size of your multirow data block definition in the JournalDBSize parameter of the DBS Control Record. For example, the JournalDBSize value should be greater or equal to twice the maximum row length.
The file system allocates any row that exceeds the current size of a multirow data block to a WAL log data block of its own.
Depending on the configuration of your system, the database builds its data blocks using either native 512 byte sectors or native 4KB sectors. Systems built on 512 byte sectors are referred to as unaligned configurations, while systems built on 4KB sectors are referred to as aligned configurations.
For details on the JournalDBSize parameter, see the DBS Control utility section in Teradata Vantage™ - Database Utilities, B035-1102.