HP 3000 Manuals

ALLBASE/SQL Reference Manual

CREATE DBEFILESET 

The CREATE DBEFILESET statement defines a DBEFileSet.  A DBEFileSet is a
group of related DBEFiles; as such, it serves as a mechanism for
allocating and deallocating file space for tables.

Scope 

ISQL or Application Programs

SQL Syntax 

CREATE DBEFILESET DBEFileSetName 

Parameters 

DBEFileSetName          specifies the name to be given to the new
                        DBEFileSet.  Two DBEFileSets in the same
                        DBEnvironment cannot have the same name.

Description 

   *   The CREATE DBEFILESET statement records the new DBEFileSet name in
       the system catalog with an indication that no physical storage is
       associated with the DBEFileSet.

   *   You associate physical storage with a DBEFileSet by associating
       DBEFiles with the DBEFileSet, using the ADD DBEFILE statement.
       Then you can associate a table and its indexes with the DBEFileSet
       by using the CREATE TABLE statement.  ALLBASE/SQL allocates all
       data and index pages for a table to DBEFiles in the DBEFileSet
       named in the IN clause of the CREATE TABLE statement.  If
       automatic DBEFile expansion is not being used when you need more
       space for a table, you add another DBEFile to the DBEFileSet
       associated with the table when the CREATE TABLE statement was
       issued.

   *   To remove a DBEFile from a DBEFileSet, you use the REMOVE DBEFILE
       statement.

   *   If a LONG column uses the IN DBEFileSet clause, ALLBASE/SQL
       allocates all LONG data pages for that column in DBEFiles in the
       DBEFileSet specified.  If automatic DBEFile expansion is not being
       used when more space is needed for the LONG column, you add
       another DBEFile to the DBEFileSet associated with the LONG column
       when the column was defined.

   *   To delete the definition of a DBEFileSet, use the DROP DBEFILESET
       statement.

   *   One DBEFileSet is created automatically when the START DBE NEW
       statement is issued--the SYSTEM DBEFileSet.  The system catalog
       resides in the SYSTEM DBEFileSet.  Those parts of the system
       catalog that are needed to start up a DBEnvironment reside in
       DBEFile0.  You may add a DBEFile to the SYSTEM DBEFileSet.

Authorization 

You must have DBA authority to use this statement.

Example 

The DBEFile is used to store rows of a new table.

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

      CREATE DBEFILESET Miscellaneous 

     ADD DBEFILE ThisDBEFile TO DBEFILESET Miscellaneous

When the table needs a DBEFile to hold 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 SomethingElse

     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

     CREATE DBEFILE NewDBEFile

       ADD DBEFILE NewDBEFile
     TO DBEFILESET SYSTEM

MPE/iX 5.5 Documentation