Re: What is a TABLESPACE?

From: Blair Kenneth Adamache <adamache_at_ca.ibm.com>
Date: Sat, 24 Mar 2001 09:15:50 -0500
Message-ID: <3ABCAC16.274D7BE3@ca.ibm.com>

A tablespace can hold one or more tables, and indexes and/or the long fields (CLOBs, BLOBs, LONG VARCHARs) for a table. It maps the logical data (tabular view) to the physical storage devices (files or containers on a file system).

By default, tables you create are created in the tablespace USERSPACE1. If you want to have more control of where your data is placed, you have to created additional table spaces.

On DB2 for OS/390, some DBAs like to put each table in its own tablespace. On DB2 on UNIX, OS/2 and Windows, the common practice is to put all related tables (those with referential constraints against each other) in the same tablespace.

Combined with the CREATE BUFFERPOOL command, the CREATE TABLESPACE command gives you a lot of control of your data, including:

- whether the maximum row width is 4K, 8K, 16K or 32K
- the maximum number of columns in a table
- whether the table data is stored on raw devices or on the file system
- the ability to restore a tablespace from a backup image rather than a full

database backup image
- the ability to give a table a better chance of being cached in memory by assigning it to its own bufferpool

Wyvette Banks wrote:

> Hi,
>
> I am a novice in the Database world. What is a TABLESPACE, and when do I
> use it?
>
> I was creating a Table in DB2 and it gave me two default choices for a
> TABLESPACE name:
> 1. None
> 2. USERSPACE1
> What is happening if I choose None or if I choose USERSPACE1? I noticed
> this in Oracle as well, so image this is a DBMS thing.
>
> Gil
Received on Sat Mar 24 2001 - 08:15:50 CST