Occurrence

When 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.

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.