Locations and Areas

Each database file type (data, checkpoint, journal, and so on) is associated with a location, which maps to a specific disk volume or directory, called an area.

Default Locations

During installation, default storage locations and underlying areas are established for each type of database file.

When you create a database, the Ingres default locations are assumed unless you specify alternate locations.

The following table shows the default locations and the Ingres environment variables that identify the areas to which the locations are mapped:

In each case, the Ingres environment variable points to a specific disk volume or directory that has a particular structure, which is shown in the following tables.

Windows:

For example, using the default location for data files causes them to be stored in the ii_database\ingres\data\default directory, where ii_database is the value displayed by the ingprenv command for the II_DATABASE environment variable.

UNIX:

For example, using the default location for dump files causes them to be stored in the ii_dump/ingres/dmp/default directory, where ii_dump is the value displayed by the ingprenv command for the II_DUMP environment variable.

VMS:

For example, using the default location for work files causes them to be stored in the ii_work:[INGRES.WORK] directory, where ii_work is the value displayed by the show logical command for the II_WORK environment variable.

Alternate Locations

You can use alternate locations for a new database, but first you must create the area (directory structure) where the files will be stored, and then define their location.

You create a location's area using the facilities of the host operating system.

Each area must have a specific subdirectory structure, depending on the file types with which it is associated. This structure parallels that of the corresponding default location area, as summarized in Default Locations.

Create an Area in Windows

An area must be created before you can define an alternate location for a new database.

Note: If you use the extenddb command with the –aarea_dir flag, the area is created for you. You do not have to create the directory path below the ingres root directory.

To create an area in Windows, follow these steps:

1. Change location to the drive and directory where you create the new directory structure. For example, to create the new directory structure on the D: drive under the \otherplace directory, issue the following commands at the command prompt:

   D:
   cd \otherplace

2. Create a new subdirectory. For example, to make a subdirectory named new_area, issue the following command at the command prompt:

   mkdir new_area

3. Create subdirectories for the types of database files that use the new area. For example, to create a subdirectory for data files in new_area, issue these commands at the command prompt:

   mkdir new_area\ingres
   mkdir new_area\ingres\data
   mkdir new_area\ingres\data\default

   To make subdirectories for checkpoint, journal, dump, or work files, substitute ckp, jnl, dmp, or work for data when issuing these commands.

In these steps, you created the area D:\otherplace\new_area, which you can now specify as the Area when defining a new location using the Create Location dialog in VDBA. The subdirectories you created in Step 3 determine which Usage Types you can select in this same dialog (and in the Alter Location dialog). For example, creating ingres\data\default allows you to enable Database as a Usage Type, and creating ingres\work\default allows you to enable Work as a Usage Type.

Create an Area in UNIX

An area must be created before you can define an alternate location for a new database.

Note: If you use the extenddb command with the –aarea_dir flag, the area is created for you. You do not have to create the directory path below the ingres root directory.

To create an area in UNIX, follow these steps:

1. Log in as the installation owner.

   By using this account, this user becomes the owner of the subdirectories created in this procedure.

2. Change location to the directory where you create the new directory structure. For example, to create the new directory structure in the otherplace directory, issue the following command at the operating system prompt:

   cd /otherplace

   The installation owner account must be able to create a directory below this directory; this means permissions set to at least 755. If this number needs to be changed, see your system administrator. Top-level directories are usually managed by root.

3. Create a new subdirectory. For example, to make a subdirectory named new_area, issue the following command at the operating system prompt:

   mkdir new_area

4. Create subdirectories for the types of database files that use the new area. For example, to create a subdirectory for data files in new_area, issue these commands at the operating system prompt:

   mkdir new_area/ingres
   mkdir new_area/ingres/data 
   mkdir new_area/ingres/data/default

   To make subdirectories for checkpoint, journal, dump, or work files, substitute ckp, jnl, dmp, or work for data when issuing the above commands.

5. Place the appropriate permissions on the new directories and subdirectories, as shown in the following example. Limit access to the data directory to the user account for the installation owner only:

   chmod 755 new_area
   chmod 755 new_area/ingres
   chmod 700 new_area/ingres/data
   chmod 777 new_area/ingres/data/default

   To place permissions on new directories for checkpoint, journal, dump, or work files, substitute ckp, jnl, dmp, or work for data when issuing the above commands.

In these steps, you created the area /otherplace/new_area, which you can now specify as the Area when defining a new location using the Create Location dialog in VDBA. The subdirectories you created in Step 4 determine which Usage Types you can select in this same dialog (and in the Alter Location dialog). For example, creating ingres/data/default allows you to enable Database as a Usage Type, and creating ingres/work/default allows you to enable Work as a Usage Type.

Raw Area in UNIX

A raw area contains data from a single database only.

A raw location can be assigned a usage of database only, cannot be used as the root location of a database, and can contain data for one table only.

The maximum size of a table is bound by the smallest raw location to which it is assigned.

A raw area can contain many locations; each location can contain the data for one table. A raw location is the equivalent of a cooked database file, which contains data of one table only.

To set up a raw area file, use the mkrawarea utility. For more information, see the Command Reference Guide.

How You Change from Raw to Cooked (Non-raw) Transaction Log

If your installation uses a raw transaction log file and you want to change to a cooked transaction log file, follow this process:

1. Destroy the existing transaction logs, including dual logs if present.
2. Define the locations to be used for the new transaction logs.
3. Create the new transaction logs.
4. Test the new transaction logs by restarting Ingres.

Create an Area in VMS

An area must be created before you can define an alternate location for a new database.

To create an area in VMS, follow these steps:

1. Log into the VMS system account.
2. Create the top level [INGRES] directory on the new device with the protection mask set to equal (S:RWE,O:RWE,G,W:RE) and ownership set to [INGRES] by executing the following command at the operating system prompt:

   CREATE/DIR device:[INGRES]/OWNER_UIC=[INGRES] –
   /PROT=(S:RWE,O:RWE,G,W:RE)

   Substitute the name of the new device for device in the command. Also, do not set the protections any more restrictive than recommended here, because doing so can result in errors later.

3. Make sure the master file directory [000000] on the new device has at least W:E protection by executing the following command at the operating system prompt:

   DIR/PROT device:[0,0]000000.dir

   If the protection is incorrect (for example, the WORLD has no access), correct this with the following command:

   SET FILE/PROT=(S:RWE, O:RWE, G, W:E) – 
   device:[0,0]000000.dir

4. Define a logical name for the new area at the system level:

   DEFINE/SYSTEM/EXEC/TRANS=CONCEALED –
    logical_name device

   Substitute the name of the new area for logical_name. This is useful if you ever reconfigure your system or move data between systems, because it is much easier to redefine one logical than to re-point all references to a device.

   For example, the following command defines a new altarea1 for device DUA1 at the indicated subdirectory:

   DEFINE/SYSTEM/EXEC/trans=concealed –
   altarea1 dua1:[MYDIRECTORY.SUBDIRECTORY.]
   @II_SYSTEM:[INGRES.UTILITY]INGDEFDEV.COM

5. The definition in Step 4 lasts until the next system boot. Add the same DEFINE statement to SYS$MANAGER:SYSTARTUP_V5.COM or II_SYSTEM:[INGRES]IISTARTUP1.COM so that it is executed on future boots.
6. Exit the VMS system account.
7. Log in to the system administrator's account.
8. Create the subdirectories and set the appropriate protections on these directories by executing the INGDEFDEV command procedure at the operating system prompt:
9. When INGDEFDEV prompts you, provide the device name and the file type (data, journal, checkpoint, dump, or work) that resides in this area. Because you can specify only one file type each time you run INGDEFDEV, you must run INGDEFDEV once for each file type and device name pairing.

   Depending on the type of file that resides in this area, INGDEFDEV creates one of the following directories, where device is the name of the new device from Step 2:

   -device:[INGRES.DATA] (for data files)
   -device:[INGRES.CKP] (for checkpoint files)
   -device:[INGRES.JNL] (for journal files)
   -device:[INGRES.DMP] (for dump files)
   -device:[INGRES.WORK] (for work files)

In these steps, you created an area corresponding to the logical_name identified in Step 4, which you can now specify as the area name when defining a new location using the create location statement. The directories created by INGDEFDEV in Step 9 determine which usage types you can specify for both the create location and alter location statements. For example, creating [INGRES.DATA] allows you to specify usage = database, and creating [INGRES.WORK] allows you to specify usage = work.

Working with Locations

After you have created an area for a location, you must than create the location.

A location object specifies the location name, associated area, and the types of files that reside in the location.

You can perform the following basic operations on location objects:

• Create and alter location objects
• View existing location objects, including the detailed properties of each individual object
• Drop location objects

In VDBA, use the Locations branch in the Database Object Manager window. For detailed steps for these procedures, see online help for VDBA.

In SQL, you can manage locations using the create location, alter location, and drop location. For more information, see the SQL Reference Guide.

To work with locations, you need the maintain_locations privilege. This subject privilege is granted by default to the system administrator, who in turn can grant it to other users, such as database administrators, who need to manage locations. For more information on subject privileges, see the chapter "Ensuring Access Security."

Guidelines for Using Locations

After you have set up the underlying area and mapped it to a location by creating a location object, use the new location as summarized below:

• When you create a new database, specify the location for the database's data dump, checkpoint, journal, and work files.
• Extend a database to include the new location for its data and work files.
• After extending a database to use an alternate location designated for data files, move existing user data files (that is, user tables and indexes, but not the system catalogs) to it, and place new user data files in it. For more information, see the chapter "Managing Tables and Views."
• The following file types can use only a single location (that is, these file types are not affected when you extend a database):
  • Checkpoint files
  • Journal files
  • Dump files
• The initial location of the following file types is determined when you create a database, but you can move each to a new location if the need arises:
  • Checkpoint files
  • Journal files
  • Dump files
• Store the data, checkpoint, journal, dump, and work files for a database in the same locations or in different locations.
  • If the default locations are used when you create the database, all these files are stored in the same area.
  • We strongly recommend that you store data files on a different disk from those used to store checkpoints, journals, and dumps. Doing so helps to protect your data in the event of disk failure and to maximize disk drive performance.

The following table summarizes some of these guidelines:

Work Locations

All databases use work files for sorting, which can occur when queries are executed (select or fetch statements with order by clauses) or when tables are restructured (for example, using the modify statement or equivalent operation in VDBA). While small sorts are performed in memory, larger sorts use temporary sort files. Depending on the size of the tables involved in the sort, the temporary disk space requirements can be large.

Initial and Extended Work Locations

You specify the initial, or primary, location (or use the default location) for work files when you create a database. The area mapped to this location is used for all work files.

To use additional locations, extend a database. When you extend a database in this manner, sort space can be spread among multiple work locations.

Note: We recommend that you put work locations on scratch disks so that sorting activity does not contend with other database I/O and data disks do not become excessively fragmented.

Classification of Extended Work Locations

When extending a database to use additional work locations, classify them as follows:

• Work (also known as defaultable) locations are used for all user sorts on a database.
• Auxiliary locations are not used unless explicitly requested by a set work locations statement.

After a database has been extended to use an additional work location, you can subsequently modify the work area's classification using the Alter Database dialog in VDBA.

Work Locations for a Session

A session automatically uses all defaultable work locations to which the database has been extended (including the initial work location). In addition, the session can issue set work locations statements to specify auxiliary work locations to use. Using this statement, a session can dynamically add and drop work locations and replace the set of locations currently in use. The set work locations statements affect the current session only—their effects disappear when the session ends.

For more information on using set work locations, see the entry for the set statement in the SQL Reference Guide.

Note: To list the set of work locations used in a given session, you can use a trace point, DM1440. For information on setting trace points, see the System Administrator Guide.