SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX File Attributes

HP NonStop SQL/MX Reference Manual—540440-003

8-2

ALLOCATE/DEALLOCATE

ALLOCATE is a Guardian file attribute that reserves disk space for a file.

DEALLOCATE frees disk space previously reserved for the file that does not contain

data. ALLOCATE applies to tables and indexes. Allocate disk space in advance to

ensure that space is available when needed and to avoid processing errors caused by

full or fragmented disks during normal allocation-on-demand.

You set the ALLOCATE attribute for a table with CREATE TABLE or ALTER TABLE.

You set the attribute for an index with CREATE INDEX or ALTER INDEX. You set the

DEALLOCATE attribute with ALTER TABLE or ALTER INDEX.

The default is ALLOCATE 0. You cannot allocate extents during table or index creation

unless you specify the ALLOCATE attribute.

ALLOCATE num-extents

is the number of extents to allocate in advance. The number must be an integer

between 0 and the current value of the MAXEXTENTS file attribute. If the object

contains partitions, the number of extents to allocate cannot be greater than the

MAXEXTENTS partition attribute for any of the partitions.

Depending on your file configuration, you might not be able to allocate the full

number of MAXEXTENTS. For ALTER TABLE or ALTER INDEX, ALLOCATE

allocates new extents until the total of new and existing extents equals the

specified number.

DEALLOCATE

frees all unused allocated extents (that is, all allocated extents beyond the extent

that contains the end-of-file).

Considerations for ALLOCATE

ALLOCATE and DEALLOCATE apply to all partitions of the specified file.

ALLOCATE affects the number of extents, but not the size of the extents. The EXTENT

file attribute determines the extent size.

If the number of extents to allocate is less than or equal to the current number of

extents allocated, the ALLOCATE operation does nothing. To decrease the number of

extents allocated, you must perform a DEALLOCATE operation to deallocate any

unused extents, followed by an ALLOCATE operation to allocate the desired number of

extents.

ALLOCATE num-extents | DEALLOCATE