Hierarchy of Storage Units

Hierarchy of Storage Units

1. In DB2 smallest storage units is Page, then comes extent, container and largest of all is tablespace.
2. 
3. Lets start with Page:-
1. Page is considered to be the smallest unit for storage, which is available in 4K,8K,16K and 32K size.
2. Basic Page structure consists of
1. Page Header(Fixed Length):- It stores location of continuous free space(i.e its offset), free space(Hole Chain) and total amount of free space within the page. 
2. Page Trailer(Variable Length):- It stores an array of pointers, pointing to the address of the starting point of each row saved within the page. Value of this pointers is called as RBA(Relative Byte Address). As the size of RBA is 2 bytes, page is limited to save only 255 entries(rows).
3. Page Content(Variable Length):- It stores row entries of either table or index but not of both.
3. When there is more than 4 bytes of free space available in Page, it is called as Large Hole and maintained by Hole chain List. If free space available is less than 4 bytes its not maintained.
4. 
4.  Next is Extents:-
1. An Extent is defined as continuous collection of pages.
2. All the Pages in an Extent must belong to single Table or Single Index only.
3. It's basic IO unit for writing data from bufferpool(in RAM) to database files(on hard disk).
4. Page Size and Extent Size are declared during the creation of tablespace and can't be modified later.
5. Typically an OLTP environment will have small pages (4k,8k), and a small number of pages per extent: 4,6,16. Decision Support and Data Warehousing applications will have larger pages (16 or 32K), and larger extents (16, 32, 64 or 128). The extent size should be chosen based on the table size and anticipated usage, depending on whether the application is query intensive, transaction intensive or a mixture of both. Smaller tables are handled more efficiently with smaller extents.
5. Next is Containers:-
1. Containers define the storage path(location) on the OS where data is being saved. It also defines the directory structure in case of partition storage / distributed storage configuration.
2. In Automatic Storage the database has a number of storage paths associated with them, and the tablespaces (and hence containers) in the database are assigned and allocated based on those storage paths by DB2. 
3. In this way, you don’t need to explicitly define the containers. They will grow automatically in their directory paths, and if the directory paths are on different disks, the table data will be spread across these disks. DB2 will create new containers if they are needed.
4. Automatic storage gives the best options of DMS (speed and flexibility) and SMS (ease of administration)
6.  Next is Tablespace:-
1. Tablespace can be defined as collection of containers. Containers basically provide location to save data.
2. For each tablespace, you have to define the page size, and each tablespace must have access to a bufferpool of the same page size, and a system temporary tablespace of the same page size.
3. You have to specify which buffer pool the table space is associated with.
4.  Extents are stored within containers. Depending on how the containers are set up (either with Automatic Storage, DMS or SMS) there is a path associated with each container to part of the physical file system. If the tablespace (and hence containers) are on a disk array, the extent size should be set to the disk stripe size
5.  
6. There are three types of tablespace in DB2; Regular, Large, and Temporary.
7. A default database will have 1 bufferpool and 3 tablespace as follows
1. SYSCATSPACE Tablespace(REGULAR)
2. TEMPSPACE1 Tablespace(TEMPORARY)
3. USERSPACE1 Tablespace(LARGE)
4. IBMDEFAULTBP