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]]
  [, TYPE = {TABLE 
             INDEX 
             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 [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.

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.
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.

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