4. Preparing for the Installation

This is the longest section and so it should be: after careful planning the installation itself should be an easy task.

4.1 Ingres Environment Variables

You will use Ingres environment variables to determine where to put further elements of the Ingres installation. These variables, unlike II_SYSTEM, aren't shell variables but rather parameters of Ingres stored in a file. Some of them can be changed at any time after installation, but altering the value of others requires a whole new install. Later you will see which of them are of this "stable" nature.

During installation, you can choose between setting these variables manually or letting the installer program set them to their default values (Express Install).

In the following, we will take the Ingres environment variables one by one and see what each of them is good for. It will help if you put their planned values on paper. You can find an Installation Worksheet in the Getting Started Guide which you can print out and use for this purpose.

4.2 II_LOG_FILE and II_DUAL_LOG

Ingres uses an installation-wide transaction log file to record all changes made to any database (recording changes is necessary for rolling back transactions, should it be required, and also for recovering databases after a system crash). The transaction log resides in II_LOG_FILE/ingres/log, where II_LOG_FILE is an Ingres environment variable. The name of the log file is ingres_log.

Express Install creates a log file of the minimal possible size, 16 Mb. Such a log file may not be large enough even in a development system. If you have space and choose manual install (in which case you can specify the size of the log), set it to something much larger.

Both the location of the log file and its size can be changed at any time after installation. The method of doing this is described in the System Reference Guide.

You also have to decide if you want dual logging (mirroring the transaction log). If the log gets corrupted for any reason, Ingres stops and you have to recover your databases from backup. Therefore, in a live system, it is almost compulsory either to have some type of RAID protection of the log or to have it mirrored by Ingres. If you use dual logging, the copy of the log file can be found under II_DUAL_LOG/ingres/log. Its name is dual_log.

In a development environment, mirroring the log is not always necessary.

4.3 Database Locations

Files constituting an Ingres database are put in different directories determined by means of Ingres locations. Every location points to the root of a directory tree. A location can be used for storing different types of files (see below). Databases can also share locations.

Let us see the five location types:

• Data location: place for data files of a database. In Ingres every system table and every user table, and also every index goes in a separate file.
• Checkpoint location: checkpoint is the term Ingres uses for a database backup. By default, backups are created in the checkpoint location of the database.
• Dump location: on-line backups are possible in Ingres, that is, the database may be in use while the backup program is running. For this reason, the database may change while it is being checkpointed. Ingres, so that it can restore the database to the state it was in at the beginning of the backup, saves the before images of those data blocks (pages) that change during the backup process. Ingres places these pages in the dump location.
• Journal location: from time to time, Ingres writes the records of committed transactions from the log file to journal files (journaling may be set on or off at the database or even at the table level). The frequency of this activity is set as a function of the amount of information that is written to the transaction log. Journaling protect the installation against media failures: if the disk containing the database crashes, you can restore the last (just before the failure occurred) committed state of the database using a backup (checkpoint) of the database and the journals created after that checkpoint was taken. If you lose the log disk, you can restore the last committed state the database was in at the time the last journal file was refreshed. Naturally, the journal location determines where the journal files for a database reside.
• Work location: Ingres, mainly for sorting large volumes of data, needs temporary work space on disk. It creates temporary files in the work location.

A database can have more than one data or work location (this is called extending the database). However, every database must have a primary data location. The system tables reside on the primary location, together with the control file of the database (this latter is called aaaaaaaa.cnf. The control file stores certain basic information about the database. You can see this information with the infodb command after you have completed the installation.) When creating a table or index, if you don't specify the location(s) to put it in, it will be placed in the primary data location.

If a database has more than one work location, Ingres, by default, uses all of them for each sort.

When backing up the database you don't necessarily need to use the checkpoint location of the database. The ckpdb command allows you to specify an arbitrary place for the backup, this way you can checkpoint a database directly to tape as well.

As I said before, every Ingres location points to a Linux directory. The opposite is not true: more than one location can point to the same directory. Let us suppose that our database, test, has the following locations:

• DATALOC1: data location --> /opt
• CKPLOC: checkpoint location --> /opt
• DMPLOC: dump location --> /opt
• JRNLLOC: journal location --> /opt
• WORKLOC1: work location --> /opt

Every location points to the /opt directory. Then, elements of the database will be in these directories:

• data files: /opt/ingres/data/default/test
• checkpoint files: /opt/ingres/ckp/default/test
• dump files: /opt/ingres/dmp/default/test
• journal files: /opt/ingres/jnl/default/test
• temporary files: /opt/ingres/work/default/test

Let us suppose now, that we extend the database to the following locations:

• DATALOC2: data location--> /opt
• DATALOC3: data location--> /disk2
• WORKLOC2: work location --> /disk2

The database is effectively extended to the directories:

• data files: /disk2/ingres/data/default/test
• temporary files: /disk2/ingres/work/default/test

Location DATALOC2 points to /opt, just like DATALOC1. Tables to be created in location DATALOC2 will go to /opt/ingres/data/default/test, the same directory where tables created in location DATALOC1 reside.

As a location can be used for storing different types of files, we could have created just one location for DATALOC1, CKPLOC, DMPLOC, JRNLLOC, and WORKLOC1.

You can also see from the example above why different databases can use the same location: the name of the database becomes part of the directory tree, hence files of different databases never mix.

4.4 The iidbdb database

Every Ingres installation has a master database called iidbdb. Ingres stores information about users, locations and user databases in this database. iidbdb is created by the installer.

During installation you have to set the locations for iidbdb. These locations are stored in the following Ingres environment variables:

• II_DATABASE: data location
• II_CHECKPOINT: checkpoint location
• II_DUMP: dump location
• II_JOURNAL: journal location
• II_WORK: work location

These variables determine the default locations for every user database as well, if you don't override them when creating those databases.

Changing the value of any of the five variables requires a complete re-install of Ingres.

Let us see them one by one.

4.5 II_DATABASE

II_DATABASE determines the data location of iidbdb. Its default value is $II_SYSTEM (in case of a manual install you can enter a different value for II_DATABASE, while Express Install inevitably sets it to $II_SYSTEM).

The size of iidbdb after the installation is somewhat more than 5 Mb. It can only grow significantly if you create hundreds of Ingres users, databases or locations.

4.6 II_CHECKPOINT

II_CHECKPOINT contains the value for the checkpoint location of iidbdb. By default, it is also set to $II_SYSTEM.

The size of a checkpoint is just about the same as the size of the database itself (at least until you modify the template file of the checkpoint program: it is possible, as you will see in subsection Basic System and Database Administration/Backup and Recovery). The installer takes the first checkpoint of iidbdb.

If you plan to place checkpoints of user databases under II_CHECKPOINT then you have to provide more space here.

A further factor that must be taken into account is how long you want to keep backups. When starting the checkpoint program, you can request the deletion of older backups if you don't have too much free space.

4.7 II_DUMP

II_DUMP determines the dump location of the iidbdb database. By default, its value equals to that of II_CHECKPOINT.

By the end of the installation process, II_DUMP will contain a very small amount of data. If you always create checkpoints off-line then you won't need much space here.

4.8 II_JOURNAL

II_JOURNAL contains the value for the journal location of the iidbdb database. Its default value is the same as II_CHECKPOINT's.

The first checkpoint, taken by the installer causes the first, small journal file to appear here. If you don't use different journal locations for user databases then the necessary amount of free space under II_JOURNAL depends on three factors:

• Do you want Ingres to journal at all. If you take checkpoints of your databases regularly and don't mind losing the changes you made to them since the latest checkpoint should anything nasty happen to these databases or the transaction log, you may switch off journaling. Naturally, running a live system without journaling is usually not acceptable.
• If journaling is switched on, then the growth rate of the journal area is determined by the volume of changes made to the databases. Frequent, large updates require quite a bit of space under II_JOURNAL.
• The third factor is, how long you wish to keep old journal files. If, when taking a checkpoint, you instruct ckpdb to delete the old checkpoints, then previous journal files will be removed as well.

4.9 II_WORK

II_WORK determines the work location of the iidbdb database. It also defaults to II_CHECKPOINT.

The problem of sizing the work location only arises if II_WORK serves as a work location of user databases as well. It is next to impossible to estimate the temporary disk space that will be needed; however, having the size of the largest table multiplied by three should work as a starting point.

Remember that a database can have more than one work location. If the original location turns out too small, you can always extend the database to further work locations.

4.10 Other Ingres Environment Variables

Besides the Ingres environment variables that determine locations there are a couple more of them you have to set during installation (or have Express Install set them to their default values). These are:

• II_INSTALLATION: a two-character code, identifying the installation. If you create multiple Ingres installations on the same machine, each of them must have its own unique installation code. The default value for II_INSTALLATION is II. Once set, it can't be changed.
• II_NUM_OF_PROCESSORS: number of processors in the machine. By default, it is 1. If you set it to a higher value, Ingres will use spin-locks when accessing the database cache. If you don't know exactly what spin-locks are, don't bother. The point is to set II_NUM_OF_PROCESSORS to 2 if you have a multiprocessor machine. Its value can be changed at any time later.
• II_CHARSET: this variable determines the code set of all character data stored in all databases you will create in the installation. Its default value is ISO-8859-1. Perhaps it's not surprising that changing it to a different value after installation may corrupt data stored in your existing databases. As the iidbdb database is created by the installer program, you shouldn't choose Express Install if ISO-8859-1 doesn't suit you.
• II_TIMEZONE_NAME: name of the time zone, by default NA-PACIFIC. During manual install you can select its value from a list of valid codes. Ingres stores all date and time values in GMT and adjusts them according to II_TIMEZONE_NAME when communicating with the client. Therefore, if you set II_TIMEZONE_NAME to a different value, you will see all date-time values in the database change. For this reason, set this variable to its final value before creating the first user database.

The (manual) installer prompts you for the value of two further parameters which aren't Ingres environment variables:

• Maximum number of concurrent users in the system: this is 32 by default. Based on this number, the installer sets the value of a number of other parameters, such as the size of the database cache. These derived parameters can later be adjusted.
• SQL-92 compatible databases: by default, Ingres databases differ from the SQL-92 standard in some ways. For example, object names not protected by single or double quotes are converted to lower case rather than upper case. You can find the other differences in the SQL Reference Guide.

After you have made up your mind on the values of all installation parameters, you now know whether the default values for the variables that can't be changed after installation are acceptable to you. If they are, you can choose Express Install.