DB2 Table Space Types and its usage

Table Space concept in DB2:

Table space is a database object where actually the table data gets stored. It is a like child of the database, where there can be multiple table spaces within a database. Each table space contains the ‘containers’. There can be one or more containers per table space. Containers can be added/dropped to TS to increase the size, if it is a DMS managed TS.

There are different types of table spaces depending upon their usage and management type.

Depending upon their management type there are types of Table Spaces:

• DMS (database managed space)- Here DB2 manages the table spaces
• SMS (System managed Space)

According to their usage table spaces can be classified in to 5 names:

• Catalog Table Space
• Regular Table space
• Large Table Space
• System Temporary Table Space
• User Temporary Table Space

Catalog Table Space: This is used to store all the catalog tables maintained by the DB2 database. Generally this Table space gets created at the time of Database creation and named as SYSCATSPACE.

Regular Table space: This table space is used to store all the permanent table data and indexes. This can be either DMS or SMS.

Ex: Catalog TS is one of the regular TS, and catalog TS is the only TS created during the database creation and all the table spaces will be created later.

Normal table spaces which are used to store the application wise table data are also comes into this type of TS.

Large Table space: There are used to store the LOBs, it can also store the regular data as that of Regular TS. Large TS should be of DMS. DB2 creates one large TS named USERSPACE1 at the time of database creation.

System Temporary Table Space: This is used to store all the temporary data during SQL process such as Sorting, reorganizing, joining etc. There must be at least one Temporary TS per database; the default one created at the time of database creation is TEMPSPACE1.

User temporary Table Space: These can be used to store the declares temporary tables. These are not mandatory, can be created if necessary.

Fields that are specified during the Table space creation are:

• Page Size: Specifies the size of each page. There can be 4k, 8k, 16k & 32k page sizes.
• Extent Size: Specifies the number of pages to be loaded in to each container before loading it to next available container.
• Prefetch size: Prefetch fetches the pages from database before the SQL gets executed, so that the wait time gets reduced. Prefetch size is the number of pages to be read from the database.                                                                                                Find detailed info on prefecth @ http://www.mainframezone.com/it-management/the-magic-of-db2s-prefetch/P4
• Overhead and transfer rate: Determines the cost of I/O during query optimization

Depending upon their structure table spaces can be classified as below:

• Simple Table Space
• Partitioned Table Space
• Segmented Table Space
• Large Table Space
• XML table space

STS- It is the initial table space in the early days of Db2. It can contain more than one table in a single page. There is no mechanism to separate to table rows. So whenever an SQL gets executed it scans all the rows from all the tables that are there in a page.

Ex: If a simple table space contains 4 tables in it. Suppose you were trying to load one table (you have specified the option Replace) in that four, then data will be stored in that one table and remaining tables will get empty. This is the disadvantage of Simple table space. It will be useful if specify only one table per table space.

Partitioned TS: Table space is divided into partitions; it allows only one table per Table space. This table space more useful in case of large tables with huge data. The tables can be defined with partition key, so that depending upon that key, table space gets partitions.

Ex: A table that contains the data of Cities and for every city it has huge data.

If we define the table with city as partition key. Then each city information will be stored in separate Partition. So that the one can access/update one city and other can access/update different city information. Means it allows the partition level locking.

Segmented TS- This table space is divided in to equal parts called ‘segments’. Segment is the group of pages in the table space. Each segment contains rows from only one table. But these TS can contain rows from different tables in different segment. If any SQL executes, the table space scan can be done at segment level, thus we can avoid unnecessary overhead. Segment Level Locking also supported.

Large TS: Hold the large object data such as videos, graphics and large strings etc.

XML TS: Holds the XML data