Positioning Indexes

An XDB index can be stored on a physical device different from the device where its related tables are stored by using an environment variable and an extension to the VCAT parameter of the CREATE INDEX command.

• To store each index on its own path, use the XDB_index-name_ location-name environment variable to specify the path for storing each index. Then create each index using the VCAT NAME option on the CREATE INDEX command (using the index-name specified in the environment variable). If a qualifier is required for the index, you can enter a two-part name such as XDB_eric.orderidx_location1.
• To store all indexes for a given location on the same path, use the XDBIDX_location-name environment variable to specify the path for storing all indexes for the location. Then create indexes for the location using the VCAT PATH option on the CREATE INDEX command.

For example:

1. Enter the environment variable XDB_index-name_location-name in the computer's configuration file to specify a drive and path. You can create one or more index paths, such as:

   SET XDB_customerindex_location1= d:\indexes\customer

   SET XDB_ordersindex_location1= d:\indexes\orders

2. Reboot.
3. Create the indexes with the VCAT NAME parameter, as follows:

   CREATE INDEX customerindex ON customer(c_no) 
       USING VCAT NAME;

   CREATE INDEX ordersindex ON orders(o_no) 
       USING VCAT NAME;

   The VCAT NAME option causes the XDB Server to check the environment variable for the drive and path to use when creating the index. XDB index files are placed on the path associated with the environment variable for that particular index.

To move an index to a different device, simply copy the *.idx and *.ptr files to the desired drive and path, and change the path specification in the XDB_index-name_location-name or the XDBIDX_location-name environment variable. If the index path is not specified in an environment variable, then the index files must be in the same path as the related table.

See the CREATE INDEX command in the SQL Reference for more information about VCAT NAME and VCAT PATH. See the chapter Environment Variables for more information about the index environment variables.