Tablespaces in DB2

Tablespaces in DB2

1. Database consists of Tables and Indexes which are logical entities as we don't find any particular files on file system with tabular formatted data.
2. Question of how to save the tables and Indexes is solved by Tablespaces.
3. Tablespaces can be defined as configuration file which configures
1. Page size to be used for the tables with it.(4k,8k,16k AND 32k)
2. Type of / Name of Buffer pool to be used while loading data.
3. No. of pages to be stored in an container( i.e Extent size)
4. No. of pages to be read during prefetch operation (i.e prefetch size)
4. Tablespaces are  composed of one or more containers which could be a directory or file on file system or an RAW device.
5. Multiple tablespaces can share one bufferpool until their page size matches.
6. There are four types of tablespaces
1. REGULAR
1. It is used for storing tables and indexes
2. LARGE
1. It is used for storing Large object such as CLOB or BLOB. Generally a table is partly saved in REGULAR tablespace and its large object column is configured to be saved in LARGE tablespace
3. SYSTEM TEMPORARY
1. It is internally used by DB2 for Sorting and joins operations.
4. USER TEMPORARY
1. It is used for storing User defined Global temporary tables.
7. Table spaces can be set up in different ways, depending on how we want to use available storage.
1. We can have the OS manage allocations of space, or 
2. We can have the database manager allocate space for your data, based on parameters you specify. Or 
3. We can create table spaces that allocate storage automatically.
8. The three types of table spaces manager are known as:
1. System managed space (SMS), in which the operating system's file manager controls the storage space once you have defined the location for storing database files.
2. Database managed space (DMS), in which the database manager controls the usage of storage space one you have allocated storage containers.
3. Automatic storage table spaces, in which the database manager controls the creation of containers as needed.
9.  Both SMS and DMS are deprecated in version 10.1 onwards and many be removed in the future release, where as Automatic storage tablespaces are being used for storing user data.
10. Bydefault, while creating tablespace, if non of the options are specified then automatic storage management is used for that particular tablespace.
11. Following are some of the ways for creating automatics storage tablespace
1. CREATE TABLESPACE TS1
2. CREATE TABLESPACE TS2 MANAGED BY AUTOMATIC STORAGE
3. CREATE TEMPORARY TABLESPACE TEMPTS
   CREATE USER TEMPORARY TABLESPACE USRTMP MANAGED BY AUTOMATIC STORAGE
4. CREATE LARGE TABLESPACE LONGTS
5. CREATE TABLESPACE TS3 INITIALSIZE 8K INCREASESIZE 20 PERCENT MANAGED BY AUTOMATIC STORAGE
6. CREATE TABLESPACE TS4 MAXSIZE 2G 
7. CREATE TABLESPACE TS5 USING STOGROUP SG_HOT