ALLBASE/SQL Reference Manual (36216-90216)

Chapter 10 329

SQL Statements A - D

CREATE DBEFILE

• DBEFiles that contain hash tables are not expanded even though they were speciﬁed as

expandable when created.

Authorization

You must have DBA authority to use this statement. hpdb must have write permission in

the directory where the DBEFile will reside.

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, a

DBEFile is created to store an index:

CREATE DBEFILE ThatDBEFile\

WITH PAGES = 4, NAME = 'ThatFile', TYPE = INDEX

ADD DBEFILE ThatDBEFile TO DBEFILESET Miscellaneous

When the index is subsequently dropped, its ﬁle 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

All rows are later deleted from the table, so you can reclaim ﬁle space.

REMOVE DBEFILE ThisDBEFile FROM DBEFILESET Miscellaneous

DROP DBEFILE ThisDBEFile

The DBEFileSet deﬁnition can now be dropped.

DROP DBEFILESET Miscellaneous

CREATE DBEFILE NewDBEFile\

WITH PAGES = 4, NAME = 'ThatFile', TYPE = INDEX

ADD DBEFILE NewDBEFile TO DBEFILESET SYSTEM