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