CREATE TABLESPACE
Creates
a new regular or autoextend tablespace.
SYNTAX
| tablespace_name |
Name
of the new tablespace you want to create. |
| file_name |
Logical
name of the physical tablespace files. |
DESCRIPTION
The CREATE
TABLESPACE command creates a new tablespace. Creating a new tablespace
allows you to increase the physical storage available to the database.
Only users with SYSADM or DBA user privileges can execute the CREATE TABLESPACE
command.
The way
data is physically stored on computers has little or no significance to
a most users. DBMaker uses the relational data model to hide the details
of the physical storage model and present data using a logical storage
model instead.
In the DBMaker
physical storage model, files are physical storage structures that contain
the data in the database. Files themselves are managed by the operating
system (with the exception of raw Unix devices), while data in the files
is managed by the DBMS. DBMaker uses three types of files during normal
operation: data files, BLOB files, and journal file.
Journal
files are special files that provide a real-time, historical record of
all changes made to a database and the status of each change. This allows
the database to undo changes made by a transaction that fails, or redo
changes made successfully but not written to disk after a database crashes.
Journal files are used only by the database management system, and are
not used to store user data.
Data files
and BLOB files are used to store user and system data. Although they have
similar characteristics, DBMaker manages these two file types in different
ways to improve performance. Data files store table and index data, while
BLOB files store only Binary Large OBjects (BLOBs).
In the DBMaker
logical storage model, tablespaces are the logical storage structures
used to partition information in a database into manageable areas. Each
tablespace may contain several tables and indexes. Data in the tablespace
is managed by the DBMS, but is physically stored in data and BLOB files.
There are three types of tablespaces: regular, autoextend, and system.
Regular
tablespaces are tablespaces that have a fixed size and contain one or
more data or BLOB files. You can manually extend a regular tablespace
by enlarging existing files or adding new files. A regular tablespace
may contain a maximum of 32767 files, with a maximum cumulative size of
2 GB. On Unix platforms, regular tablespaces may be placed on raw devices.
(For more information on raw devices, see your Unix system documentation.)
Autoextend
tablespaces are tablespaces that automatically increase in size (to a
maximum of 2 GB) to hold additional data when required. They must contain
one data file, and may optionally contain one BLOB file. You cannot add
new files to an autoextend tablespace unless you first convert it to a
regular tablespace, with one exception: if an autoextend tablespace is
created with only one data file and no BLOB file, you may add a single
BLOB file at a later time. Although it is not necessary, you can manually
extend an autoextend tablespace by enlarging existing files. You may want
to do this to pre-allocate space for improved performance when inserting
a large amount of data into an autoextend tablespace. An autoextend tablespace
may only contain one data file and one BLOB file, with a maximum cumulative
size of 2 GB. Autoextend tablespaces cannot be used with raw devices.
System tablespaces
are tablespaces generated by DBMaker when you create a database. Each
database has one system tablespace, which contains the system catalog
tables used to store schema, security, and status information about the
entire database. The system tablespace is a special type of autoextend
tablespace. You cannot use the system tablespace to store user data. System
tablespaces contain one data and one blob file, which are created automatically
with the tablespace. System tablespaces may be converted to regular tablespaces.
System tablespaces cannot be used with raw devices.
The AUTOEXTEND
keyword is optional. This keyword specifies whether a tablespace is created
as an autoextend tablespace. An autoextend tablespace can extend its size
automatically if it requires more space to store data. If you create a
tablespace as an autoextend tablespace, you may change it to a regular
tablespace at any time. However, once you make the change to a regular
tablespace, you cannot change it back to an autoextend tablespace. You
may want to change a tablespace from autoextend to regular when a tablespace
has grown to fill all available space on a disk and you want to add additional
files on another disk, or when you want to restrict the amount of disk
space a tablespace will occupy. (An autoextend tablespace will grow to
fill all available space on a disk to a maximum of 2GB.)
The BACKUP
BLOB keyword is optional. This keyword specifies whether DBMaker will
back up BLOB data in this tablespace when the database is in BACKUP_DATA_AND_BLOB
mode. When BACKUP BLOB is set to ON, DBMaker will back up all BLOB data
in the tablespace when the database is in BACKUP_DATA_AND_BLOB mode. When
BACKUP BLOB is set to OFF, DBMaker will not back up any BLOB data in the
tablespace, regardless of the backup mode.
To ensure
data independence within a database, operating system files cannot be
referenced directly within a database. To work around this, each database
file has two names: a physical file name and a logical file name. The
physical file name is the name used by the operating system, while the
logical file name is the name used by the database when executing commands
such as CREATE TABLESPACE, ALTER TABLESPACE, and ADD DATAFILE. The logical
and physical file names are related by an entry in the dmconfig.ini configuration
file.
The DATAFILE
keyword specifies logical file name and type of files to create when creating
the tablespace. You can specify multiple files up to a maximum of 32767,
providing the type of tablespace you are creating allows it and there
is sufficient disk space for each file. However, you must have at least
one data file in each tablespace you create. You can add more files to
a tablespace at a later time using the ALTER TABLESPACE command.
The TYPE
keyword specifies whether DBMaker will create a new file as a data file
or a BLOB file. Use TYPE=DATA to create a new data file, and TYPE=BLOB
to create a new BLOB file. If you do not specify the type of file using
the TYPE keyword, the file will be created as a data file by default.
To ensure
data independence within the database, operating system files cannot be
referenced directly within a database. To work around this, each database
file has two names: a physical file name and a logical file name. The
physical file name is the name used by the operating system, while the
logical file name is the name used by the database. These two names are
related by an entry in the dmconfig.ini configuration file. Before executing
the CREATE TABLESPACE command, you must make an entry in the dmconfig.ini
specifying the logical file name, the physical file name, and the initial
size of each physical file in the appropriate database configuration section
(see example).
DBMaker
will create all physical files in the default database directory specified
by the DB_DBDIR keyword in dmconfig.ini, unless you explicitly specify
a different directory or path for the file. The initial file size is specified
as a number of data pages for data files, or a number of BLOB frames for
BLOB files.
You can
specify an initial file size for data files by specifying a value between
25 - 524287 pages. To calculate the actual size of the file in kilobytes,
multiply this value by 4 KB. You can specify an initial file size for
BLOB files by specifying a value between 2 - 524287 frames. To calculate
the actual size of the file in kilobytes, multiply this value by the value
of DB_BFRSZ from the dmconfig.ini configuration file.
The files
in a tablespace do not have to be located on the same disk; you may specify
a different disk (or different path on the same disk) for each file in
the tablespace. If you are using Unix, you may also locate files in a
regular tablespace on raw devices. Using raw devices allows faster access
and performance improvements over regular operating system files, since
DBMaker writes to raw device files directly instead of relying on operating
system calls.
Tablespace
names and logical file names have a maximum length of eighteen characters,
and may contain numbers, letters, the underscore character, and the symbols
$ and #. The first character may not be a number. Tablespace names are
case sensitive.
Physical
file names have a maximum length (including drive and path names) of 79
characters, and may contain any characters and symbols permitted by the
operating system, except spaces. The case-sensitivity of physical file
names is dependent on the operating system.
EXAMPLES
The following
example creates a regular tablespace named ts1 with one logical data file
named datafile, and one logical BLOB file named blobfile. You can add
additional data or BLOB files to this tablespace, up to a maximum of 32767
files.
CREATE TABLESPACE ts1 DATAFILE datafile TYPE=DATA, blobfile TYPE=BLOB
|
Before executing
this command, you must add a line similar to the following to your dmconfig.ini
file to map the logical file names to the physical file names, and indicate
the initial physical file size in pages for data files or frames for BLOB
files. In this example the size of the data file will be 400 KB, and the
size of the BLOB file will be 1600 KB (assuming the default BLOB frame
size of 16 KB is used).
datafile = c:\dbmaker\database\ts1_df1.db 100
blobfile = c:\dbmaker\database\ts1_bf1.bb 100
|
The following
example creates an autoextend tablespace named ts2 with one logical data
file named datafile, and one logical BLOB file named blobfile. You cannot
add any additional data or BLOB files to this tablespace.
CREATE AUTOEXTEND TABLESPACE mineTs DATAFILE myfile
|
Before executing
this command, you must add a line similar to the following to your dmconfig.ini
file to map the logical file names to the physical file names, and indicate
the initial physical file size in pages for data files or frames for BLOB
files. In this example the size of the data file will be 400 KB, and the
size of the BLOB file will be 1600 KB (assuming the default BLOB frame
size of 16 KB is used).
datafile = c:\dbmaker\database\ts2_df1.db 100
blobfile = c:\dbmaker\database\ts2_bf1.bb 100
|
RELATED COMMANDS
<
CREATE TABLE | Contents
| CREATE TEXT INDEX >
|
