HP 3000 Manuals HP 3000 Manuals
Creating DBEFileSets and DBEFiles
As discussed in the "Physical Design" chapter, DBEFileSets and DBEFiles
are the key elements of ALLBASE/SQL storage. A DBEFile is an MPE file
that contains table data, or index data, or both. A DBEFileSet is a
logical group of one or more DBEFiles. The amount of storage available
in a DBEFileSet can be increased by adding DBEFiles.
When a DBEnvironment is configured, ALLBASE/SQL creates the SYSTEM
DBEFileSet containing DBEFile0. DBEFile0 contains all the table, view,
and index data for the system catalog, as well as all stored sections for
preprocessed programs. If the SYSTEM DBEFileSet runs out of space,
create another DBEFile and add it to the SYSTEM DBEFileSet before you
create anything else. If you never create any new DBEFileSets and
DBEFiles, all tables and indexes created in the DBEnvironment are placed
in the SYSTEM DBEFileSet. As discussed in the "Physical Design" chapter,
separate DBEFileSets should be created for groups of tables that are
maintained together.
Creating DBEFileSets
You must have DBA authority to create a DBEFileSet. Use the following
syntax:
CREATE DBEFILESET DBEFileSetName
The name you specify is stored in the system catalog, as shown in the
view SYSTEM.DBEFILESET. Two DBEFileSets in the same DBEnvironment cannot
have the same name.
A DBEFileSet is a logical construct. There is no physical space
associated with it until you create DBEFiles and add them to the
DBEFileSet.
The statements to create the three DBEFileSets for the PurchDB database
are as follows:
isql=> CREATE DBEFILESET PurchFS;
isql=> CREATE DBEFILESET WarehFS;
isql=> CREATE DBEFILESET OrderFS;
The DBEFileSet names are stored as character strings in
SYSTEM.DBEFILESET. Names are all upshifted unless you enclose them in
double quotes.
To look at the DBEFileSets that were created,
examine the Static DBEFile screen in SQLMON, or query the system catalog
as follows:
isql=> SELECT DBEFSNAME, DBEFSNDBEFILES,
> DBEFSNPAGES, DBEFSUPAGES FROM System.DBEFileSet;
--------------------+--------------+-----------+-----------+
DBEFSNAME |DBEFSNDBEFILES|DBEFSNPAGES|DBEFSUPAGES|
--------------------+--------------+-----------+-----------+
SYSTEM | 1| 150| 0|
PURCHFS | 2| 100| 2|
WAREHFS | 2| 100| 2|
ORDERFS | 2| 100| 2|
RECFS | 1| 50| 3|
------------------------------------------------------------------------
Number of rows selected is 5
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]>
DBEFileSet names follow the rules of ALLBASE/SQL basic names; refer to
the SQL Reference Manual for ALLBASE/SQL naming conventions.
Assigning Default DBEFileSets
ALLBASE/SQL system catalog tables are stored in the SYSTEM DBEFileSet.
When a new DBEnvironment is created (with the START DBE NEW statement),
the SYSTEM DBEFileSet is the default DBEFileSet for storing all user
tables and sections. When user data is stored in the SYSTEM DBEFileSet,
system performance is impacted.
One way of insuring that user sections and data are stored in a
DBEFileSet other than SYSTEM is to assign a default DBEFileSet. Then,
when tables and long column data are created without being assigned a
specific DBEFileSet, they are stored in the default TABLESPACE DBEFileSet
you have assigned. And when sections are created without being assigned
a specific DBEFileSet, they are stored in the default SECTIONSPACE
DBEFileSet. For example:
SET DEFAULT SECTIONSPACE TO DBEFILESET SectionDBESet FOR PUBLIC;
SET DEFAULT TABLESPACE TO DBEFILESET TableDBESet FOR PUBLIC;
Complete syntax for the SET DEFAULT DBEFILESET statement is found in the
ALLBASE/SQL Reference Manual.
Creating DBEFiles
You must have DBA authority to create DBEFiles. Use the following
syntax:
CREATE DBEFILE DBEFilename WITH PAGES = DBEFileSize, NAME = 'SystemFileName'
[, INCREMENT = DBEFileIncrSize[, MAXPAGES = DBEFileMaxSize]]
[ {TABLE}]
[,TYPE = {INDEX}]
[ {MIXED}]
All DBEFiles must be created in the group and account where the DBECon
file resides. DBEFileName is the name by which a DBEFile is known to
ALLBASE/SQL. It is stored in the system catalog, as shown in the view
SYSTEM.DBEFILE. No two DBEFiles can have the same DBEFileName.
DBEFileNames follow the rules of ALLBASE/SQL basic names. DBEFileNames
are stored as character strings; they are upshifted unless you enclose
them in double quotes.
PAGES is the number of 4096-byte pages that will be preallocated to the
DBEFile. A number from 2 to 524,287 must be specified. The number of
pages you assign to the DBEFile depends on how much data you plan to
store in it. Use your calculations from "Physical Design" to determine
how many pages you should assign to a DBEFile. Remember that a DBEFile
is an MPE file, so its size is limited by the system resources available.
SystemFileName is the name of the DBEFile as it appears in MPE/iX. It
must follow MPE/iX naming conventions. If you use the LISTF command to
view the files on your system, you can see the system file name specified
in the CREATE DBEFILE statement for all DBEFiles you have created.
Type TABLE allows only table data; type INDEX allows only index data.
The default type is MIXED, which allows both table and index data. The
sample database DBEFiles are created as either TABLE or INDEX so that the
tables and indexes can be manipulated independently of each other. As
discussed in the "Physical Design" chapter, locating index data and table
data on separate devices decreases the work load for the disk drives and
increases performance.
The following examples show how to create two DBEFiles for the PurchFS
DBEFileSet:
isql=> CREATE DBEFILE PurchDataF1
> WITH PAGES = 50,
> NAME = 'PurchDF1',
> TYPE = TABLE;
isql=> CREATE DBEFILE PurchIndxF1
> WITH PAGES = 50,
> NAME = 'PurchXF1',
> TYPE = INDEX;
Similarly, the following examples show how to create DBEFiles for the
DBEFileSets WarehFS and OrderFS respectively:
isql=> CREATE DBEFILE WarehDataF1
> WITH PAGES = 50,
> NAME = 'WarehDF1',
> TYPE = TABLE;
isql=> CREATE DBEFILE WarehIndxF1
> WITH PAGES = 50,
> NAME = 'WarehXF1',
> TYPE = INDEX;
isql=> CREATE DBEFILE OrderDataF1
> WITH PAGES = 50,
> NAME = 'OrderDF1',
> TYPE = TABLE;
isql=> CREATE DBEFILE OrderIndxF1
> WITH PAGES = 50,
> NAME = 'OrderXF1',
> TYPE = INDEX;
The characteristics of the DBEFiles are stored in the system catalog, as
shown in the view SYSTEM.DBEFILE. You can perform a SELECT on
SYSTEM.DBEFILE to see the DBEFiles you have created along with DBEFile0
created at DBEnvironment configuration time. Note that although you
entered the DBEFile names in upper and lower case, they are recorded in
the system catalog as all upper case letters unless you enclose them in
double quotes.
Adding DBEFiles to DBEFileSets
Adding DBEFiles to a DBEFileSet allocates storage space in which table
and index data can be stored. Although you can create tables in an empty
DBEFileSet, you cannot create indexes in a DBEFileSet until a DBEFile has
been added to the DBEFileSet. Creating an index causes a root page to be
created, whereas creating a table does not cause any physical page to be
created.
You must have DBA authority to add DBEFiles to DBEFileSets. Use the
following statement syntax:
ADD DBEFILE DBEFileName TO DBEFILESET DBEFileSetName
Both the DBEFile and the DBEFileSet must already exist in the
DBEnvironment.
You can add DBEFiles to the SYSTEM DBEFileSet using the following syntax:
ADD DBEFILE DBEFileName TO DBEFILESET SYSTEM
To add a file to the SYSTEM DBEFileSet, your transaction must be the only
active transaction in the system. If other users are active, your
transaction will wait until they complete.
The PurchDB database has three DBEFileSets, each of which contains two
DBEFiles. The following series of statements adds the DBEFiles to their
respective DBEFileSets and allocates storage for the sample database:
isql=> ADD DBEFILE PurchDataF1 TO DBEFILESET PurchFS;
isql=> ADD DBEFILE PurchIndxF1 TO DBEFILESET PurchFS;
isql=> ADD DBEFILE WarehDataF1 TO DBEFILESET WarehFS;
isql=> ADD DBEFILE WarehIndxF1 TO DBEFILESET WarehFS;
isql=> ADD DBEFILE OrderDataF1 TO DBEFILESET OrderFS;
isql=> ADD DBEFILE OrderIndxF1 TO DBEFILESET OrderFS;
Now query the SYSTEM.DBEFILE view to see which DBEFiles are associated
with which DBEFileSets:
isql=> SELECT DBEFName,DBEFSName FROM SYSTEM.DBEFILE;
The query result is as follows:
SELECT DBEFName,DBEFSName FROM System.DBEFile;
--------------------+--------------------
DBEFNAME |DBEFSNAME
--------------------+--------------------
PARTSDBE0 |SYSTEM
PURCHDATAF1 |PURCHFS
PURCHINDXF1 |PURCHFS
WAREHDATAF1 |WAREHS
WAREHINDXF1 |WAREHS
ORDERDATAF1 |ORDERFS
ORDERINDXF1 |ORDERFS
------------------------------------------------------------------------
Number of rows selected is 7
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]>
DBEFiles should be created and added to DBEFileSets as they are needed so
that you do not have empty DBEFiles wasting disk space.
Allocating Expandable DBEFile Space
As an alternative to creating fixed-length DBEFiles, you can create
DBEFiles which are expandable, by specifying a maximum DBEFile size and
an expansion increment. This approach is known as dynamic DBEFile
expansion, which you can employ by using the optional INCREMENT and
MAXPAGES clauses of the CREATE DBEFILE statement. The procedure is as
follows:
1. Create expandable DBEFiles of the appropriate type (TABLE, INDEX,
or MIXED) using the CREATE DBEFILE statement. Specify an
expansion increment and a maximum file size. For example:
isql=> CREATE DBEFILE DBEFile1 WITH PAGES=100,
> NAME='DBEFile1', INCREMENT=100, MAXPAGES=5000, TYPE=TABLE;
2. Use the ADD DBEFile statement to add each DBEFile to a previously
created DBEFileSet.
By using this method, you do not have to add DBEFiles to the DBEFileSet
to increase its capacity until the space has expanded to the maximum file
size specified. If a DBEFile runs out of initial capacity during
execution of an INSERT or UPDATE statement, an additional increment of
space is added to the file. However, when expandable DBEFiles are in
use, the DBA should still monitor the size of DBEFiles to determine the
following:
* How close the file's size is to the maximum.
* Whether the file has grown unexpectedly large following a
temporary condition in the database.
Note If a transaction that causes expansion of a DBEFile were to abort,
the DBEFile expansion is rolled back logically and not physically.
In this event, the size of the DBEFile as shown in the DBEFNPAGES
column of the SYSTEM.DBEFile view can be smaller than the actual
file size shown on the operating system. The discrepancy
disappears the next time a successful expansion takes place.
Allocating Expandable DBEFile Space in the SYSTEM DBEFileSet
The SYSTEM DBEFileSet contains the system catalog and its indexes,
together with space used by temporary tables during query processing.
When you create a DBEnvironment using the START DBE NEW statement,
ALLBASE/SQL creates a fixed-length file called DBEFILE0 and adds it to
the SYSTEM DBEFileSet. You specify the size of the file in the START DBE
NEW statement. Because DBEFile0 is of fixed length, it is not
expandable. Therefore, in order to provide dynamic space expansion for
the system catalog or for sort operations during query processing, you
must create one or more additional expandable DBEFiles and add them to
the SYSTEM DBEFileSet. You cannot create an expandable DBEFile using the
START DBE NEW statement.[REV BEG]
CAUTION An expandable DBEFile cannot be compressed again once expanded.
Also a DBEFile cannot easily be removed from the SYSTEM
DBEFileSet once added because it could be used by the system
catalog. Large sorted queries can easily require large amounts
of sort space from the SYSTEM DBEFileSet causing expandable
SYSTEM DBEFiles to expand to their maximum size. These DBEFiles
cannot be reduced or dropped once expanded. Therefore, care
should be taken to limit the maximum size of expandable SYSTEM
DBEFiles.
[REV END]
Partial DBEFile Expansion
Partial expansion of files can occur if the following conditions exist:
* The current size of a DBEFile is not a multiple of
DBEFileIncrSize. In this case, the number of pages by which the
DBEFile is expanded is determined as follows:
* If the number of pages needed to bring the size of the
DBEFile to a multiple of its increment is less than or
equal to half the increment size, the DBEFile is expanded
by that many pages plus those specified in the increment
size.
* Otherwise, the DBEFile is expanded by the number of pages
needed to make its size a multiple of its increment.
* If the file system gets full during expansion, the DBEFile remains
partially expanded.
Obtaining Information about Expandable DBEFiles
The following query displays information about expandable DBEFiles as it
appears in the SYSTEM.DBEFile view:
isql=> SELECT DBEFNAME, DBEFTYPE, FILEID,
> DBEFNPAGES, DBEFUPAGES, DBEFINCRSZ, DBEFIPAGES,
> DBEFMPAGES, CTIME, DBEFSNAME
> FROM SYSTEM.DBEFILE;