|
   
14.
Advanced Database Administration
 14.1
Monitoring a Database
14.2
Checking Database Consistency
14.3
Re-sizing Journal Space
14.4
Turning the Journal On/Off
14.5
Forcing Database Startup
14. Advanced Database Administration
This chapter
provides information about some of the advanced database administration
features provided by DBMaker. This includes monitoring a database, checking
database consistency, re-sizing journal space, turning journals on and
off, and forcing database startup.
14.1 Monitoring a Database
This section
shows you how to monitor information about the status of a database,
including resource status, operation status, connection status, and
concurrency status. This section also shows you how to kill a connection.
The Monitor Tables
DBMaker
stores the database status in four system catalog tables: SYSINFO, SYSUSER,
SYSLOCK, and SYSWAIT.
The SYSINFO
table contains database system values, including the total DCCA size,
the available DCCA size, the number of maximum transactions, the number
of page buffers, and statistics on system actions such as the number
of active transactions, the number of started transactions, the number
of lock requests, the number of semaphore requests, number of physical
disk I/O, number of journal record I/O, and others. You can use this
table to monitor the database system status, and you also can use this
information to tune your database. Refer to Performance Tuning
for more information on performance tuning.
The SYSUSER
table contains connection information, including connection ID, user
name, login name, login IP address, and the number of DML operations
which have been executed. You can use this table to monitor which users
are using your database.
The SYSLOCK
table contains information about locked objects, such as the ID of the
locked object, lock status, lock granularity, ID of the connection locking
this object, and others. You can use this table to monitor which objects
are being locked by which connection, and which users are locking which
objects.
The SYSWAIT
table contains information on the wait status of connections, including
the ID of the connection that is waiting and the ID of the connection
it is waiting for. You can use this table to monitor the concurrency
status of connections. Once a connection is waiting for those resources
locked by an idle (or dead) connection, you can determine which connection
is locking those objects from this table. Then you can kill the idle
(or dead) connection to release those resources.
You browse
these four system catalog tables in the same way you browse ordinary
tables. For example, the following SQL command is used to browse the
SYSUSER table.
dmSQL> SELECT * FROM SYSUSER;
|
Refer to
Appendix B for more detailed information on these four system
catalog tables.
Killing Connections
You may
need to kill a connection when the connection is holding resources and
is idle for a long time, or when you need those resources urgently.
Also, you should kill all active connections before you shut down a
database.
Before
killing a connection, you can browse the SYSUSER table to determine
its connection ID. For example, assume you want to kill the connection
for the user named Eddie. You can get his connection ID with
the following SQL command:
dmSQL> SELECT CONNECTION_ID FROM SYSUSER WHERE USER_NAME = `Eddie';
CONNECTION_ID
============
352501
|
And then
you can kill his connection with the following SQL command:
14.2 Checking Database Consistency
DBMaker
includes several commands that are used to check the consistency of
a database. Database consistency includes things such as if an index
has a key but it does not exist in the table, or if a key exists in
a foreign table but does not exist in the parent table. DBMaker supports
six commands to check different levels of consistency. These commands
are time consuming when the database is large and they will take locks,
thus you should only use them when necessary.
Checking Indexes
DBMaker
allows you to check an index and its relationship to a table. It checks
if the index structure (B-tree) is correct, if the data is in order,
and if the index keys exactly match the data records, etc.
If you
think an index has a problem, you can use this command to verify that
a problem exists. If DBMaker finds corruption in the index, you can
drop and rebuild the index to fix the corruption. For example, the following
SQL command checks the index consistency of the index IDX1 in
table TBL1.
dmSQL> CHECK INDEX TBL1.IDX1;
|
Checking Tables
DBMaker
allows you to check all records, indexes, and BLOB data associated with
a table, and the relationship between foreign and parent tables. If
there is any inconsistency in a table, you can unload all records in
the table, drop the table, recreate it and then reinsert all records.
For example, the following SQL command checks the table consistency
of the table TBL1.
Checking Files
DBMaker
allows you to check the contents of every page (or frame) in a data
file. Any corruption found when checking files is usually caused by
disk errors. For example, the following SQL command checks the file
consistency of the data file FILE1.
Checking Tablespaces
DBMaker
allows you to check files and tables associated with a tablespace. When
checking files and tables, DBMaker uses the same methods as the check
file and check table commands, and returns the same results as if these
commands were executed directly. For example, the following SQL command
checks the tablespace consistency of the tablespace TS1.
dmSQL> CHECK TABLESPACE TS1;
|
Checking Catalogs
DBMaker
allows you to check the consistency of system tables. If the system
catalogs have errors, the database may be seriously corrupted. For example,
the following SQL command checks the consistency of the system catalogs.
Checking Databases
DBMaker
also allows you to check the whole database including the system catalogs
and all tablespaces. For example, the following SQL command checks the
consistency of an entire database.
If any
corruption exists and you have previously backed up of your database,
please use the most recent backup to restore the database. For more
information, refer to "Database Recovery, Backup, and Restoration".
When the
database has no backup and corruption is found in indexes, you can just
drop and recreate the affected indexes. If any other type of corruption
has occurred, you should immediately back up the database including
all data and journal files. Then you can try to shut down and restart
database and run the DBCC commands again. After DBMaker automatically
recovers from a crash, some types of corruption may be fixed. If any
inconsistency still exists, please contact a CASEMaker technical support
representative to help you fix the remaining problems with your database.
14.3 Re-sizing Journal Space
If you
frequently get journal full messages when your database is running,
you may want to enlarge the journal files to improve database performance.
However, you should note that in DBMaker 3.0, you cannot use previous
backups to restore your database to a specific point in time after re-sizing
the journal files. To protect your database from disk failure, perform
a full backup immediately after you resize the journal files.
To resize
a journal file, the DBA needs to perform the following steps:
Estimate your new journal file size. This step is needed to determine
the number and size of journal files you require.
Shut down the database.
Update dmconfig.ini to re-specify these 2 parameters.
Set the start mode to new journal mode in dmconfig.ini.
Restart the database.
Reset the start mode back to normal in dmconfig.ini.
Perform an online full backup if your database is in BACKUP-DATA or
BACKUP-DATA-AND-BLOB mode.
14.4 Turning the Journal On/Off
When loading
a large amount of data into a clean database, you can turn off the journal
to enhance performance if you are not concerned with the possibility
of errors occurring.
To turn
off the journal, the database must be in single-user and NONBACKUP mode.
If any transactions have already made changes to the database, the command
will fail. You can use the SET JOURNAL OFF command to turn off the journal.
After turning off the journal, DBMaker will not record the various changes
made to the database. This means the ROLLBACK TRANSACTION and ROLLBACK
TO SAVEPOINT commands will have no effect.
You can
turn the journal on again after turning it off. You do this with the
SET JOURNAL ON command. After issuing this command, the journal will
function normally.
14.5 Forcing Database Startup
DBMaker
automatically performs recovery operations if errors occur when you
start a database normally. In this situation, almost all databases should
start up properly. If your database cannot start up at this time, there
may be some disk errors in your database. Disk errors require the database
be restored from the most recent backup to repair it. If your database
has no backups and cannot start, you need to use the forced startup
mode provided by DBMaker.
DBMaker
supplies a forced startup option for this type of situation.
All you need to do is to set forced startup mode on by using the DB_FORCS
keyword in the dmconfig.ini file. Setting this keyword to 1 enables
forced startup mode, and setting it to 0 disables it. When forced startup
mode is on, DBMaker will skip errors when starting the database.
If you
still cannot start your database, there is one remaining alternative
provided in the procedure below. However, before performing this procedure
you should back up all data and journal files. When this is complete,
do the following:
Set the forced startup mode to off in dmconfig.ini.
Set the start mode to new journal mode in dmconfig.ini.
Restart the database.
Reset start mode back to normal in dmconfig.ini.
The database
will start up this time. DBMaker provides the option to use a new journal
to force the database to start without any recovery operations. Therefore,
if errors serious enough to prevent the database from starting have
occurred, the database may be in an inconsistent state.
After starting
the database with this method, check the consistency of the database.
For more information on database consistency checking, refer to "Checking
Database Consistency".
|
