[Db2] How can I solve a table space full situation?

Troubleshooting

Problem

When a table space becomes full, Db2 cannot insert or update data into a table in that table space. In such situation, applications that executing such requests might fail.
This document describes how to recover from a "table space full" situation.

Symptom

When a table space has become full, transactions including update statements can fail since there is not enough space to store new data.

If there is enough room in extents or pages, which is already allocated to the object, update transactions can be completed successfully even though there is no free page in the table space.

Diagnosing The Problem

Search for ADM6044E or ADM6017E messages in db2diag.log file, and find the table space name that became full. Check the type of the table space by running the following SQL.

Connect to the database with user ID that has DBADM authority or equivalent.
```
db2 connect to [database]
```

TBSP_UTILIZATION administrative view returns attribute value for the table space. TBSP_TYPE column shows whether the table space is SMS or DMS, and if AUTO column value is 1 it means auto storage. The TBSP_CONTENT_TYPE column shows whether it is a LARGE table space or a REGULAR one (column value ANY means a REGULAR table space).

db2 "SELECT TBSP_ID, SUBSTR(TBSP_NAME,1,20) AS TBSP_NAME, TBSP_TYPE, TBSP_CONTENT_TYPE, TBSP_USING_AUTO_STORAGE, TBSP_TOTAL_SIZE_KB, TBSP_TOTAL_PAGES FROM SYSIBMADM.TBSP_UTILIZATION"

TBSP_ID TBSP_NAME            TBSP_TYPE  TBSP_CONTENT_TYPE AUTO TBSP_TOTAL_SIZE_KB TBSP_TOTAL_PAGES
------- -------------------- ---------- ----------------- ---- ------------------ ----------------
      0 SYSCATSPACE          SMS        ANY                  0           163840            20480
      1 TEMPSPACE1           SMS        SYSTEMP              0                0                0
      2 USERSPACE1           SMS        ANY                  0            32768             4096    
      3 AUTOTBSP             DMS        LARGE                1           229376            28672
      4 SYSTOOLSPACE         DMS        LARGE                1            32768             4096
      5 DMSTBSP              DMS        LARGE                0         11665408          1458176
      6 DMSTBSP2             DMS        ANY                  1         25427968          3178496

Resolving The Problem

Available methods differ depending on the type of the table space. Refer to the following table to find which method is available for your problem.

Type of table space	Error message returned to application	Administrative notification message	Available method for recovery
Automatic storage	SQL0289N Unable to allocate new pages in table space "<tablespace-name>".	ADM6044E	Choose any of the following based on your requirement a) add a storage path b) extend the storage area specified in the storage path c) delete unnecessary data and reorganize the table g) convert the table space into a LARGE table space in case it is a REGULAR table space h) change MAXSIZE for the table space to an appropriate value
DMS	SQL0289N Unable to allocate new pages in table space "<tablespace-name>".	ADM6044E	Choose any of the following based on your requirement c) delete unnecessary data and reorganize the table d) add a container e) extend or resize the existing containers g) convert the table space to a LARGE table space in case it is a REGULAR table space h) change MAXSIZE for the table space to an appropriate value
SMS	SQL0968C The file system is full.	ADM6017E	Choose any of the following based on your requirement c) delete unnecessary data and reorganize the table f) extend the existing containers assigned by OS

a) Add a storage path

a-1) Add a storage path to the database (V9.7 or before)

Add a storage path with an ALTER DATABASE statement.

Example

db2 connect to [database]
db2 get snapshot for database on [database]
Number of automatic storage paths          = 1
Automatic storage path                     = /work/dsidauto
      Node number                          = 0
      State                                = In Use
db2 "alter database [database] add storage on '/work/dsidauto2'"
db2 get snapshot for database on [database]
Number of automatic storage paths          = 2
Automatic storage path                     = /work/dsidauto
      Node number                          = 0
      State                                = In Use
Automatic storage path                     = /work/dsidauto2
      Node number                          = 0
      State                                = In Use

a-2) Add a storage path in the storage group (V10.1 or later)

Add a storage path with an ALTER STOGROUP statement.
The following query tells you which storage group the table space belongs to.

db2 connect to [database]
db2 "select t.tbspace, s.sgname from syscat.tablespaces t, syscat.stogroups s where t.sgid=s.sgid"
TBSPACE                        SGNAME
------------------------------ ------------------------------
SYSCATSPACE                    IBMSTOGROUP
TEMPSPACE1                     IBMSTOGROUP
USERSPACE1                     IBMSTOGROUP

You can list the storage groups and corresponding storage paths in the database with the following query.

db2 "select storage_group_name, db_storage_path from table(ADMIN_GET_STORAGE_PATHS('',-1)) as t"
STORAGE_GROUP_NAME             DB_STORAGE_PATH
------------------------------ ------------------------------
IBMSTOGROUP                    /work/dsidauto

Add a storage path to the storage group with an ALTER STOGROUP statement.

db2 "alter stogroup IBMSTOGROUP add /work/dsidauto2"

Note when new storage paths are added

Existing REGULAR and LARGE table spaces using this storage group does not initially use the new paths. The database manager might choose to create new table space containers on these paths only if an out-of-space condition occurs.
Existing temporary table spaces managed by automatic storage do not automatically use the new storage paths. The database must be stopped normally then be restarted for containers in these table spaces to use the new storage path or paths. As an alternative, the temporary table spaces can be dropped and be re-created. If a table space is re-created, it automatically uses all storage paths having sufficient free spaces.

b) Extend the storage area specified in the storage path

Refer to the method a) depending on your version and find the storage path assigned to the table space.
Extend the storage area for the path that uses appropriate OS operations.

c) Delete unnecessary data and reorganize the table

In order to make room to store new data, remove some existing data from tables in the table space, for example by issuing DELETE statements.
Then, reorganize the table with a REORG command to free extents that became empty.

Example

offline reorganization (requires free space in the table space of the size of the table after you delete the data)
```
  db2 "delete from table1 where col1=10"
  db2 "reorg table table1"
```
online reorganization (requires no additional space in the table space, but takes much more time than offline reorganization)
```
  db2 "delete from table1 where col1=10"
  db2 "reorg table table1 inplace"
```
EXPORT and LOAD REPLACE (requires no additional space in the table space. It can take longer than the offline reorganization. You need to consider code page compatibility when you execute the EXPORT command. When the table contains LOB or XML columns, you might need more options or operations).
```
  db2 "delete from table1 where col1=10"
  db2 "export to table1.ixf of ixf select * from table1"
  db2 "load from table1.ixf of ixf replace into table1"
```

d) Add a container

Add a container with an ALTER TABLESPACE statement.

Example

db2 "alter tablespace dmstbsp add (file '/work/dsiddms2' 10M)"

Note after you add new containers

If existing containers become full and a new container is added, ALTER TABLESPACE ... REBALANACE statement can fail with SQL2094W rc=2. SQL2094W means that the rebalance attempt fails because the existing containers don't have enough room. Before you try rebalance, make sure there is free space in existing containers.

e) Extend or resize the existing container

Extend or resize existing container size with an ALTER TABLESPACE statement.

Example (assume the existing container size is 100MB)

EXTEND: Add 50MB more storage into the existing container (that is, the new size is 100MB + 50MB = 150MB)
```
db2 "alter tablespace dmstbsp extend (file '/work/dsiddms' 50M)"
```
RESIZE: Resize the existing container to 150MB (that is, the new size will be 150MB)
```
db2 "alter tablespace dmstbsp resize (file '/work/dsiddms' 150M)"
```

f) Extend the existing container assigned by OS

Connect to the database and run the following query to list SMS table space containers.

select substr(a.tbsp_name, 1, 15) as tbsp_name, substr(a.tbsp_id,1,4) as tbsp_id, a.container_name, substr(a.container_id,1,5) as container_id from sysibmadm.snapcontainer a, sysibmadm.tbsp_utilization b where a.tbsp_id = b.tbsp_id and b.tbsp_type = 'SMS'

Example

db2 connect to [database]
db2 "select substr(a.tbsp_name, 1, 15) as tbsp_name, substr(a.tbsp_id,1,4) as tbsp_id, a.container_name, substr(a.container_id,1,5) as container_id from sysibmadm.snapcontainer a, sysibmadm.tbsp_utilization b where a.tbsp_id = b.tbsp_id and b.tbsp_type = 'SMS'"

 TBSP_NAME       TBSP_ID CONTAINER_NAME                              CONTAINER_ID
 --------------- ------- ------------------------------------------- ------------
 SYSCATSPACE     0       /work/dsiddb/NODE0000/SQL00001/SQLT0000.0   0          
 TEMPSPACE1      1       /work/dsiddb/NODE0000/SQL00001/SQLT0001.0   0          
 USERSPACE1      2       /work/dsiddb/NODE0000/SQL00001/SQLT0002.0   0

From the operation system perspective, check the usage of file system containing the paths in CONTAINER_NAME, and expand it if necessary.

g) Convert to a LARGE table space

In a REGULAR table space, maximum number of pages is limited to 16777216. When the table space already reached the limit, only method c) or g) can be available options.

When method c) could not generate enough room in the table space, and you have usable free space in the file system where the table space container resides, convert the table space into a LARGE table space.

Example

db2 alter tablespace dmstbsp2 convert to large

All the indexes must be rebuilt if the base tables are in the table space being converted to a LARGE table space.
Find the tables created in the table space by running the following query, then rebuild indexes by REORG INDEXES command.

db2 select tabschema, tabname from syscat.tables where tbspace = '[tablespace]'
db2 reorg indexes all for [schema].[table]

Following SQL creates a script file (reorg_index.db2) to rebuild all indexes, which meet the condition.

db2 -x "SELECT 'REORG INDEXES ALL FOR TABLE '||TRIM(TABSCHEMA)||'.'||TRIM(TABNAME)||';' from SYSCAT.TABLES where TBSPACE='[tablespace]' and TYPE='T'" > reorg_index.db2

h) Update MAXSIZE setting for table space

Check the MAXSIZE setting using the following command:

db2pd -db [database name] -tablespaces

Sample output where [database name] is SAMPLE:

[db2inst1@db2-prod root]$ db2pd -db sample -tablespaces

Database Member 0 -- Database SAMPLE -- Active -- Up 4 days 23:58:32 -- Date 2021-12-29-13.57.38.177504

Tablespace Configuration:
Address            Id    Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe  LastConsecPg RSE  Name
0x00007F5F72109F40 0     DMS  Regular 4096   4        Yes  4        1     1         Def 1        0          3            Yes  SYSCATSPACE
0x00007F610AC30080 1     SMS  SysTmp  4096   32       Yes  32       1     1         On  1        0          31           No   TEMPSPACE1
0x00007F610AC3D440 2     DMS  Large   4096   32       Yes  32       1     1         Def 1        0          31           Yes  USERSPACE1  
....

Tablespace Statistics:
Address            Id    TotalPgs   UsablePgs  UsedPgs    PndFreePgs FreePgs    HWM        Max HWM    State      MinRecTime NQuiescers PathsDropped TrackmodState
0x00007F5F72109F40 0     73728      73724      65824      0          7900       65824      65824      0x00000000 0          0          No           n/a
0x00007F610AC30080 1     1          1          1          0          0          -          -          0x00000000 0          0          No           n/a
0x00007F610AC3D440 2     26214400   26214368   12288      0          26202080   12288      12288      0x00000000 0          0          No           n/a
...

Tablespace Autoresize Statistics:
Address            Id    AS  AR  InitSize             IncSize              IIP MaxSize              LastResize                 LRF
0x00007F5F72109F40 0     Yes Yes 33554432             -1                   No  None                 12/24/2021 16:04:06.204452 No
0x00007F610AC30080 1     Yes No  0                    0                    No  0                    None                       No
0x00007F610AC3D440 2     Yes Yes 33554432             -1                   No  107374182400         None                       No

Change it to an appropriate value or set it to NONE.

db2 "alter tablespace [Tablespace] MAXSIZE [Size]

In this example, the table space USERSPACE1 has a size limit set. To change it to unlimited, run the following statement:

db2 "alter tablespace USERSPACE1 MAXSIZE None"

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;10.1;10.5;11.1;11.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Tips

[Db2] How can I solve a table space full situation?

Troubleshooting

Problem

Symptom

Diagnosing The Problem

Resolving The Problem

Was this topic helpful?

Document Information

UID

Share your feedback

Need support?