Steps to Increase a DMS Tablespace | DB2

In this blog we will learn how to increase a Database-Managed tablespace, Whenever the tablespace size is equal or bigger than the defined safety size (usually about 80-90% used)

Steps to Follow

1) Detect what tablespace has size issues

Once logged at the instance, to check what tablespace are almost full, type the following command:

db2 list tablespaces show detail

It will shows you a list with all tablespaces and you can identify the specific tablespace with 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 tablespace, you can check the percentage used of it. Type 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. Do this typing

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 "db2 list tablespaces show detail" command.

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.