Table Spaces, Tables, and Indexes

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:

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\ ). The other table space will reside in one of the previously defined stogroup directories ( d:\stor1 ).

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;