Setting Free Space Percent Limits - Analytics Database - Teradata Vantage

Database Administration

Analytics Database
Teradata Vantage
Release Number
June 2022
English (United States)
Last Update
Product Category
Teradata Vantage™

You can control the Free Space Percent (FSP) at the global and table level using the following parameters.

Parameter Description
FreeSpacePercent field of DBS Control Global parameter that the system uses to determine the percentage of space to leave unused on each cylinder during bulk loading operations such as MultiLoad and FastLoad. This default can be overridden at the table level with the FREESPACE clause.

Choose a percentage that reflects the net growth rate of your data tables (INSERTs minus DELETEs). For example, most sites choose 5 to 15%.

FREESPACE = n of a CREATE/ALTER TABLE statement n percent of cylinder space to remain free on each cylinder when bulk loading this table (where n is an integer constant).
Do not use the Ferret utility PACKDISK command to change this value once you have created a table or have modified its FREESPACE PERCENT with an ALTER TABLE request.

Instead, submit an ALTER TABLE request to change the free space percent value for the table and then immediately afterward submit a PACKDISK command which will pick up the new free space percent value (see the documentation for the Ferret utility in Teradata Vantage™ - Database Utilities, B035-1102 for more information and instructions for running PACKDISK).

DEFAULT FREESPACE of an ALTER TABLE statement Reset the current free space percent for a table at the global or table level. DEFAULT FREESPACE resets Free Space to the value defined in the FreeSpacePercent field in DBS Control.
FREESPACEPERCENT option in PACKDISK Optionally specifies the percentage of cylinder space to leave free. This allows for future table growth without requiring the allocation of new free cylinders.

FSP can be specified for individual tables by using the FREESPACE option to CREATE TABLE and ALTER TABLE.

The FREESPACE value defined for a table with CREATE TABLE takes precedence over the FREESPACEPERCENT value with PACKDISK, unless PACKDISK includes the FORCE option. In that case, PACKDISK will pack to the specified FREESPACEPERCENT, but after that, the AutoCylPack job running in the background will eventually repack the table cylinders to the FREESPACE value that was specified in CREATE TABLE. For information on the FORCE and PACKDISK commands, see the Ferret Utility in Teradata Vantage™ - Database Utilities, B035-1102.

Use the FREESPACE option of ALTER TABLE to add or modify an FSP value for an existing table. For more information on the FREESPACE option, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

Because the AutoCylPack (automatic background cylinder packing) feature monitors and maintains proper levels of cylinder free space using AutoCylPackFSP, you only need to use the FREESPACEPERCENT option in PACKDISK in exceptional situations. For a list of these exceptions, see “Ferret Utility (ferret)” in Teradata Vantage™ - Database Utilities, B035-1102.

For more information on the DBS Control utility, see Teradata Vantage™ - Database Utilities, B035-1102.

For information on the CREATE TABLE or ALTER TABLE statements, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.