Using Alternate Locations
Locations and Areas
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.
Default Locations
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.
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.
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:
ingprenv II_WORK
Create an Alternate HDFS Location
When you create a database, the default locations are assumed unless you specify alternate locations.
To create an alternate location for a new database
1. Create the area (directory structure) where the files will be stored by using HDFS system commands.
$ hdfs dfs ‑mkdir /Actian/new_area
$ hdfs dfs ‑chown /Actian/new_area
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);
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, 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.
Alter or Drop a Location
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
ALTER LOCATION invoix WITH USAGE = (WORK);
Current use of the location is unaffected but attempts to extend a database to the target location are constrained by the new usage setting.
To drop the location “duplicate”
DROP LOCATION DUPLICATE;
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.
Work 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.
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 and spilling to disk activity does not contend with other database I/O and data disks do not become excessively fragmented.
Extend and Unextend a Database
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.
To extend a 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:
extenddb -lextraloc1 stockdb -a/disk1/loc1 -Udata,work
This command extends the employeedb database to an existing location:
extenddb -lextraloc2 -Udata employeedb
This command creates a location without extending databases to it:
extenddb -lextraloc2 -a/disk2/loc2 -Uckp,jnl -nodb
To unextend a database
Use the unextenddb command.
For example, this command unextends the stockdb database location extraloc1:
unextenddb -lextraloc1 stockdb -Udata,work
Create a Database in an Alternate HDFS Location
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.
To create a database in a location other than the default
1. Create the location. For example:
CREATE LOCATION vwhdfs WITH AREA = 'hdfs://mydatanode:9000/Actian/vwhdfs', USAGE = DATABASE;
2. Issue a createdb command. For example:
createdb sales_fact -vvwhdfs
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.