Interview Questions   Tutorials   Discussions   Programs   

DB2 - Name the different types of Table spaces.

asked mar August 20, 2014 12:21 PM  

Name the different types of Table spaces.


1 Answers

answered By Mswami   0  
DB2 table spaces:

A DB2 table space is a set of volumes on disks that hold the data sets in which tables are actually stored. All tables are kept in table spaces. A table space can have one or more tables.

A table space can consist of a number of VSAM data sets. Data sets are VSAM linear data sets (LDSs). Table spaces are divided into equal-sized units, called pages, which are written to or read from disk in one operation. You can specify page sizes (4 KB, 8 KB, 16 KB, or 32 KB in size) for the data; the default page size is 4 KB. As a general rule, you should have only one table in each table space. It is also best to keep only one table space in each database. If you must have more than one table space in a database, keep no more than 20 table spaces in that database.

Data in most table spaces can be compressed, which can allow you to store more data on each data page.

You can explicitly define a table space by using the CREATE TABLESPACE statement, which can specify the database to which the table space belongs and the storage group that it uses.

Alternatively, you can let DB2 implicitly create a table space for you by issuing a CREATE TABLE statement that does not specify an existing table space. In this case, DB2 assigns the table space to the default database and the default storage group. If DB2 is operating in conversion mode, a segmented table space is created. In new-function mode, DB2 creates a partition-by-growth table space.

The maximum number of partitions for a table space depends on the page size and on the DSSIZE. The size of the table space depends on how many partitions are in the table space and on the DSSIZE. The maximum number of partitions for a partition-by-growth table space depends on the value that is specified for the MAXPARTITIONS option of the CREATE TABLESPACE or ALTER TABLESPACE statement.

When you create a table space, you can specify what type of table space is created. DB2(V9) supports different types of table spaces:

Universal table spaces:

    Provide better space management (for varying-length rows) and improved mass delete performance by combining characteristics of partitioned and segmented table space schemes. A universal table space can hold one table.

Partitioned table spaces:

    Divide the available space into separate units of storage called partitions. Each partition contains one data set of one table.

Segmented table spaces:

    Divide the available space into groups of pages called segments. Each segment is the same size. A segment contains rows from only one table.

Large object table spaces:
    Hold large object data such as graphics, video, or very large text strings. A LOB table space is always associated with the table space that contains the logical LOB column values.

Simple table spaces:

    Can contain more than one table. The rows of different tables are not kept separate (unlike segmented table spaces).
    Restriction: Starting in DB2 Version 9.1, you cannot create a simple table space. Simple table spaces that were created with an earlier version of DB2 are still supported.

XML table spaces:

    Hold XML data. An XML table space is always associated with the table space that contains the logical XML column value.
   add comment

Your answer

Join with account you already have



Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!