Tablespace Creation in DB2

Creating Table Space:

            It is an object used to specify the physical location of data is known as table space. A tablespace is used as a layer between the database and the container objects that hold the actual table data. A table space can contain more than one table.

            When we create tablespace, you can associate it with a specific buffer pool (database cache) as well as to specific containers. This gives you flexibility in managing performance.

            Backup and recovery can be performed at the tablespace level.

       
            In DB2 UDB three default table spaces are created automatically upon  database creation by using CREATE DATABASE command.

          SYSCATSPACE    -    Catalog tablespace containing metedata(table definition,attributes..) 

          TEMPSPACE1      -    System temporary table space used to perform operations                                such as joins and sorts. The name of this table space can be changed.
          USERSPACE1      -    This table space can be optional and can be used to store
                                 user table when a table space is not explicitly indicated at creation time.
         SYSTOOLSPACE -   While working on HADR, REPLICATION the SYSTOOLSPCE is                                                  created

2-Types of table spaces.

            1.  SMS (System Managed Space).

            2.  DMS (Database Managed Space).
      

            SMS: SMS table space are managed by the operating system, The SMS provides good                              performance with minimum administration.

            DMS: DMS table space are managed by DB2 UDB, and can be files or raw devices.
                    

DB2 UDB database or table space

SYSCATSPACE (catalog table space), this information is kept only at the database level.

System catalog Table (identified by the SYSIBM schema) and System views (identified by the SYSCAT or SYSSTAT schema) and reside inside the SYSCATSPACE table space

SAMPLE database

System Temporary table space (By default it is called tempspace1)

N/A

User table space. By default, USERSPACE1 is normally created after database creation.

          

Regular tablespace: 

Table containing  user data exist in one or more regular table spaces. By default table space is called USERSPACE1.

The System catalog tables exist in regular table space, as well indexes are also stored in regular tablespace. By default system catalog tablespace is called SYSCATSPACE.

The maximum size of a regular table space (including index data) depending up on the page size used by the table space.

            (Ex:-Maximum is 64 GB for 4KB pages and 128 GB for 8KB pages )

 Large Table Space:

Table containing long data or long object data, such as multimedia objects. exist in one or   many long table spaces. Long data can also reside in regular table spaces.

            Long table spaces must be DMSs. The maximum size of a long table space is 2 TB.

       

System Temporary Table Space:

Temporary table spaces are used by the database manager during SQL operations for holding transient data such as immediate tables during sort operations, reorganizing tables,           creating indexes, and joining tables. A database must have atleast one temporary table space. By default SMS table space called TEMPSPACE1 is created when the database created. It can be dropped after another temporary table space created. Temporary table space can be either SMS or DMSs. ( Better to keep in SMS). The maximum SIZE of a temporary table space is 2 TB.

 

CREATE TABLE SPACE:

            Syntax

CREATE <REGULAR/LARGE> TABLESPACE <Table Space Name>

PAGE SIZE [4K/8K/16K/32K]

MANAGED BY SYSTEM

USING FILE <Path of the container(s)>

EXTENT <extent size>

PREFETCH <no of pre-fetch pages>

BUFFERPOOL <Buffer Pool Name>

 

Example:         CREATE TABLESPACE TSPNAME1

                                    PAGESIZE 4096

                                    MANAGED BY DATABASE USING (FILE '/home/itlokam/TSP1.dat' 8001)

                                    EXTENTSIZE 8

                                    PREFETCHSIZE 16

                                    BUFFERPOOL BPCERT4K

 

CREATE TABLE:

 

            CREATE TABLE EMPTABLE1

                                             (

                          COL1             CHAR(10) NOT NULL

                                                 COL2              INTEGER NOT NULL
                                              ) IN TSPNAME1 INDEX IN USERSPACE1

DROP TABLE SPACE:       DROP TABLESPACE TSPNAME1, USERSPACE1               

 

EXTED TABLE SPACE SIZE:

 

]$ db2 "ALTER TABLESPACE TSPNAME1 EXTEND (FILE '/home/itlokam/TSP1.dat' 9000)"