|
   
6.
Storage Architecture
 6.1
Storage Architecture
6.2
Files
6.3
Managing Tablespaces and Files
6. Storage Architecture
This chapter
introduces the storage architecture of DBMaker. The storage architecture
of DBMaker includes the logical level and the physical level.
The logical
level is the view that is presented to users and organizes the data
in the database in a way which is easy to understand. The physical level
consists of operating system files which correspond to information in
the tablespaces, but which are managed by DBMaker and hidden from the
user.
This chapter
also explains how you can control the storage allocation of a database
by using tablespaces and files.
6.1 Storage Architecture
A DBMaker
database is composed of one or more logical divisions known as tablespaces.
Tablespaces are the primary logical storage structure in DBMaker. In
the logical view, a tablespace contains one or more tables and indexes
(see Figure 6-1). In the physical view, a tablespace is the logical
storage that consists of one or more operating system files (see Figure
6-2).
Figure
6-1: DBMaker database storage components in the logical view
Figure
6-2: DBMaker database storage components in the physical view
6.2 Files
To increase
database performance, DBMaker places data into two different types of
files-data files and Binary Large Object (BLOB) files. BLOB data
consists of large data objects in the form of image, voice, or large
text which cannot be packed into a page. DBMaker stores the BLOB data
in BLOB files and stores the data rows and index keys in the data files.
In order to achieve high performance, DBMaker manages these two file
types in different ways.
Data Files
Data files
are comprised of pages, while BLOB files are comprised of frames. The
maximum size of both data and BLOB files is 2GB. However, there are
two major differences between frames and pages:
The size of a page is fixed at 4KB, but the size of a frame can be customized
by a user.
A page can contain more than one tuple, but a frame only contains a
single BLOB data item.
A data
page, 4096 bytes, is the smallest unit of storage used by data files.
The data page format is similar regardless of whether the data page
stores table or index data. A data page contains four sections: the
page header, row data, free space and the row directory.
Figure
6-3: Format of a data page
The page
header contains general page information for the DBMaker system. The
row data area contains the actual table or index data that is displayed
as rows and columns when looking in a table or index, and the row directory
contains information about the rows in the page. Free space is the available
space on that page that has not yet been used to store data.
BLOB Files
A BLOB
frame is the smallest unit of storage used by BLOB files. The size of
the BLOB frame can be specified in dmconfig.ini before creating the
database. The minimum frame size is 8KB and the maximum frame size is
256KB, except in the Windows 3.1 environment where the frame size is
fixed to 8KB. A BLOB frame contains three sections: the frame header,
BLOB data and free space. (For more detailed information about BLOB
files, refer to the chapter "Large Object Management".)
Figure
6-4: Format of a frame.
Like the
page header, the frame header contains general frame information for
the DBMaker system. The BLOB data area contains the BLOB data, and each
frame can only contain a single BLOB item. However, BLOB data that is
larger than the frame size can be spread over several frames. Free space
is the available space on that page that has not been used to store
BLOB data.
Journal Files
DBMaker's
journal is composed of several physical journal files. Every journal
file is a fixed-size file, and all journal files are the same size.
A journal file is composed of blocks, where each block is 4096 bytes.
Every action that causes a change in the database system will have a
journal record to record it. Journal records are the logical elements
in the journal, and several journal records may be packed in a journal
block or a single record may span several journal blocks. A journal
record that is owned by an active transaction cannot be reused. Logically
all the journal files form a ring of journal records. DBMaker automatically
switches to a new journal file when the current journal file fills.
When all journal files are filled by active transactions, the current
transaction will be aborted because no journal blocks are available
to use. This situation is called journal full. In addition to
journal records, a journal file contains some blocks to record the journal
status, called journal status blocks. These are used when recovering
or restoring the database. Recovery and restoration will be described
in later sections.
DBMaker
maintains journal block buffers in memory to speed up journal file access.
Before the actual modified data is written to disk, the journal record
is written to disk using the Write-Ahead-Log (WAL) protocol.
When the journal buffer is full or a transaction is committed, the journal
buffer will be flushed to journal files with the WAL protocol.
Specifying Journal Fields in dmconfig.ini
There are
several journal parameters that can be set to enhance database performance.
|
DB_JNFIL
- Specifies the names of journal files. You can specify one to
eight journal file names. Every journal file name is separated
by a comma or a space. For example:
DB_JNFIL=myDb.jn1, /usr/myDb.jn2, myDb.jn3, /home/myDb.jn4, myDb.jn5
|
In
this example, the database will have five journal files. As stated
above, you can specify journal files on different drives to enhance
performance.
|
|
DB_JNLSZ
- Specifies the size of a journal file as a multiple of journal
pages. (One journal page is 4096 bytes.) The total journal file
size is:
(no.
of journal files x journal file size) pages
You
decide on a reasonable size for journal files when creating your
database. As the previous section stated, when all journal files
are filled, the current transaction might be aborted because of
a full journal. Therefore, a small journal size may cause a long
transaction to be aborted by the system. If your database operations
involves long transactions, you should choose a larger journal
file size or more journal files.
|
|
DB_NJNLB
- Specifies the size of a journal buffer as a multiple of journal
pages. (One journal page is 4096 bytes.)
|
Tablespaces
A DBMaker
database can be partitioned logically into smaller logical areas of
space known as tablespaces. Tablespaces are logical areas of
storage that allow the database to be subdivided into manageable areas.
Each tablespace contains one or more files. Before you begin using tablespaces
and files in DBMaker, you should be familiar with the terms below.
Tablespace Types
Tablespaces
can be either fixed in size or automatically extensible. Tablespaces
that are fixed in size are called regular tablespaces, and tablespaces
that can have their size automatically extended are called autoextend
tablespaces. DBMaker also has a special tablespace called the system
tablespace.
The System Tablespace
All DBMaker
databases have at least one tablespace called the system tablespace.
Whenever you create a database, DBMaker generates a system tablespace
to record the system catalog tables. The system catalog tables
store information about the entire database. It is also possible to
store user tables in the system tablespace. However, it is more flexible
and efficient if you create additional tablespaces to store user tables
separate from the system catalog tables.
Regular Tablespaces
A regular
tablespace is a tablespace that has a fixed size and contains one or
more data files. If a file in a regular tablespace is too small to hold
all the data you wish to store in it, you can enlarge it manually. The
maximum number of files in a regular tablespace is 32767. The total
number of pages in all files in a tablespace must be equal to or less
than 2GB.
Autoextend Tablespaces
An autoextend
tablespace is a tablespace that will automatically grow as needed and
contains one data file and one BLOB file. Files in an autoextend tablespace
will expand automatically.
You can
change any autoextend tablespace to a regular tablespace if you don't
wish the tablespace to expand, but a regular tablespace cannot be changed
to an autoextend tablespace. However, you can add new files or enlarge
existing files to expand a regular tablespace. You should note that
raw device files can only be used with regular tablespaces, and cannot
be used with autoextend tablespaces.
DBMaker
automatically creates an autoextend tablespace called the system tablespace
when creating a database. However, if you create any other tablespaces,
these are created as regular tablespaces by default. If you don't wish
to allow the system tablespace to grow without limit, you can change
it to a regular tablespace.
The number
of pages in a data file, specified in dmconfig.ini, is the initial
size if that file belongs to an autoextend tablespace, and the real
size if that file belongs to a regular tablespace.
Expanding a Regular Tablespace
There are
two ways to expand a regular tablespace:
Add new files to a regular tablespace.
Add pages to existing files in a regular tablespace.
The following
example demonstrates how to add a new file to a regular tablespace.
In this example, an additional BLOB file named file_blob with
120 frames is added to the tablespace named app_ts:
dmSQL> ALTER TABLESPACE app_ts ADD DATAFILE file_blob TYPE = BLOB;
|
Before
you type the above command, you must tell DBMaker the name of the physical
file that corresponds to the logical file named file_blob. To
do this, you need to add the following statement to the dmconfig.ini
file in the section for that database.
file_blob = file.blb 120
In this
case file_blob is the logical name that will be used in the database,
and file.blb is the physical file name that is used by the operating
system.
To add
100 pages to an existing data file named file_data in a regular
tablespace named app_ts, you would enter the following:
dmSQL> ALTER DATAFILE file_data ADD 100 PAGES;
|
After altering
the size of the file by adding the extra pages, DBMaker will update
the number of pages for that file in dmconfig.ini file to the reflect
the new value.
Altering a Tablespace from Autoextend to Regular
You may
want to alter a tablespace from autoextend to regular when:
You want to add more data to an autoextend tablespace, but the tablespace
has already grown to fill all available space on the disk. You can convert
this to a regular tablespace and add additional files on another disk
to the tablespace.
You want to restrict the amount of space a tablespace will occupy. (An
autoextend tablespace can grow to fill all available space on a disk
to a maximum of 2GB.)
After altering
an autoextend tablespace to a regular tablespace, you can add files
to that tablespace to increase its size. You should note that DBMaker
does not allow a regular tablespace to be converted to an autoextend
tablespace.
6.3 Managing Tablespaces and Files
There are
numerous things to consider when managing tablespaces and files for
a database. For example you can: determine the size and type of a new
database at the time of its creation, create additional tablespaces,
alter autoextend tablespaces to regular tablespaces, add data files
to tablespaces, set and alter the size of files in tablespaces, and
drop data files and tablespaces when they are no longer required.
Each DBMaker
database has at least one tablespace called the system tablespace. When
you create a database, DBMaker generates five files: a system data file,
a user data file, a system BLOB file, a user BLOB file, and a journal
file. The system data file and the system BLOB file are placed in the
system tablespace. These two files are used to record the system catalog
tables for the entire database.
You can
also store user tables in the system tablespace. However, it is more
flexible and efficient if you create additional tablespaces to store
user tables separate from the system catalog tables.
Initial Setting of System Files
DBMaker
generates the system tablespace and the three system files (the system
data file, the system BLOB file, and the journal file) when creating
a new database. The names of those three files are made by taking the
database name and concatenating .SDB, .SBB and .JNL to the respective
files. If you don't specify their size, they will be created with the
default sizes of 600KB, 20KB and 4000KB respectively. If you want to
use different names for the system files, you can do so by specifying
them in the dmconfig.ini file.
To specify
the names of the system files in dmconfig.ini, you can add the parameters
shown below to the dmconfig.ini file:
[MY_DB] ;database name
DB_DBDIR = /disk1/usr ;database directory
DB_DBFIL = datafile.sdb ;data file
DB_BBFIL = blobfile.sbb ;BLOB file
DB_JNFIL = jrnlfile.jnl ;journal file
|
With these
values in the dmconfig.ini file, when you use the above CREATE DB command,
DBMaker creates the three system files as before, but this time it uses
the names provided above instead of the default names. In this case,
the system data file is named datafile.sdb, the system BLOB file
is named blobfile.bb and the journal file is named jrnlfile.jnl.
Since the
system tablespace is an autoextend tablespace, the size of the system
tablespace is just an initial size, not a limitation. If you want to
limit the disk space used by the system tablespace, you can change the
system tablespace to a regular tablespace by using the ALTER TABLESPACE
command. Once all of the space in a regular system tablespace is exhausted,
the only way to enlarge it is to add files to the regular system tablespace
or enlarge the system files by adding pages.
Creating Tablespaces
You can
also create additional tablespaces to contain other data and BLOB files.
By default DBMaker creates a new tablespace as a regular tablespace.
If you want to create a new tablespace as an autoextend tablespace,
you must specify the autoextend type at creation time.
A tablespace
must contain at least one data file, but additional files in the tablespace
can be either data files or BLOB files. DBMaker creates a new file as
a data file by default, so you have to specify the file type as BLOB
when you create it if you want a BLOB file.
Before
creating a new tablespace, you have to specify the size and filenames
of the data files associated with the tablespace in the dmconfig.ini
file.
The example
below shows the entries required in dmconfig.ini to specify three files
named f1, f2 and f3 with their operating system
filenames and page sizes.
[MY_DB] ;database name
f1 = /disk1/usr/f1.dat 1000 ;a data file with 1000 pages
f2 = /disk2/usr/f2.dat 500 ;a data file with 500 pages
f3 = /disk1/usr/f3.blb 1000 ;a blob file with 1000 pages
|
To create
a new tablespace that uses these files, you can enter the following
SQL command:
dmSQL> CREATE TABLESPACE ts1 DATAFILE f1, f2, f3 TYPE=BLOB;
|
This creates
a regular tablespace ts1 with two data files and one BLOB file,
with the data files placed on different disks.
The following
example creates an autoextend tablespace with one data file and one
BLOB file. The initial size of the data file is 500 pages, and the initial
size of the BLOB file is 20 pages. If the data file or BLOB file is
filled, it will expand automatically.
[MY_DB] ;database name
f4 = /usr/f4.dat 500 ;a data file with initial 500 pages
f5 = /usr/f5.blb 20 ;a blob file with initial 20 pages
|
To create
a new tablespace that uses these files, you can enter the following
SQL command:
dmSQL> CREATE AUTOEXTEND TABLESPACE ts2 DATAFILE f4 TYPE=DATA,
2> f5 TYPE=BLOB;
|
Raw Device Files
On UNIX
systems, if the prefix of the physical file name is /dev/, DBMaker
will regard it as a raw device file. The raw device file supports
faster accesses than a normal file. Thus you can use raw device files
to improve your database performance. You must create a raw device file
on a disk before associating this file with a tablespace. The following
dmconfig.ini specifies a raw device file f2 whose operating system
filename is /dev/rawf2 with 5000 pages.
[MY_DB] ;database name
f2 = /dev/rawf2 5000 ;a raw device file with 5000 pages
|
The following
SQL command creates a regular tablespace ts3 containing the above
raw device file.
dmSQL> CREATE TABLESPACE ts3 DATAFILE f2;
|
Adding Files to Tablespaces
You can
enlarge the size of a regular tablespace, and consequently the database,
by creating and adding new files to it. If you want to increase the
size available to insert or update data rows, you can add data files
into this regular tablespace. If you want to increase the size available
to store BLOB data, you should add BLOB files.
It is not
possible to add any new files to an autoextend tablespace, with one
exception. If an autoextend tablespace was created with only one data
file, and no BLOB file, you can add a BLOB file to it at a later time.
In other words, an autoextend tablespace can contain at most one data
file and one BLOB file.
Just like
when you create a tablespace, you have to specify the size and filenames
of the files you would like to add in the dmconfig.ini file when you
add data files to a tablespace. You also have to specify the file type
as BLOB when you want to add BLOB files, otherwise DBMaker will create
data by default. For example, the following entries in dmconfig.ini
specify a data file named f7 with 3000 pages whose operating
system filename is /disk1/usr/f7.dat.
[MY_DB] ;database name
f7 = /disk1/usr/f7.dat 3000 ;a data file with 3000 pages
|
And the
following SQL command adds the data file f7 into the tablespace
ts1.
dmSQL> ALTER TABLESPACE ts1 ADD DATAFILE f7;
|
The following
entries in dmconfig.ini specify a BLOB file named f8 with 5000
pages whose operating system file name is /disk1/usr/f8.blb.
[MY_DB] ;database name
f8 = /disk1/usr/f8.blb 5000 ;a blob file with 5000 pages
|
To add
this BLOB file to tablespace ts1 you enter the following command.
Note that the file type must be explicitly stated or it will be added
as a data file by default.
dmSQL> ALTER TABLESPACE ts1 ADD DATAFILE f8 TYPE=BLOB;
|
Altering File Sizes in Tablespaces
In addition
to adding files to a regular tablespace to enlarge a database, you can
also enlarge a database by increasing the size of existing files in
a regular tablespace. You can also alter the size of files in autoextend
tablespaces to pre-allocate disk space for improved performance. When
you alter the size of a file, DBMaker automatically updates the entry
for that file in dmconfig.ini to reflect the increased number of pages.
The following
SQL command gives an example of altering file size. It extends the size
of the file f1 by adding 100 pages. The file f1 must already
exist and be associated with some tablespace.
dmSQL> ALTER DATAFILE f1 ADD 100 PAGES;
|
Changing Autoextend Tablespaces to Regular Tablespaces
After you
create an autoextend tablespace, you can change it to a regular tablespace
by using the alter tablespace command. The following SQL command changes
the autoextend tablespace ts1 to a regular tablespace.
dmSQL> ALTER TABLESPACE ts1 SET AUTOEXTEND OFF;
|
Dropping Tablespaces
If a tablespace
is empty or contains information that is no longer required, you can
drop it from the database. Any tablespace in a DBMaker database, except
the system tablespace, can be dropped. To drop a tablespace, you must
first drop all tables in the tablespace or ensure it is already empty
of tables. For more information on how to drop tables from a tablespace,
refer to the chapter "Managing Schema Objects".
Dropping
a tablespace will automatically drop all the files associated with it,
but does not remove them from the operating system file system. Those
files will still exist in the file system and can be removed using operating
system commands to recover the disk space they occupy. The data stored
in the physical files corresponding to a tablespace is not recoverable
once the physical files have been removed from the file system. Therefore,
be careful when removing files associated with tablespaces or you may
lose valuable data.
The following
SQL command drops the tablespace ts2 and all files associated
with it.
dmSQL> DROP TABLESPACE ts2;
|
Getting Information About Tablespaces and Files
You can
obtain information about tablespaces, such as tablespace names, whether
they are regular or autoextend tablespaces, the number of files associated
with tablespaces and the number of total pages by browsing the system
table SYSTABLESPACE in the system catalog. You can display this data
by using the following SQL command:
dmSQL> SELECT * from SYSTABLESPACE;
|
You can
obtain information about files in a similar manner. By browsing the
system table SYSFILE you can get information about file names, file
types, database internal file identification, which tablespace files
are associated with and how many pages each file contains. To display
this data you can use the following SQL command:
dmSQL> SELECT * from SYSFILE;
|
For more
information about the system catalog tables SYSTABLESPACE and SYSFILE,
refer to Appendix B.
|
