6. Basic System and Database Administration

In this section I outline some of the basic tasks of the Ingres system administrator and the Ingres database administrator. You will also see what tools Ingres provides to perform these tasks. In the following I suppose you are logged in as ingres.

6.1. Starting and Stopping Ingres

You have already seen how to start Ingres:

$ ingstart

To stop Ingres, use the ingstop command:

$ ingstop

ingstop only stops Ingres if the are no active user sessions. If you want to stop the system regardless of user sessions, use the following form:

$ ingstop -force

In this case, after you have killed Ingres, check if it released all shared memory segments and semaphores it had used:

$ ipcs -a

If you see shared memory segments or semaphores in ipcs's output that are still attached to the ingres user, release them with Ingres' ipcclean utility:

$ ipcclean

Warning

Take care: forcing Ingres to stop might make your databases inconsistent.

6.2. New Ingres Users and Locations

In order for any user to have access to the Ingres installation, you have to define them as Ingres users with the accessdb utility.

Start accessdb:

$ accessdb

Select the Users option, then Create.

Here, enter the name of the user. You do not have to modify permissions.

Save, then End, and End.

You can also use accessdb to create new locations, change their types or extend databases to new locations. The usage of accessdb is covered in the System Reference Guide and in the Database Administrator's Guide.

As an alternative to accessdb, you can maintain users and locations by running SQL commands on iidbdb (create user, create location, etc.). The syntax of these commands can be found in the SQL Reference Guide.

Warning

Since the ingres user has unlimited power of changing and possibly destroying any element of an Ingres installation, it is highly advisable that you only use this account for carrying out administrative tasks. Create another Linux user and set its environment to that of ingres. Register it as an Ingres user via accessdb and use this account for everyday work.

6.3. Creating and Destroying Databases

In subsection Checking the Installation you created a new database. You did not specify any options in the

$ createdb test

command. Therefore the values stored in II_DATABASE, II_CHECKPOINT, etc., became locations for the test database. You could have specified each location explicitly:

$ createdb test -d<data location> -c<checkpoint location> -j<journal location>
-b<dump location> -w<work location>

You can remove a database with the destroydb command:

$ destroydb test

Warning

Be careful, because Ingres will not prompt you before destroying the database.

6.4. Collation Sequences

The collation sequence determines which of any two character strings should be considered less than the other. In Ingres, every database can have its own sort order. You can specify the collation sequence when creating the database:

createdb test -lhun

If you omit the -l parameter, the database will have the default collation sequence which is determined by the implicit sort order of the code set of the Ingres installation (II_CHARSET).

If you want to use your own collation sequence (it is hun in the example above), you have to create a definition file first. The structure of this file must obey to simple rules by which you specify the absolute or relative ordering of letters and/or strings in your language. This file must then be compiled by the aducompile utility for Ingres to be able to use it.

The Spanish collation sequence and the collation based on the DEC Multinational Character Set are available both in source (spanish.dsc and multi.dsc), and compiled form (spanish and multi).

You specify these collation sequences in the following way:

createdb test -lspanish

createdb test -lmulti

The compiled definition files for a collation sequence must be in the $II_SYSTEM/ingres/file/collation directory. The syntax rules of the definition files can be found in the System Reference Guide. It may also be useful to examine the definition files for the Spanish and the DEC Multinational collations.

6.5. Backup and Recovery

You can back up an Ingres database or certain tables in it with the ckpdb utility. The following command backs up the test database:

$ ckpdb test

Note: Checkpoints can be taken online.

Restoring a database can be done with the rollforwarddb command:

$ rollforwarddb test

By default, rollforwarddb, using the latest checkpoint and all journal files created since that checkpoint, restores the database to its last committed state. However, you can specify a point in time to restore the database to the state it was in at that time. You can go back as far as 16 checkpoints (Ingres stores data for the last 16 checkpoints in the control file of the database).

Both ckpdb and rollforwarddb accept many parameters. You can read more about these commands in the System Reference Guide. Besides, you should read Michael Leo's paper on Ingres backup and recovery at http://www.naiua.org/papers/backup99.zip.

Both ckpdb and rollforwarddb use a template file ($II_SYSTEM/ingres/files/cktmpl.def). By modifying this file, you can customize the Linux commands that do the physical backup and restore of the data files. Consult the Database Administrator's Guide for the syntax of this file.

6.6. Configuring Ingres

Most Ingres parameters can be set via the cbf utility. This is the program by which you can specify the number of DBMS servers, the sizes of different caches and a lot of other variables. The usage of cbf is detailed in the System Reference Guide.

6.7. Monitoring Ingres

You can use the ipm utility to monitor a running Ingres system (Visual DBA only runs on Win32). With ipm, you can monitor and manage user sessions, and also the locking and logging subsystems.

6.8. Message Files

The Ingres message files reside in the $II_SYSTEM/ingres/files directory. The most important of these is errlog.log. Should any problems arise during the running of Ingres, this is the file to check first.