HP 3000 Manuals

ALLBASE/SQL Reference Manual

REMOVE DBEFILE 

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

MPE/iX 5.5 Documentation