Ingres Basics
Architecture
Processes
An Ingres installation is made up of a number of processes. These all run as user Ingres and can be seen by doing a ps –fu ingres.
The main processes are:
iidbms dbms
This is the DBMS server process.
iidbms recovery
This is the recovery server process
dmfacp
This is the archiver process
iijdbc
The JDBC Server process
rmcmd
Remote command execution from Visual DBA
iigcc
This is the communications process
iigcn
This is the name server (holds a register of all the other processes in the ingres installation).
Environment Variables
There are a number of environment variables that need to be set for the Ingres installation. These are:
II_SYSTEM
The root location of the ingres installation. This will contain a ingres directory which will in turn contain all the ingres files
PATH
Must contain $II_SYSTEM/ingres/bin and $II_SYSTEM/ingres/utility
LD_LIBRARY_PATH
Ingres shared libraries. Usually set to $II_SYSTEM/ingres/lib:/usr/lib
Log files
There are a number of log files used in an Ingres installation. These are all in the $II_SYSTEM/ingres/files directory and are called:
errlog.log
This is the main installation error log and should be checked first if there are any problems
iiacp.log
This is the archiver process log file.
iircp.log
This is the recovery process log file
Environment Settings
Ingres also has a set of its own installation parameters that it stores in a file called symbol.tbl (do not edit as it is not a ASCII file!). To view these parameters use the command ingprenv.
This will show things like default database/journal/checkpoint locations, installation ID and other global settings.
Databases
An Ingres database is made up of one or more data locations and one checkpoint and journal location.
There is a master database for each installation called the iidbdb. This contains information like users, databases, and locations. It is owned by user Ingres and is required for the installation to start.
An Ingres database itself uses Unix files. A table in the database is actually a Unix file (it can be multiple files when splitting across data locations). These files are all located in the data location for the database. To view database location details, the command infodb
For each database, there is a configuration file located in the main data location. This file is called aaaaaaaa.cnf and the details in this file are displayed by the infodb command.
Backup and Recovery
An Ingres backup is called a checkpoint. This can be run via the ckpdb command. There is a checkpoint template file that contains the commands to use when running a checkpoint. This is located in $II_SYSTEM/ingres/files and is called cktmpl.def.
The checkpoint normally takes a Unix tar backup of all the files in each data location for the database. There are 2 types of checkpoint – offline and online. An offline requires exclusive access to the database (and locks it while running), while a online doesn’t. Any transactions that are executing during the online checkpoint are recorded in what is called a dump file. These will be replayed if the checkpoint in recovered.
Ingres also has journal files (this is a option that can be enabled/disabled at a individual table/database level). A journal file contains transaction information that can be reviewed using the auditdb command. This will show user details and the SQL that they have run.
Monitoring
The main monitoring tool is called IPM (Interactive Performance Monitor). To run this, log on as Ingres and type ipm. This is split into 3 main sections:
Server
Enables you to drill down into each DBMS server and look at the user sessions and what state they are in and what SQL they are running (if any). User sessions can be removed using this tool
Locking
Shows current locking system state and also user locks
Logging
Shows transaction log file information including percentage full, and a list of open transactions and the status of the log file
If IPM cannot be started, there are a few other commands to use:
logstat
This will show all the details from the transaction logging system. The first and second page of output is the most important as it shows the status of the log file and the percentage full
ps –fu ingres
This will show the Ingres processes. Check to see if any are consuming 100% cpu (not usually a good sign)
iimonitor
This command will attach to the specified server and enable you to view/remove sessions and also stop the server. To find the server number, run the command iinamu and type show ingres. This will output the DBMS server number.
Basic Database commands