When SYSCATSPACE gets full

Body

The maximum size of a large DMS tablespace in DB2 can grow up to 64 TB, but a regular DMS tablespace can only go up to 512 GB (64 GB for 4K page size).

(https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001029.html)

A regular DMS tablespace could be converted to a large tablespace using the ALTER TABLESPACE command. But this command does not support the conversion of the system catalog table space which is known as SYSCATSPACE. But we have to find the root casue and fix it.

2017-10-21-07.57.28.114515-300 E1787846A663 LEVEL: Warning
PID : 44173052 TID : 301290 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYPROD01
APPHDL : 0-18636 APPID: 192.168.1.30.12345.171021031133
AUTHID : DB2USR
EDUID : 301290 EDUNAME: db2agent (MYPROD01) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbPrepareToGrowLastRange, probe:160
MESSAGE : ADM6099W Table space "SYSCATSPACE" (ID "0") has reached the maxiumum
size ("68719476736" bytes) for a tablespace of this type and page
size. To add more storage to the database, add a new table space.

2017-10-21-07.57.28.115118-300 E1788510A1127 LEVEL: Error
PID : 44173052 TID : 301290 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYPROD01
APPHDL : 0-18636 APPID: 192.168.1.30.12345.171021031133
AUTHID : DB2USR
EDUID : 301290 EDUNAME: db2agent (MYPROD01) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbAllocateExtent, probe:835
MESSAGE : ADM6044E The DMS table space "SYSCATSPACE" (ID "0") is full. If
this is an autoresize or automatic storage DMS tablespace, the
maximum table space size may have been reached or the existing
containers or storage paths cannot grow any more. Additional space
can be added to the table space by either adding new containers or
extending existing ones using the ALTER TABLESPACE SQL statement. If
this is an autoresize or automatic storage DMS table space,
additional space can be added by adding containers to an autoresize
table space or by adding new storage paths to an automatic storage
database.

db2pd -db <db_name> -tcbstats is a quick way to tell you which system table is the largest:

TCB Table Information:
Address            TbspaceID TableID PartID MasterTbs MasterTab TableName          SchemaNm ObjClass DataSize   LfSize     LobSize    XMLSize
0x0A00020FA0FD7D58 0         1       n/a    0         1         SYSBOOT            SYSIBM   Perm     1          0          0          0
0x0A00020FA171B4D8 0         5       n/a    0         5         SYSTABLES          SYSIBM   Perm     9633       0          15941632   0

In this case, the LOB data in table SYSIBM.SYSTABLES is reponsible for 15941632 pages which is about 60.8 GB. So it does look a bit unusual. We can run a query to find out the actual length of LOB data on each row in sysibm.systables:

select substr(NAME,1,40), CTIME, ALTER_TIME, COALESCE(length(PACKED_DESC),0) + COALESCE(length(VIEW_DESC),0) + COALESCE(length(REL_DESC),0) + COALESCE(length(CHECK_DESC),0) + COALESCE(length(TRIG_DESC),0) + COALESCE(length(REMOTE_DESC),0) + COALESCE(length(AST_DESC),0) + COALESCE(length(STATISTICS_PROFILE),0) + COALESCE(length(CONTROLS_DESC),0) + COALESCE(length(AUDITEXCEPTION_DESC),0) as LOB_LENGTH from sysibm.systables order by LOB_LENGTH desc

If result does not look unusual, a REORG on the table may help delete unnecessary LOB pages and fix this issue. Don't forget to use a system temporary space in REORG when SYSCATSPACE is full already.

db2 reorg table sysibm.systables LONGLOBDATA use TMPSPACE4K