16.10 - About Setting Free Space Percent Limits - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

You can control the Free Space Percent (FSP) at the global and table level as described below.

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 npercent of cylinder space to remain free on each cylinder when bulk loading this table (where nis 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 Utilities 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 Utilities.

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 SQL Data Definition Language.

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 Utilities.

For more information on the DBS Control utility, see Utilities

For information on the CREATE TABLE or ALTER TABLE statements, see SQL Data Definition Language.