|
   
5.
Basic Database Administration
 5.1
Configuration File - dmconfig.ini
5.2
Basic Configuration of DBMaker
5.3
Creating a Database
5.4
Starting a Database
5.5
Connect To and Disconnect From a Database
5.6
Shut Down a Database
5. Basic Database Administration
This chapter
describes how to do basic database administration, including creating
a database, starting a database, connecting to a database, and shutting
down a database using the dmSQL interactive SQL utility. DBAs can use
the DBATool utility to do the same operations described in this chapter.
5.1 Configuration File - dmconfig.ini
Whenever
DBMaker is run, it requires many parameters to configure itself. These
parameters are stored in the dmconfig.ini configuration file. Users
(DBAs) can customize the database by properly setting up the options
in the dmconfig.ini configuration file. To reference a complete list
of options for dmconfig.ini, see Appendix A at the end of this
manual.
Format of dmconfig.ini
The format
of the dmconfig.ini configuration file is as follows:
[Section_name1]
<key_word1> = <value1>
<key_word2> = <value2> <value3> ; this is a comment
; this is a comment
...
...
...
[Section_name2]
<key_word3> = <value4> <value5>
<key_word4> = <value6>
...
...
...
|
The section
names are the names of databases. The keywords under each section define
the configuration of that database. Any string following a semi-colon
is considered to be a comment.
Location of dmconfig.ini
For UNIX
platforms, there are three places DBMaker may search for the dmconfig.ini
configuration file, in the order listed below:
the current directory.
the directory specified by the environment variable DBMAKER.
the subdirectory data in the home directory for user dbmaker
(~dbmaker/data).
However,
for Microsoft Windows systems, including Windows 3.1, Windows 95, and
Windows NT, the rule is different. DBMaker will only search for the
dmconfig.ini file in the directory under which you installed your Windows
system. In a typical Windows installation, this will be the WINDOWS
directory.
When DBMaker
requires the value of a configuration parameter for a particular database,
it will scan the above three directories (or the WINDOWS directory on
Microsoft Windows systems) to find a dmconfig.ini which contains a section
having the same section name as the database. Users can use any text
editor to edit this file and add or modify the parameter values in dmconfig.ini
so that DBMaker will use these parameters when it is run. When a DBA
creates a database and no corresponding section can be found in any
dmconfig.ini files, DBMaker will create a new section for the database
in the first dmconfig.ini file found, or in a new dmconfig.ini file
in the local directory (or the WINDOWS directory on Microsoft Windows
systems) if no dmconfig.ini file can be found. Therefore, when a DBA
starts a database the corresponding section in dmconfig.ini must be
found, or DBMaker will return an error. Although you may put various
sections in different dmconfig.ini files and put different dmconfig.ini
files in different directories, this practice is not recommended. Using
one global dmconfig.ini file will make maintenance easier.
Some Important Keywords
Here are
some examples of valid keywords that can appear in dmconfig.ini.
DB_DBFIL=<filename> - specifies the file name
for the system database file as <filename>.
DB_DBFSZ=<NP> - specifies the size of the system
database file in <NP> (number of pages).
DB_JNFIL=<filename> - specifies the file name
for the system journal file as <filename>.
DB_JNLSZ=<NP> - specifies the size of the system
journal file in <NP> (number of pages).
<logical_file>=<filename> <NP>
- specifies that the user-defined file with the name <logical_file>
will be mapped to <filename> with <NP>
pages. In other words, <filename> is the physical
file name for <logical_file>.
DB_NBUFS=<NP> - specifies the runtime data buffer
size in <NP> (number of pages).
DB_SVADR=<ip_address> or <host name>
- specifies the database server's IP address or its host name. In
a client/server system this option must be set on the client side.
DB_PTNUM=<port number> - specifies the TCP/IP
port number used to communicate between the database client and database
server.
Note:
Each DBMaker database page is 4KB.
Note:
All pattern matching is case insensitive except for <logical_file>.
Default Value
Some of
the options have default values. Therefore if a keyword doesn't appear
in dmconfig.ini, it's default value will be used. See Appendix A
for a more detailed description of the keywords and their default values.
A Typical Example of dmconfig.ini
Here is
a typical example of a dmconfig.ini file:
[Personnel]
DB_DBFIL = /disk1/bin/PERSONNEL.DB
DB_JNFIL = /disk1/bin/PERSONNEL.JNL
f1.os = /disk1/bin/PERSONNEL.OS 100
f1.blob = /disk1/bin/PERSONNEL.BLOB 1000
DB_UMODE = 1 ; multi-user mode
DB_NBUFS = 50
DB_NJNLB = 100
DB_NTRAN = 100
DB_DBFSZ = 40
DB_JNLSZ = 2000
DB_RTIME = 0
DB_SVADR = 192.72.116.130 ; server's IP address
DB_PTNUM = 21000 ; and port number
[LIBRARY]
DB_DBFIL=/disk3/usr/lib/library.db
DB_JNFIL=/disk3/usr/lib/library.jnl
DB_SVADR = 192.72.116.137
DB_PTNUM = 26999
DB_JNLSZ = 2000
|
In the
example, two sections are defined in dmconfig.ini, one for the Personnel
database and the other for the LIBRARY database.
5.2 Basic Configuration of DBMaker
In this
section we will examine some of the basic configuration parameters that
are commonly used when running DBMaker. If a DBA just wants to run a
database without consideration for its performance, he may ignore most
of the configuration parameters and DBMaker will use the default values
for these parameters.
File Name and File Size
In dmconfig.ini,
users can specify the name of the system file by adding the line:
The <filename>
parameter can be a simple file name like firstdb.sdb, a relative
path like mydb/firstdb.sdb, or a full path like /disk1/mydb/firstdb.sdb
("/" would be "\" for Windows platforms). The first
two cases will look for the file starting in the current directory.
The size of the system database file is 150 pages (600KB).
For the
journal file, the setup is similar the system file. The journal file
name and size can be changed using the following two keywords:
DB_JNFIL = <filename>
DB_JNLSZ = <np>
|
The DBMaker
default journal file size is 1000 pages (4 MB).
For the
system BLOB (Binary Large OBject) file, the setup is a little bit different.
Use the following keywords:
DB_BBFIL = <filename>
DB_BFRSZ = <nk>
|
The <NK>
parameter is the frame size in kilobytes. The size of the system BLOB
file is:
(4 + (nf
- 1) x nk) KB
If no DB_DBFIL,
DB_JNFIL and DB_BBFIL settings are defined, the default setting will
be used. The default setting for these three keywords is:
DB_DBFIL -- database name + '.SDB'
DB_JNFIL -- database name + '.JNL'
DB_BBFIL -- database name + '.SBB'
|
When creating
a database, DBMaker will create the system database file, the journal
file and the system BLOB file according to the related settings in dmconfig.ini.
DBMaker allows the user to add more files to a database by adding the
following line to dmconfig.ini:
<logical_file> = <filename> <np>
|
The <logical_file>
parameter is the file name used by DBMaker and the <filename>
parameter is the physical name which will be used by the operating system.
The <NP> parameter is the number of pages in this physical
file. The <NP> parameter is not optional.
DBMaker will create a file <NP> pages in
size and will link it to the specified tablespace in the database.
File Locations
If a database
will be accessed by many users who may run the DBMaker programs from
different directories (making the "current directory" different
for each user), then all of the file names in dmconfig.ini should be
full paths so that every DBMaker program will access the right files.
An alternative way is using the DB_DBDIR keyword. You can think of this
keyword as the "home directory" of a database. Once this keyword
is found in dmconfig.ini, all unqualified file names in that section
are considered to be located in this home directory.
Example
[DB1]
DB_DBDIR = /disk1/db
DB_DBFIL = mydb1
DB_JNFIL = /disk2/usr/DB1.JNL
|
The physical
file names are:
DB_DBFIL -- /disk1/db/mydb1
DB_JNFIL -- /disk2/usr/DB1.JNL
DB_BBFIL -- /disk1/db/DB1.SBB (using default file name)
|
Example
[DB2]
DB_DBFIL = mydb2
DB_JNFIL = /disk2/usr/DB2.JNL
|
The physical
file names are:
DB_DBFIL -- mydb2 ( in current directory )
DB_JNFIL -- /disk2/usr/DB2.JNL
DB_BBFIL -- DB2.SBB ( in current directory )
|
Note:
The rule also applies to user defined files.
Raw Devices
The DBMaker
physical storage system is very flexible. DBMaker allows users to create
a database with UNIX files only, with raw device files only, or with
files from both file systems. In dmconfig.ini, if a file name begins
with /dev/, that file will be treated as a raw device. Because
I/O operations on raw devices will be faster than on regular UNIX files,
DBAs are encouraged to use raw devices as database files. To use raw
devices as database files, the system manager must create raw devices
before creating any databases. Please refer to your UNIX system manual
for the procedure to create raw devices. Furthermore, the file size
must exactly match the physical size of the raw device. For example,
a raw device has 100 cylinders on a hard drive. Suppose each cylinder
has 1000 blocks and each block is 0.5KB large on that disk. Therefore
the number of pages(<NP>) specified in dmconfig.ini
should be:
| 1000
cylinders x 1000 blocks x |
0.5 |
= 12,500 pages |
| 4 |
Note:
Microsoft Windows and Windows NT do not support raw devices.
Example
[RAW1]
DB_DBFIL = /dev/rdsk/c2t0d0s2
[RAW2]
DB_DBDIR = /dev
FILE1 = rsd2c 1000
|
The Data Communications and Control Area
The Data
Communications and Control Area (DCCA) is a memory block in which
almost all information and data is placed. For multi-user databases,
the DCCA is allocated from shared memory and is used to do inter-process
communications. When a database starts, it will allocate a DCCA to hold
all information about that database. The DCCA can be divided into three
parts-page buffers, journal buffers, and the system control area. There
are several keywords in dmconfig.ini related to the usage of
the DCCA.
DB_NBUFS=<NP> - this keyword sets the number
of page buffers which DBMaker will use. The default value is 250.
DB_NJNLB=<NP> - this keyword sets the number
of journal buffers which DBMaker will use. The default value is 64.
DB_SCASZ=<NP> - this keyword will set the number
of pages in the system control area. The default value is 100.
DB_NTRAN=<number> - this keyword specifies the
maximum number of transactions in DBMaker. The default value is 20.
In general, specifying a larger number for DB_NTRAN will increase the
space allocated for the system control area.
The size
of the DCCA can be roughly estimated by adding the size of the page
buffers, the journal buffers, and the system control area. However,
this estimate may be smaller than the actual size required. This can
happen when the size of the system control area is too small. Once the
specified size of the DCCA is not large enough, DBMaker will automatically
allocate the minimum necessary space to hold the information required
to the DCCA instead of the default size used in the calculation above.
The size
of the DCCA cannot exceed the allowable shared memory size of the system
in a multi-user environment in UNIX, because in such a case the DCCA
is allocated from shared memory. Users can refer to their UNIX manuals
for instructions on how to increase the size of shared memory, which
generally requires a rebuild of the kernel. DBMaker will run more smoothly
with more buffers and a larger system control area.
The relationship
between the DCCA, page buffers, journal buffers, and the system control
area is explained in more detail in the chapter "Performance
Tuning".
Security Settings in DBMaker
For single-user
databases, no security is needed for setup. For client/server databases
users can turn security features on and off when creating the database
by using:
Setting
this parameter to 0 turns the security features off, and setting it
to 1 turns the security features on. This value defaults to 1 for multi-user
databases. Once a database has been created, there is no way to change
the security on/off flag for this database.
Default User and Password Settings in DBMaker
DBMaker
allows you to specify a default user name and password to use when connecting
to a database. The default user name and password is supplied by using:
DB_USRID = <user name>
DB_PASWD = <*****>
|
The default
user name and password must already exist in the database. These two
keywords are not examined when starting a database, but are checked
when connecting to a database instead.
5.3 Creating a Database
Since the
file formats for different models of DBMaker are all the same, DBAs
can use dmsqls or Server Manager to create databases either with
or without security by specifying the DB_SECUR parameter in dmconfig.ini.
Once a database is created without security, there is no way to change
it to security mode except by recreating the database.
Run dmsqls
and execute the following commands to create a database.
dmSQL> create db <database name>;
dmSQL> terminate db;
dmSQL> quit;
|
Before
creating the database the DBA must give the required parameters in dmconfig.ini,
such as file names and file sizes. DBAs are encouraged to put database
files onto raw devices to improve the performance of DBMaker. Furthermore,
if more than one hard drive is available, distributing the database
files onto different hard drives so that I/O can occur in parallel to
some degree will also improve performance.
When a
database is created with security, a default user SYSADM with no default
password is created automatically. The SYSADM user has all privileges
and access rights for that database.
5.4 Starting a Database
When a
database exists, DBMaker provides many ways of starting the database.
The purpose of starting a database is to allocate the required resources
from the operating system, initialize them, and wait for users to connect
to the database. Before starting a database, there are some parameters
such as DB_NBUFS and DB_NTRAN that can be configured, if necessary.
Starting a Single-User Database
For single-user
databases, a user must start the database every time they want to connect
to the database, and terminate the database when they finish using it.
You can use dmsqls to start a single-user database by issuing
the following commands:
dmSQL> START DB <database name> <user name> <password>;
.
< do DML here >
.
dmSQL> TERMINATE DB;
|
Note that
only users with DBA privilege can start a database. For information
about DBA privileges, refer to the chapter "Security Management".
After a single user database is started, only one user can access the
database.
Starting a Client/Server Database
For client/server
databases, the DBA must start the database on the server machine so
that all clients on different machines (or on the same machine) can
connect to the server database via network access. Starting a client/server
database is a little more complicated than starting a single-user database.
In order to do this, we have to get more system information. First,
we need to know the server machine's IP address. Because all clients
may be not in the same machine as the server machine, the only ID to
distinguish each machine on a network is the IP address. The server's
IP address is specified by DB_SVADR in dmconfig.ini.
The second
item is the port number. The server program will bind itself on a given
port number, which is specified by DB_PTNUM in dmconfig.ini, to wait
for connections. All client programs must connect to that port number
to communicate with the database server.
You can
add these two keywords to the dmconfig.ini file as follows:
DB_SVADR = <server IP address> (on client side)
DB_PTNUM = <port number> (on both server and client sides)
|
Use the
dmserver program to start a client/server database on the server
machine.
UNIX> dmserver <database name>
|
After you
enter the user name and the password, dmserver will start the database
and wait for clients to connect. The full syntax used to run dmserver
is as follows:
UNIX> dmserver [-f] [-t port_number] [-u username [-p password]] db_name
|
-f - runs the server program in foreground mode. (dmserver normally
runs in background mode.)
-t - explicitly specifies the port number to use. This port number
will be used rather than the port number defined in dmconfig.ini.
-u - specifies the login user name.
-p - specifies the password for the given user name.
If you
do not specify a username and password on the command line, dmserver
will search for DB_USERID and DB_PASWD in dmconfig.ini. If still not
found, dmserver will prompt users to enter a username and password.
Start Mode
When starting
a database, a DBA can specify the start mode by using the DB_SMODE keyword
in dmconfig.ini. Setting this keyword to 1 means normal startup. Setting
this keyword to 2 will start a database with a new journal. The old
journal information will be cleared and new journal information is written
from the beginning of the journal file. Never start a database with
a new journal unless you are sure you want to do so. For more detailed
information refer to the chapter "Advanced Database Administration".
Forced Startup
If a database
is damaged for some reason, it is possible that when starting that database
an error message is always returned, and you will not be able to start
that database. As a solution to this problem, DBMaker provides "forced
startup" mode to start a database that cannot be started by any
other means. Set DB_FORCS to 1 and DBMaker will force the database to
start up. Please refer to the chapter "Advanced Database Administration"
for more detailed information.
5.5 Connect To and Disconnect From a Database
This section
discusses how to connect to a client/server database which has already
been started. Before a user can do DML operations on a database, he
or she must first connect to that database. Because a single-user database
only allows a single user connection, every time a user is going to
use the database they must start it first (see "Starting
a Database"), but they don't need to explicitly make a
connection. For a client/server database, after disconnecting from the
database, the database is still active. Users can continue to make connections
until the database is shut down.
Connect To/Disconnect From a Client/Server Database
Use dmsqlc
to connect to and disconnect from a client/server database.
dmSQL> CONNECT TO <database name> <username> <password>;
.
< do DML here >
.
dmSQL> DISCONNECT;
dmSQL> QUIT;
|
The DB_SVADR
and DB_PTNUM keywords must be set in the dmconfig.ini configuration
file. If the DB_USRID and DB_PASWD keywords are defined in dmconfig.ini,
the <username> and <password>
options in the CONNECT command can be ignored.
Connection Time-Out
In a client/server
model database, sometimes a client can't connect to the server because,
for example, the server machine is powered off or the IP address of
the server machine is wrong. In these cases users may wait for the connection
to be established for a long time. To explicitly set the connection
time-out value, users can set the DB_CTIMO parameter in seconds. The
default value for this keyword is 5 seconds.
Lock Time-Out
When connecting
to a database, a lock time-out keyword DB_LTIMO could be defined in
dmconfig.ini to indicate how long in seconds a user will wait for a
lock which can't be acquired. For example, if DB_LTIMO=10, DBMaker will
return a "lock time-out" error if the user waits for a lock
for more than 10 seconds. DB_LTIMO can be set to 0 indicating that user
doesn't want to wait at all. Setting DB_LTIMO to -1 will turn off this
feature. In this case a user will wait for a lock until the lock is
released. Each user can have his own DB_LTIMO value.
5.6 Shut Down a Database
A database
must be shut down after all operations are finished. When a database
is shut down DBMaker will try to free all resources, such as the DCCA,
back to the operating system. If there are still active transactions
in the database engine, DBMaker will try to abort them. However, if
there are still active connections to the database engine, DBMaker will
shut down the database without killing the processes for those connections.
In this case the DBA should manually kill the processes, otherwise the
error message "Can not lock file transaction rollback" will
occur when starting the database the next time. Therefore DBAs should
ensure all users are logged off before shutting down the database. To
shut down a database, a DBA has to connect to a database first and then
issue the proper command to shut down the database. Only a DBA has the
privilege to shut down a database.
Use dmsqls
to shut down single-user databases, and use dmsqlc to shut down
client/server databases. Issue the following commands to shut down a
database:
dmSQL> CONNECT TO <database name> <dba username>
<password>;
dmSQL> TERMINATE DB;
dmSQL> QUIT;
After a
database is shut down, no one can connect to this database until the
database is started the next time.
|
