ALLBASE/SQL Reference Manual > Chapter 11 SQL Statements E - R

REMOVE DBEFILE

	MPE documents
	Complete PDF
	Table of Contents
	Index

	E0300 Edition 9 ♥
	E0399 Edition 8
	E0897 Edition 7

The REMOVE DBEFILE statement removes the name of the DBEFileSet that the DBEFile was associated with from SYSTEM.DBEFile.

Scope

ISQL or Application Programs

SQL Syntax


  REMOVE DBEFILE DBEFileName FROM DBEFILESET DBEFileSetName

Parameters

DBEFileName: is the name of the DBEFile to be removed. The DBEFile must be empty (contain no tables, long data, or indexes).
DBEFileSetName: is the name of the DBEFileSet with which the DBEFile is currently associated.

Description

You must have exclusive access to all tables associated with the DBEFileSet.
After you remove a DBEFile from a DBFileSet, you can drop the DBEFile or add it to another DBEFileSet.
Before a DBEFile can be removed from the SYSTEM DBEFileSet, other users' transactions must complete. Other users must wait until the transaction that is removing the DBEFile from SYSTEM has completed.
REMOVE DBEFILE also decreases the number of files associated with the DBEFileSet shown in the DBEFSNDBEFILES column of SYSTEM.DBEFileSet by one.

Authorization

You must have DBA authority to use this statement.

Example


   CREATE DBEFILE ThisDBEFile WITH PAGES = 4,
                  NAME = 'ThisFile', TYPE = TABLE

   CREATE DBEFILESET Miscellaneous

   ADD DBEFILE ThisDBEFile TO DBEFILESET Miscellaneous

The DBEFile is used to store rows of a new table. When the table needs an index, one is created as follows:


   CREATE DBEFILE ThatDBEFile WITH PAGES = 4,
                  NAME = 'ThatFile', TYPE = INDEX

   ADD DBEFILE ThatDBEFile to DBEFILESET Miscellaneous

When the index is subsequently dropped, its file space can be assigned to another DBEFileSet.


   REMOVE DBEFILE ThatDBEFile FROM DBEFILESET Miscellaneous

   ADD DBEFILE ThatDBEFile TO DBEFILESET SYSTEM

   ALTER DBEFILE ThisDBEFile SET TYPE = MIXED

Now you can use this DBEFile to store an index later if you need one. All rows are later deleted from the table, so you can reclaim file space.


   REMOVE DBEFILE ThisDBEFile FROM DBEFILESET Miscellaneous

   DROP DBEFILE ThisDBEFile

The DBEFileSet definition can now be dropped.


   DROP DBEFILESET Miscellaneous

RELEASE

REMOVE FROM GROUP