ALTER TABLESPACE

Adds a file to a tablespace or changes the tablespace type from autoextend to regular.

SYNTAX

DESCRIPTION

The ALTER TABLESPACE command adds a file to an existing tablespace or changes the tablespace type from autoextend to regular. To execute the ALTER TABLESPACE command on a tablespace, you must have DBA or SYSADM security privileges.

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

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 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. They may be extended manually by enlarging existing files in the tablespace or adding new files to the tablespace. When adding a new file, you must first make an entry in dmconfig.ini specifying the logical file name, the physical file name, and the initial file size in the appropriate database section. A regular tablespace may contain a maximum of 32767 files, with a maximum cumulative file 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. When adding a BLOB file, you must first make an entry in dmconfig.ini specifying the logical file name, the physical file name, and the initial file size in the appropriate database section. 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 created as an autoextend tablespace, unless you create your database on a Unix raw device. 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 are created with an initial data file size of 600 KB, and an initial blob file size 20 KB.

Use the SET AUTOEXTEND OFF keywords to change any autoextend tablespace to a regular tablespace. 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.)

Use the ADD DATAFILE keywords to add a new data or blob file to a tablespace. Files added to a tablespace do not have to be located on the same disk as existing files in the tablespace. If you are using Unix, you may also locate files on raw devices. This allows faster access and performance improvements over normal files, since DBMaker writes to raw device files directly instead of relying on operating system calls.

As mentioned earlier, files that make up a tablespace are referenced within the database using logical file names to maintain physical data independence. The logical file names are mapped to the physical file names in the dmconfig.ini configuration file, as shown in the examples on the following page. DBMaker will create a new file in the default database directory specified by the DB_DBDIR keyword in dmconfig.ini, unless you include a different directory or path in the file name.

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

When adding a new file, you should specify the file type with the TYPE=DATA and TYPE=BLOB keywords. If you do not specify the type of file, the file will be created as a data file by default.

You must also indicate the file size in data pages for a data file, and BLOB frames for a BLOB file. Data pages are 4KB, while BLOB pages are variable in size and can range from 8KB to 256KB. (To determine the size of a BLOB frame, check the DB_BFRSZ keyword for your database in the dmconfig.ini configuration file.) For autoextend tablespaces this is only the initial size, since DBMaker will increase the file size to hold additional data when required.

EXAMPLES

The following example adds file f1.db to the ts1 tablespace. File f1.db has the logical file name file1.

ALTER TABLESPACE ts1 ADD DATAFILE file1 TYPE = DATA

Before executing this command, you must add a line similar to the following to your dmconfig.ini file to map the logical file name to the physical file name, and indicate the initial file size in 4KB pages. In this example, the file size will be 400 KB.

file1 = c:\dbmaker\databases\f1.db 100

The following example changes the tablespace mode from autoextend to regular and adds file f2.bb to the ts2 tablespace. File f2.db has the logical file name file2.

ALTER TABLESPACE ts2 SET AUTOEXTEND OFF
ALTER TABLESPACE ts2 ADD DATAFILE f2 TYPE = BLOB

Before executing these commands, you must add a line similar to the following to your dmconfig.ini file to map the logical file name to the physical file name, and indicate the initial file size in frames. In this example, the file size will be 4000 KB (assuming the default BLOB frame size of 8KB).

file2 = c:\dbmaker\databases\f2.bb 500

RELATED COMMANDS

• CREATE TABLESPACE

< ALTER TABLE SET OPTIONS | Contents | ALTER TRIGGER ENABLE >