ALLBASE/SQL Reference Manual > Chapter 10 SQL Statements A - D

CREATE DBEFILESET

	MPE documents
	Complete PDF
	Table of Contents
	Index

	E0300 Edition 9 ♥
	E0399 Edition 8
	E0897 Edition 7

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

CREATE DBEFILE

CREATE GROUP