HP 3000 Manuals HP 3000 Manuals
CREATE DBEFILE
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.
Scope
ISQL or Application Programs
SQL Syntax
CREATE DBEFILE DBEFilename WITH PAGES = DBEFileSize, NAME = 'SystemFileName'
[, INCREMENT = DBEFileIncrSize[, MAXPAGES = DBEFileMaxSize]]
[ {TABLE}]
[,TYPE = {INDEX}] [,DEVICE = volume;]
[ {MIXED}]
Parameters
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 of the FileName and GroupName portion of a
file identifier. The DBEFile is created in the
group and account where the DBECon file resides,
unless the GroupName is specified in other than
the group on which the DBECon file resides. The
maximum length for SystemFileName is 17 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.
Volume identifies the volume where the DBEFile will
reside.
Description
* 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.
* Specifying a group name when you create a DBEFile allows you to
create the DBEFile on a volume set different from the DBECon file.
It also makes partial backup and recovery possible.
* Specifying a volume at creation time allows you to create DBEFile
on a particular volume.
* 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.
Authorization
You must have DBA authority to use this statement.
Example
Create a DBEFile on a specific volume with a different group name than
the DBECon resides on.
CREATE DBEFILE ThisDBEFile
WITH PAGES = 4, NAME = 'ThisFile.Othergrp',
TYPE = TABLE, DEVICE = member3;
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