3. Creating a Database#
After installing Altibase, the database administrator must create and manage the database by estimating the amount of user data generated. This chapter describes the main points to be aware of when creating a database.
An Altibase database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data. Altibase stores data logically in tablespaces and physically in data files, which are associated with corresponding tablespaces. Before the database server can be started, it is first necessary to create a database manually using the CREATE DATABASE command.
This section describes the types of tablespaces and logging systems, and how to create a database.
Types of Tablespaces#
An Altibase database consists of several kinds of tablespaces. Tablespaces are classified into several types, depending on their usage and how the data is stored.
By default, when the user runs the CREATE DATABASE statement, checkpoint images and data files are created in the $ALTIBASE_HOME/dbs directory.
Note
There is no limit on the filename extension or location of files specified when users create a tablespace.
Altibase supports the following types of tablespaces:
Memory Tablespace#
Memory tablespace exists in memory. Performance-critical objects are usually located in memory tablespace, along with dictionary tables, system objects such as sequences, and user-created memory tables.
Disk Tablespaces#
Disk tablespace exists on disk, and usually consists of user disk tables and disk indexes. Disk tablespace is classified into system tablespace and user data tablespace.
Undo Tablespace#
Undo Tablespace is the tablespace in which images of previous states of data (before update) are stored for a certain period in order to support Multiversion Concurrency Control (MVCC), which is the management of multiple versions of records that exist in disk tables.
Temporary Tablespace#
Temporary Tablespace is the tablespace for storing temporary tables and indexes, which are created when queries are processed. Analogous to the data tablespace, this tablespace is categorized into system temporary tablespace and user temporary tablespace.
Volatile Tablespace#
Volatile Tablespace is a tablespace for saving objects in memory in order to avoid disk input/output and therefore realize better performance. All data objects in volatile tablespace disappear when the database is shut down. The size of volatile tablespace can't exceed the available physical memory space in the system.
The Altibase Logging System#
The data in a database must be durable under any circumstances. Altibase guarantees the durability of data using a logging system that consists of the following two kinds of files:
Log Files#
These are files in which log records are written in order to be ready for use in performing complete system recovery in the event of an abnormal shutdown while transactions are underway. Altibase log files are named logfile (where "" indicates the sequential number of the log file).
Log Anchor Files#
Data that are important for database execution (such as information about tablespaces, the location of data files, and checkpointing) are stored in log anchor files. In order for the server to start up correctly, the contents of these files must be valid, otherwise it will be impossible to start the server. Log anchor files are also used for database recovery.
When the database is first created, the log files and the log anchor files are created and saved in $ALTIBASE_HOME/logs/.
Altibase maintains a set of 3 log anchor files. These log files are created in the same location
when the database is created, but it is recommended that the 3 log anchor files be maintained on different file systems. The property for specifying the location of the log anchor files is LOGANCHOR_DIR.
For more detailed information about the Altibase properties, please refer to the General Reference > Chapter 2. Altibase Properties
Preparing to Create a Database#
After installing the Altibase package, use the iSQL utility (which is provided as part of the package) to manually create a database as follows: First, execute the iSQL utility with SYSDBA privileges.
$ isql –u sys –p manager –sysdba
This does not access a database, but establishes an administration session. It will then be possible to see a display like the following:
------------------------------------------------
Altibase Client Query utility.
Release Version 7.3.0.0.1
Copyright 2000, ALTIBASE Corporation or its subsidiaries.
All Rights Reserved.
------------------------------------------------
ISQL_CONNECTION = TCP, SERVER = 127.0.0.1, PORT_NO = 20300
iSQL(sysdba)>
Once the preceding steps have been accomplished, it is first necessary to start the server process in order to execute the CREATE DATABASE statement. It is important to understand the Altibase startup phases. Several important administration tasks are only executable in particular startup phases.
Phase 1: The Pre-Process Phase#
Before starting the server process, Altibase initializes database memory in this phase.
A database can be created during the Process phase. In the Pre-process phase, execute the following command:
iSQL> STARTUP PROCESS
Trying Connect to Altibase.. Connected with Altibase.
TRANSITION TO PHASE: PROCESS
Command execute success.
Phase 2: The Process Phase#
In this phase, the user can create a database or to retrieve and change Altibase properties using the CREATE DATABASE statement.
Phase 3: The Control Phase#
In this phase, all database files are loaded. It is ready for restart recovery. Please refer to the section "Recovering a Database" in Chapter 10 for a description of restart recovery.
Phase 4: The Meta Phase#
In this phase, recovery is completed and in which it is possible to upgrade meta data and reset online logs.
Phase 5: The Service Phase#
In this final phase, the database is ready to provide the service to the user.
Creating a Database#
In the Process phase, use the CREATE DATABASE command to create a database as shown below.
For more detailed information on using the CREATE DATABASE statement, please refer to the SQL Reference.
In the following example, a database is created using the default options:
iSQL> CREATE DATABASE mydb INITSIZE=50M NOARCHIVELOG CHARACTER SET KSC5601 NATIONAL CHARACTER SET UTF16;
DB Info (Page Size = 32768)
(Page Count = 1537)
(Total DB Size = 50364416)
(DB File Size = 1073741824)
Creating MMDB FILES [SUCCESS]
Creating Catalog Tables [SUCCESS]
Creating DRDB FILES [SUCCESS]
[SM] Rebuilding Indices [Total Count:0] [SUCCESS]
DB Writing Completed. All Done.
Create success.
Shutting Down a Database Server after Database Creation#
Once a database has been created, the server process that was started for that purpose can be shut down, or can proceed to the service phase. To shut down the server, use the shutdown command with the abort option, as follows:
iSQL(sysdba)> SHUTDOWN ABORT
iSQL(sysdba)>
Once the server has been shut down, iSQL is disconnected from the Altibase server process and returns to the pre-processing phase.
In addition to "abort", the shutdown command can also be used with the "im../imgte" and "normal" options, but only when the server is running in the service phase.
Database Initialization Properties#
When a database is created using the CREATE DATABASE statement, any properties that are not specified in the CREATE DATABASE statement are set depending on the settings made in the altibase.properties file, which is located in the $ALTIBASE_HOME/conf/ directory. The relevant properties are listed below. In the table, a question mark ("?") indicates the path specified using the ALTIBASE_HOME environment variable.
Please fully understand the Altibase properties related to database initialization listed in the table below.
| Property Name | Description | Default |
|---|---|---|
| DB_NAME | The name of the database to be created. | mydb |
| MEM_DB_DIR | The directory where the database files will be located. | ?/dbs |
| SERVER_MSGLOG_DIR | The directory for storing the files (altibase_boot.log) in which Altibase server messages are recorded. | ?/trc |
| MEM_MAX_DB_SIZE | The maximum size to which the memory da- tabase can dynamically increase. | 4G |
| LOGANCHOR_DIR | The directory where the log anchor files will be located. Three directories must be specified. | ?/logs |
| LOG_DIR | The directory where the log files will be located. | ?/logs |
| LOG_FILE_SIZE | The size of an individual log file. | 100M |
| EXPAND_CHUNK_PAGE_COUNT | The number of memory tablespace pages that are allocated at one time. | 3200 |
| TEMP_PAGE_CHUNK_COUNT | The number of temporary pages in memory tablespace that are allocated at one time. | 128 |
| SYS_DATA_TBS_EXTENT_SIZE | The size of an extent for system data tablespace. | 256K |
| SYS_DATA_FILE_INIT_SIZE | The initial system tablespace size when the CREATE DATABASE statement is executed. | 100M |
| SYS_DATA_FILE_MAX_SIZE | The maximum size of a data file in system tablespace. | 2G |
| SYS_DATA_FILE_NEXT_SIZE | The amount by which a data file in system tablespace is automatically increased when the auto-extend option is used. | 1M |
| SYS_TEMP_TBS_EXTENT_SIZE | The size of an extent in temporary tablespace. | 256K |
| SYS_TEMP_FILE_INIT_SIZE | The initial size of temporary tablespace when the CREATE DATABASE statement is exe- cuted. | 100M |
| SYS_TEMP_FILE_MAX_SIZE | The maximum size of a data file in temporary tablespace. | 2G |
| SYS_TEMP_FILE_NEXT_SIZE | The amount by which a data file in temporary tablespace is automatically increased when the auto-extend option is used. | 1M |
| SYS_UNDO_TBS_EXTENT_SIZE | The size of an extent in undo tablespace. | 128K |
| SYS_UNDO_FILE_INIT_SIZE | The initial size of undo tablespace when the CREATE DATABASE statement is executed. | 100M |
| SYS_UNDO_FILE_MAX_SIZE | The maximum size of a data file in undo tablespace. | 2G |
| SYS_UNDO_FILE_NEXT_SIZE | The amount by which a data file in undo tablespace is automatically increased. | 1M |
| USER_DATA_TBS_EXTENT_SIZE | The size of a single extent in user tablespace. | 256K |
| USER _DATA_FILE_INIT_SIZE | The initial size of user tablespace when the CREATE DATABASE statement is executed. | 100M |
| USER_DATA_FILE_MAX_SIZE | The maximum size of a data file in user tablespace. | 2G |
| USER _DATA_FILE_NEXT_SIZE | The amount by which a data file in user tablespace is automatically increased | 1M |
| USER_TEMP_TBS_EXTENT_SIZE | The size of an extent in user temporary tablespace. | 256K |
| USER_TEMP_FILE_INIT_SIZE | The initial size of user temporary tablespace when the CREATE DATABASE statement is executed. | 100M |
| USER_TEMP_FILE_MAX_SIZE | The maximum size of a data file in user temporary tablespace, | 2G |
| USER_TEMP_FILE_NEXT_SIZE | The amount by which a temporary data file is increased in size when user tablespace is extended automatically. | 1M |
| ADD_EXTENT_NUM_FROM_TBS_TO_SEG | The number of newly assigned extents when a segment is extended. | 1 |
| ADD_EXTENT_NUM_FROM_SYSTEM_TO_TBS | The number of newly assigned extents when a tablespace is extended. | 4 |
For more detailed information about the Altibase properties, please refer to the General Reference > Chapter 2. Altibase Properties.