 |
» |
|
|
|
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]] [ ,TYPE = { TABLE INDEX MIXED } ] [,DEVICE = volume;] 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
|
|
