Tablespace Management in DB2

Tablespace is logical collection of containers which contains actual data of database. In DB2 LUW there is 3 types of tablespace SMS (System Managed Spaces), DMS (Database Managed Spaces) and Automatic Storage. In this blog we will go through different tablespace management operation and behavior of SMS, DMS and Automatic Storage for those operations.

In this blog, we will go through different tablespace operations applicable for tablespaces using File or path based containers.

Create Tablespace Operation:

To create tablespace user must have either SYSCTRL or SYSADM authority. For tablespace creation you will need a buffer pool of page size same as tablespace page size. Before tablespace creation ensure you have buffer pool of same size is present in database. DB2 supports 4K, 8K, 16K and 32K page size. If page size is not specified then database page size will be considered as default value. Once tablespace created its not possible to change its page size.

Create Bufferpool

create bufferpool <bufferpoolname> immediate/differed size <number of pages> pagesize <pagesize>

Create SMS Tablespace

create tablespace <tablespace name> pagesize <pagesize> managed by system using ('<Container path>') bufferpool <bufferpool name>

Create DMS Tablespace

create tablespace <tablespace name> pagesize <pagesize> managed by database using (File '<Container path>' <Number of Pages>) bufferpool <bufferpool name>

Create DMS Autoresize Tablespace

create tablespace <tablespace name> pagesize <pagesize> managed by database using (File '<Container path>' <Number of Pages>) autoresize yes increasesize <integer> K/M/G maxsize <integer/NONE> K/M/G bufferpool <bufferpool name>

By default autoresize is set to No for DMS tablespace.

Create Automatic Storage Tablespace

create tablespace <tablespace name> pagesize <pagesize> managed by automatic storage bufferpool <bufferpool name>

Increase Tablespace Size Operation:

As your data grows you will be in a situation where you need to increase tablespace size to accommodate more data. To increase tablespace size you need to alter existing tablespace. You must have SYSADM or SYSCTRL authority to alter tablespace.

Increase size of SMS Tablespace

SMS tablespaces are designed to increase/decrease size automatically as and when require. No manual intervention is possible to increase or decrease size on SMS tablespaces.

Increase size of DMS Tablespace or DMS Autoresize Tablespace 

alter tablespace <tablespace name> extend (File '<container path>' <Number of Pages to increase>/<Interger K/M/G>)

This will increase size of specific container. Tablespace can have 1 or more containers.

alter tablespace <tablespace name> extend (File '<container path>' <Number of Pages to increase>/<Interger K/M/G>)

This will increase size of all containers of tablespace.

Increase size of Automatic Storage Tablespace

Automatic Storage tablespaces are designed to increase size automatically as and when require. No manual intervention is possible to increase size on Automatic Storage tablespaces.

Add Container to existing Tablespace Operation:

If you want to add additional container to your tablespace to manage size of container file, or split data into multiple container for better IO performance you can do so. To add container you need to alter existing tablespace. You must have SYSADM or SYSCTRL authority to alter tablespace.

Add container to SMS Tablespace

SMS tablespaces are design to have only 1 container per partition. You cannot add additional container to SMS tablespace. 

Add container to DMS Tablespace or DMS Autoresize Tablespace 

alter tablespace <tablespace name> add (File '<container path>' <Number of Pages>/<Integer K/M/G>)

Add container to Automatic Storage Tablespace

To add container to Automatic Storage tablespace you need to add additional storage path for storage group to which tablespace belongs. We will not cover storage path and storage groups in this blog.

Decrease Tablespace Size Operation:

After data cleanup you might want to release space occupied by tablespace to OS for use by some other tablespace as and when require. To release space you need to decrease size of tablespace for that tablespace must be altered. You must have SYSADM or SYSCTRL authority to alter tablespace.

Decrease size of SMS Tablespace

SMS tablespaces are designed to increase/decrease size automatically as and when require. No manual intervention is possible to increase or decrease size on SMS tablespaces.

Decrease size of DMS Tablespace or DMS Autoresize Tablespace 

alter tablespace <tablespace name> reduce (File '<container path>' <Number of Pages>/<Integer Percent/K/M/G>)

This will decrease size of specific container if there is enough free space available above High Water Mark (HWM). If enough free space is unavailable above HWM command will fail even if free space is available below HWM. In such cases first you need to reduce HWM and then alter tablespace to reduce size.

alter tablespace <tablespace name> reduce (ALL <Number of Pages>/<Integer Percent/K/M/G>)

This will increase size of all containers of tablespace if there is enough space available above High Water Mark (HWM). If enough free space is unavailable above HWM command will fail even if free space is available below HWM. In such cases first you need to reduce HWM and then alter tablespace to reduce size.

Decrease size of Automatic Storage Tablespace

alter tablespace <tablespace name> reduce <Number of Pages>/<Integer Percent/K/M/G>

This will decrease size of tablespace by pages or size specified. In case there is not enough free space available above HWM but free space is available below HWM, first HWM will be reduced automatically then space will be released.

alter tablespace <tablespace name> reduce MAX

This will release all free space from tablespace.

Drop Container from existing Tablespace Operation:

If you want to drop additional container from your tablespace it is possible but before that ensure there is enough storage available on other containers of same tablespace to accommodate data of container being dropped. Also there must be atleast 1 container available for tablespace. To drop container you need to alter existing tablespace. You must have SYSADM or SYSCTRL authority to alter tablespace.

Drop container of SMS Tablespace

SMS tablespaces are design to have only 1 container per partition. As there is only 1 container for SMS tablespace you cannot drop it.

Drop container of DMS Tablespace or DMS Autoresize Tablespace 

alter tablespace <tablespace name> drop (File '<container path>')

Drop container of Automatic Storage Tablespace

To drop container of Automatic Storage tablespace you need to drop storage path for storage group to which tablespace belongs. We will not cover storage path and storage groups in this blog.

Note: SMS and DMS tablespace got deprecated in DB2 10.1 .  

If you liked this blog and interested in knowing more about DB2, please subscribe by clicking on Subscribe to ChoudharySumit.com by Email.