Alter Location

Valid in: SQL, ESQL

The Alter Location statement changes the type of files that can be created at an existing location.

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

Syntax

The Alter Location statement has the following format:

[EXEC SQL] ALTER LOCATION location_name
              WITH USAGE = (usage_type {, usage_type}) | NOUSAGE

• location name

  Specifies the name of an existing disk and directory combination.

• usage_type

  Specifies the types of file that can be stored at this location. Valid values are:

  • DATABASE
  • WORK
  • JOURNAL
  • CHECKPOINT
  • DUMP
  • ALL
• NOUSAGE

  Prevents any files from being stored at the location.

Embedded Usage

In an embedded Alter Location statement, the usage portion of the with clause can be specified using a host string variable. The preprocessor does not validate the with clause.

Permissions

You must have maintain_locations privileges and be connected to the iidbdb to issue this statement.

Locking

The Alter Location statement locks pages in the iilocation_info system catalog.

Related Statements

Create Location

Drop Location

Examples: Alter Location

The following examples change the type of files that can be created at an existing location:

1. Specify that only checkpoint files can be created at the checkpoint_disk location.

   alter location checkpoint_disk
   with usage = (checkpoint);

2. Prevent any files from being created at the new_db location.

   alter location new_db with nousage;