Positioning Tables

Restriction: This topic applies to Windows environments only.

Each database table can be associated with a physical device (drive and path) by using a stogroup and table space concept similar to that of DB2.

A stogroup is defined and associated with a physical device (drive and path). A database is then created using a particular stogroup. By default, any table subsequently created within that database is placed physically on the device (drive and path) specified by the database's stogroup.

To distribute tables to various disk drives, table spaces and stogroups are created. Each table space is associated with a stogroup, which in turn is associated with a drive and path. When a table is created, a table space can be specified, thus physically placing the table on the drive and path that is associated with the named table space.

For example, the following CREATE commands will create two stogroups (named STOR1 and STOR2) on two separate disk volumes (named MINE and YOURS). The UPDATE commands will define the disk drive and directory for each. (The directories must be created ahead of time using the operating system MAKE DIRECTORY (MD) command.)

CREATE STOGROUP stor1 VOLUMES (mine) VCAT xdb;
CREATE STOGROUP stor2 VOLUMES (yours) VCAT xdb;
UPDATE SYSIBM.SYSVOLUMES 
    SET XdbPrimeVolPath = "y" , 
        XdbVolPath = "d:\stor1\" (Windows) or 
			    "/stor1/" (UNIX) 
    WHERE Volid = "mine";
UPDATE SYSIBM.SYSVOLUMES   
    SET XdbPrimeVolPath = "y" , 
        XdbVolPath = "e:\stor2\" (Windows) or 
			    "/stor2/" (UNIX)
    WHERE Volid = "yours";

Note: The fields XdbPrimeVolPath and XdbVolPath are XDB Server extensions to the SYSIBM.SYSVOLUMES table. These columns do not appear in DB2.

To complete the example, you would use CREATE commands such as the following to specify storage locations for objects you create, in this case tables.

CREATE DATABASE xyz STOGROUP stor1;
CREATE TABLESPACE abc in DATABASE xyz 
    USING STOGROUP stor2;
CREATE TABLE firsttable 
    (fld1 SMALLINT, fld2 CHAR 1) 
    IN xyz;
CREATE TABLE secondtable 
    (fld1 SMALLINT, fld2 CHAR 1) 
    IN xyz.abc;

The table FIRSTTABLE will reside on d:\stor1 (Windows) or /stor1 (UNIX) because it uses the stogroup defined for the database. The table SECONDTABLE will reside on e:\stor2 (Windows) or /stor2 (UNIX) because it uses the stogroup defined for the table space. If desired, you can create a separate table space for each table, each with its own associated stogroup/path.

To move a table to a different device, copy the *.tab file to the desired drive and path, and update the path name for the associated stogroup in the SYSIBM.SYSVOLUMES table. If the index files (*.idx and *.ptr) are stored with the tables and do not have special paths defined for them in environment variables, be sure to copy them along with the related table. (See the section Positioning Indexes for more information about moving indexes.)

See the SQL Reference for more information about the storage architecture (including examples) and the commands shown here.