Table Spaces, Tables, and Indexes

Restriction: This topic applies to Windows environments only.

On a mainframe system, table spaces and stogroups provide a means of organizing tables and indexes on specific disk storage devices. Generally, each table space and index is associated with a stogroup. A table can be created within a specific table space, which associates the table with the stogroup of the table space. When an index is created, it can be associated with a stogroup as well.

The XDB Server supports the table space/stogroup structure, and provides syntactical/semantic support for relating an index to a stogroup.

Note: Use of table spaces and stogroups is optional. If you choose to specify table spaces, you can do so with or without stogroups.

If you have defined stogroups in an XDB database, you can specify which stogroup should be used when you create a table space. For example:

CREATE TABLESPACE brspace 
    IN databas1 USING STOGROUP stor1;

When you create tables and indexes in a database, use either or both of the following methods:

Allow the XDB Server to define table spaces and directory paths for the tables and indexes you create. For example:
```
CREATE TABLE branch 
    ( fld1 SMALLINT, fld2 CHAR 1 ) 
    IN databas1; 
CREATE INDEX brindex ON branch(fld1); 
```
If the database has a stogroup defined, the XDB Server uses its path to store the table and index files.

If the database does not have a stogroup defined, the XDB Server uses the SYSDEFLT stogroup routine which uses the ..\location-name\database-name\ (Windows) or ../location-name/database-name/ (UNIX) path structure. In this situation, the XDB Server creates table and index files under the database-name subdirectory to store each table and index. For example:
Windows:
- c:\xdb\locat1\databas1\branch.tab
- c:\xdb\locat1\databas1\brindex.idx
- c:\xdb\locat1\databas1\brindex.ptr
UNIX:
- /xdb/locat1/databas1/branch.tab
- /xdb/locat1/databas1/brindex.idx
- /xdb/locat1/databas1/brindex.ptr
The table space name will be the first eight characters of the table or index name, as appropriate. If the eight character name would duplicate an existing table space name, the system creates a unique name for the table space.
Specify a pre-defined table space for a table. The index will be placed in the table space where its associated table resides. For example:
```
CREATE TABLE branch 
    ( fld1 SMALLINT, fld2 CHAR 1 ) 
    IN databas1.brspace; 
CREATE INDEX brindex ON branch(fld1); 
```
In this case, all tables you create with the named table space will be stored under the stogroup directory that was defined for the table space. Indexes created on those tables will be stored in the same stogroup path.

For example:
Windows:
- d:\stor1\branch.tab
- d:\stor1\brindex.idx
- d:\stor1\brindex.ptr
UNIX:
- /stor1/branch.tab
- /stor1/brindex.idx
- /stor1/brindex.ptr

Note:

If your database is the default DSNDB04 database, you do not have to specify the database name in the above commands.

Any table you create without specifying a database name will be placed in DSNDB04 automatically.

To summarize, when you create a table, you usually specify the database in which you want it to be created. Optionally, if you created table spaces in the location, you can specify a table space name in which to store the data. The system catalog keeps track of which tables belong to each database and where they are located on disk.

When you create an index, you specify the table to which it applies. Again, the system catalog keeps track of which indexes belong to each database and table, and where they are located on disk.

See the SQL Reference for detailed information regarding the storage structure and naming conventions that apply when creating various database objects with and without stogroups.

Example

In this example, a database and two table spaces are created in the current location. The first table space does not specify a stogroup, and thus the default database subdirectory will be used to store tables for this table space ( c:\xdb\locat1\dbname\ (Windows) or /xdb/locat1/dbname/ (UNIX)). The other table space will reside in one of the previously defined stogroup directories ( d:\stor1 (Windows) or /stor1 (UNIX)).

Notice that when tables are defined, they are qualified with an IN clause to specify to which database they belong (and optionally, which table space should be used to store the physical data). In this example, the first two tables use specific table spaces. The third table is defined without a table space. The index for the BRANCH table will reside in the same table space as the BRANCH table.

CREATE DATABASE databas1;
CREATE TABLESPACE brchspc IN databas1;
CREATE TABLESPACE acctspc IN databas1 
    USING STOGROUP stor1;
CREATE TABLE branch ( 
           BRANCH_ID   INTEGER, 
           BRANCH_BAL  DECIMAL(15,2) ) 
    IN databas1.brchspc;
CREATE INDEX BIDX ON BRANCH(BRANCH_ID);
CREATE TABLE account ( 
           ACCT_ID     INTEGER, 
           BRANCH_ID   INTEGER, 
           ACCT_BAL    DECIMAL(15,2) ) 
    IN databas1.acctspc;
CREATE TABLE history ( 
           ACCT_ID     INTEGER, 
           BRANCH_ID   INTEGER, 
           ACCT_BAL    DECIMAL(15,2) ) 
    IN databas1;