Placing tables on disk

Tables that the database server supports reside on one or more portions of one or more disks. You control the placement of a table on disk when you create it by assigning it to a dbspace.

Tables that the database server supports reside on one or more portions of a disk or disks. You control the placement of a table on disk when you create it by assigning it to a dbspace. A dbspace consists of one or more chunks. Each chunk corresponds to all or part of a disk partition. When you assign chunks to dbspaces, you make the disk space in those chunks available for storing tables or table fragments.

When you configure chunks and allocate them to dbspaces, you must relate the size of the dbspaces to the tables or fragments that each dbspace is to contain. To estimate the size of a table, follow the instructions in Estimating table size.

The database administrator (DBA) who is responsible for creating a table assigns that table to a dbspace in one of the following ways:
  • By using the IN DBSPACE clause of the CREATE TABLE statement
  • By using the dbspace of the current database

    The most recent DATABASE or CONNECT statement that the DBA issues before issuing the CREATE TABLE statement sets the current database.

The DBA can fragment a table across multiple dbspaces, as described in Planning a fragmentation strategy, or use the ALTER FRAGMENT statement to move a table to another dbspace. The ALTER FRAGMENT statement provides the simplest method for altering the placement of a table. However, the table is unavailable while the database server processes the alteration. Schedule the movement of a table or fragment at a time that affects the fewest users.

Other methods exist for moving tables between dbspaces:

  • You can unload the data from a table and then move that data to another dbspace with the SQL statements LOAD and UNLOAD, the onload and onunload utilities or the High-Performance Loader (HPL).
  • You can unload the data from a table and then move that data to another dbspace with the SQL statements LOAD and UNLOAD or the onload and onunload utilities.
  • You can load data into and unload data from external tables.

Moving tables between databases with LOAD and UNLOAD, onload and onunload, or HPL involves periods in which data from the table is copied to tape and then reloaded onto the system. Moving tables between databases with LOAD and UNLOAD or onload and onunloadinvolves periods in which data from the table is copied to tape and then reloaded onto the system. These periods present windows of vulnerability during which a table can become inconsistent with the rest of the database. To prevent the table from becoming inconsistent, you must restrict access to the version that remains on disk while the data transfers occur.

Depending on the size, fragmentation strategy, and indexes that are associated with a table, it can be faster to unload a table and reload it than to alter fragmentation. For other tables, it can be faster to alter fragmentation. You can experiment to determine which method is faster for a table that you want to move or re-partition.