How to Increase a DMS Tablespace in DB2

In this article you will learn and see how to increase a Database-Managed tablespace. While monitoring in db2 on daily basis , sometime you receive alerts or observe the tablespace size is equal or bigger than the defined threshold (safety size) which is usually about 80-90% used. So what to do when the defined threshold is crossed and you start receiving alerts or incidents for same, Follow below steps before it become critical:

1) First of all detect which tablespace has size issues.

To check which tablespaces are almost full type the following command once you logged at the instance:

db2 list tablespaces show detail

This command will output a list with all the tablespaces and you can easily identify the specific tablespace which has issues.

Example:

db2 list tablespaces show detail

 Tablespaces for Current Database

  Tablespace ID                        = 0
  Name                                 = SYSCATSPACE
  Type                                 = Database managed space
  Contents                             = All permanent data. Regular table space.
  State                                = 0x0000
    Detailed explanation:
      Normal
  Total pages                          = 8192
  Useable pages                        = 8188
  Used pages                           = 7736
  Free pages                           = 452
  High water mark (pages)              = 7736
  Page size (bytes)                    = 8192
  Extent size (pages)                  = 4
  Prefetch size (pages)                = 4
  Number of containers                 = 1

  Tablespace ID                        = 1
  Name                                 = TEMPSPACE1
  Type                                 = System managed space
  Contents                             = System Temporary data
  State                                = 0x0000
    Detailed explanation:
      Normal
  Total pages                          = 1
  Useable pages                        = 1
  Used pages                           = 1
  Free pages                           = Not applicable
  High water mark (pages)              = Not applicable
  Page size (bytes)                    = 8192
  Extent size (pages)                  = 32
  Prefetch size (pages)                = 32
  Number of containers                 = 1

  Tablespace ID                        = 2
  Name                                 = USERSPACE1
  Type                                 = Database managed space
  Contents                             = All permanent data. Large table space.
  State                                = 0x0000
    Detailed explanation:
      Normal
  Total pages                          = 4096
  Useable pages                        = 4064
  Used pages                           = 1760
  Free pages                           = 2304
  High water mark (pages)              = 1824
  Page size (bytes)                    = 8192
  Extent size (pages)                  = 32
  Prefetch size (pages)                = 32
  Number of containers                 = 1

  Tablespace ID                        = 3
  Name                                 = IBMDB2SAMPLEREL
  Type                                 = Database managed space
  Contents                             = All permanent data. Large table space.
  State                                = 0x0000
    Detailed explanation:
      Normal
  Total pages                          = 4096
  Useable pages                        = 4064
  Used pages                           = 608
  Free pages                           = 3456
  High water mark (pages)              = 608
  Page size (bytes)                    = 8192
  Extent size (pages)                  = 32
  Prefetch size (pages)                = 32
  Number of containers                 = 1

2) Check the percentage of use from the tablespace

After detecting the exact tablespaces facing issues, you can check the percentage used of it by typing the following command :

db2 list tablespace containers for <tablespace_id>  show detail

Now you can identify the total number of containers, where the tablespace is stored, the total number of pages and the total number of used pages.

Example:

db2 list tablespace containers for 3 show detail

             Tablespace Containers for Tablespace 3

  Container ID                         = 0
  Name                                 = C:\DB2\NODE0000\SAMPLE\T0000003\C0000000.LRG
  Type                                 = File
  Total pages                          = 4096
  Useable pages                        = 4064
  Accessible                           = Yes

3) Calculating the size of free space needed

Now you are able to calculate the percentage of use from the tablespace. To get this number, do (Used Pages / Useable Pages) * 100 and then with this percentage you can continue calculating how many pages you need to increase to obtain the acceptable amount of free pages.

4) Verifying the filesystem and the free space.

Continuing, now you should check the total amount of free space at the fileystem by executing below command:

df -k <tablespace.path>

And you'll be able to verify if it's enough space to extend the tablespace. Remember you can check the page size in the output of executed below command:

db2 list tablespaces show detail

5) Extending the tablespace

Finally, after all checked, you can execute the command to extend the pages of that tablespace. Do this using:

db2 "alter tablespace <tablespace_name> extend (all <page_numbers>)"

This will extend the size of your tablespace. Be careful when using EXTEND ALL clause when the tablespace has more than one container; in cases where the tablespace has more than one container, divide the total number of pages you intent to increase by the number of total containers.

Example:

db2 alter "tablespace IBMDB2SAMPLEREL extend (all 1000)"

Alternatively, if it's not possible to extend the actual containers, a possibility is to add new containers to the tablespace. For that, you should enter the following command:

db2 "alter tablespace <tablespace_name> add (FILE '<new_container_file>' <size>)"

Where size can be in pages (only using the numbers you want), in KBytes (using a K following the number), in MBytes (using a M following the number) or in GBytes (using a G following the number). Also, please note to use single quotes around the name of the file you'll use as a new container.

That's all in this article. Please like, Share and Follow for more content like this. Thank You.