|
|
The CREATE DBEFILE statement defines and creates a DBEFile and places a
row describing the file in SYSTEM.DBEFile. A DBEFile is a file that
stores tables, indexes, hash structures, and/or LONG data.
ISQL or Application Programs
CREATE DBEFILE DBEFileName WITH PAGES = DBEFileSize,
NAME = 'SystemFileName'
[,INCREMENT = DBEFileIncrSize [,MAXPAGES = DBEFileMaxSize]]
[,TYPE = {TABLE
INDEX
MIXED}]
- DBEFileName
is the logical name to be assigned to the new DBEFile. Two DBEFiles in
one DBEnvironment cannot have the same logical name.
- DBEFileSize
specifies the number of 4096-byte pages in the new DBEFile. The
minimum DBEFile size is 2 pages. The maximum DBEFile size is 524,287
pages.
- SystemFileName
identifies how the DBEFile is known to the operating system. The
system file name is in the format
[Pathname/]FileName. The DBEFile is
created relative to the directory where the DBECon file resides unless an
absolute path name is specified. The maximum length for
SystemFileName is 44 bytes.
- DBEFileIncrSize
is a number you must supply with the INCREMENT clause when you want to
expand the DBEFILE. The DBEFileIncrSize should be 8 pages
or greater but it cannot exceed 65,535. No system default is provided by
ALLBASE/SQL; if this number is omitted, no DBEFile expansion takes
place.
- DBEFileMaxSize
is a number that you can supply with the MAXPAGES clause if you have
already specified a DBEFileIncrSize. If the
DBEFileMaxSize is not a multiple of
DBEFileIncrSize, the number may be rounded up or down as
follows: The smallest higher multiple is tried first. If the smallest
higher multiple is not a valid size, the largest lower multiple is used.
A warning message is returned to let you know that the
DBEFileMaxSize is rounded based on the
DBEFileIncrSize provided. If you omit the MAXPAGES
clause, the value defaults to the ALLBASE/SQL DBEFile maximum
size.
- TYPE =
specifies the setting of the DBEFile's TYPE attribute. The following
are valid settings:
- TABLE
Only data pages (table, HASH, or LONG) can be stored in the
DBEFile.
- INDEX
Only index pages can be stored in the DBEFile.
- MIXED
A mixture of data and index pages can be stored in the
DBEFile.
You use this statement to create all DBEFiles except DBEFile0,
which is created when a START DBE NEW statement is
processed.
The CREATE DBEFILE statement formats the DBEFile. The name
and characteristics of the DBEFile are stored in the system
catalog.
The DBEFile created is owned by hpdb and has the
following permissions:
-rw-----
To use a DBEFile for storing a table, LONG data, and/or an index,
you add it to a DBEFileSet with the ADD DBEFILE statement,
then reference the name of the DBEFileSet in the CREATE TABLE
statement. You may add a DBEFile to the SYSTEM DBEFileSet.
To delete the row describing a DBEFile from SYSTEM.DBEFile, use
the DROP DBEFILE statement.
INCREMENT and MAXPAGES are optional clauses. If they are omitted,
no DBEFile expansion takes place.
It is highly recommended that you provide the
DBEFileMaxSize along with the
DBEFileIncrSize. Not specifying the
DBEFileMaxSize causes it to be set to the system
maximum. This results in a high value for the ratio for this file.
The DBEFileMaxSize is stored internally as an integer
multiple of the DBEFileIncrSize; if the
DBEFileMaxSize is not a multiple of
DBEFileIncrSize, rounding can occur. Refer to the
description of DBEFileMaxSize in the previous section
for information on the rounding process.
The DBEFileMaxSize, after rounding, should be
equal to or greater than the DBEFileSize. It should
not exceed the maximum DBEFile size of 524,287 pages.
The optimal DBEFileIncrSize depends on the
expected rate of expansion for the file. Refer to the section
"Calculating Storage for Database Objects" in the ALLBASE/SQL
Database Administration Guide for information about estimating
size requirements for tables and indexes.
Expandable DBEFiles do not expand dynamically during the creation
of hash tables.
DBEFiles that contain hash tables are not expanded even though
they were specified as expandable when created.
You must have DBA authority to use this statement. hpdb must have
write permission in the directory where the DBEFile will reside.
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 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
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\
WITH PAGES = 4, NAME = 'ThatFile', TYPE = INDEX
ADD DBEFILE NewDBEFile TO DBEFILESET SYSTEM
|