8. Using Alternate Locations

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

During installation, default storage locations and underlying areas are established for each type of database file. When you create a database, the default locations are assumed unless you specify alternate locations.

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

File Type	Location Name	Area
Data	ii_database	II_DATABASE
Checkpoint	ii_checkpoint	II_CHECKPOINT
Journal	ii_journal	II_JOURNAL
Dump	ii_dump	II_DUMP
Work	ii_work	II_WORK
HDFS Data	ii_hdfsdata	II_HDFSDATA
HDFS Backup	ii_hdfsbackup	II_HDFSBACKUP
HDFS Work	ii_hdfswork	II_HDFSWORK

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

File Type	Structure on Linux	Structure on Windows
Data	ingres/data/default	ingres\data\default
Checkpoint	ingres/ckp/default	ingres\ckp\default
Journal	ingres/jnl/default	ingres\jnl\default
Dump	ingres/dmp/default	ingres\dmp\default
Work	ingres/work/default	ingres\work\default
HDFS	ingres/data/vectorwise	N/A

For example, using the default location for work files causes them to be stored in the ii_work/ingres/work/default directory, where ii_work is the value for the II_WORK environment variable, which is displayed using the command:

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

1. Create the area (directory structure) where the files will be stored by using HDFS system commands.

The area “/Actian/new_area” is created on the HDFS file system, which you can specify as the Area when defining a new location using the CREATE LOCATION statement.

2. Map the area created in Step 1 to a location by using the CREATE LOCATION statement.

For example: Connect to the database and type this statement to create a location “myloc1” in the area “\hdfs://mynamenode:9000/Actian/new_area” for database file usage.

CREATE LOCATION myloc1 WITH AREA = 'hdfs://mynamenode:9000/Actian/new_area', USAGE = (DATABASE);

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, checkpoint, 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, you can create tables and indexes in that location.

• The checkpoint files can use only a single location (that is, they are not affected when you extend a database).

• The initial location of checkpoint files is determined when you create a database, but you can move them to a new location if needed.

• Store the data, checkpoint, and work files for a database in the same locations or in separate 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. Doing so helps to protect your data in the event of disk failure.

• The database uses for temporary storage the first work location that it is extended to. If a database is extended to multiple data locations, data of a table is written to all assigned locations in a round robin fashion. If one location runs out of disk space, an error is generated and the operation stops even if the other locations have free space.

After a location is created, it can be altered or dropped. The ALTER LOCATION statement changes the type of files that can be created at an existing location.

To alter the location “invoix” in “area2” to allow usage as a work area only

Current use of the location is unaffected but attempts to extend a database to the target location are constrained by the new usage setting.

Note: You can only drop a data or work location if no currently existing database has been extended to it. You cannot drop other types of locations.

All databases use work files for sorting, joins, aggregations, and buffering and reusing of repeating query parts that spill to disk, which can occur when queries are executed. 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.

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 and spilling to disk activity does not contend with other database I/O and data disks do not become excessively fragmented.

You can extend a database to use additional data and work locations. Locations must exist prior to this operation and must be specified with a Usage Type of database or work.

After extending to another data location, you can create new tables and indexes in the extended location.

Unextending a database reverses the extend operation and deletes the entry from the configuration files so the location can be used again.

Note: After unextending a database location, you should back up the database. Previous backups (checkpoints) cannot be used because they reference a location that is no longer accessible to the database.

Use the extenddb command or Actian Director.

This command extends the stockdb database to use the directory /disk1/loc1 as new data and work areas:

This command extends the employeedb database to an existing location:

This command creates a location without extending databases to it:

Use the unextenddb command.

For example, this command unextends the stockdb database location extraloc1:

You can create a database in an HDFS location other than II_HDFSDATA by using the ‑v flag on the createdb command. This location becomes the new default HDFS location for this database.

CREATE LOCATION vwhdfs WITH AREA = 'hdfs://mydatanode:9000/Actian/vwhdfs', USAGE = DATABASE;

In this example, the sales_fact database is created in a new HDFS location for Vector data: vwhdfs. The location was defined previously with the CREATE LOCATION statement.