DB2 USERSPACE1 is full

 After a period of  usage – quite often I see these type of messages on the monitoring reports

 USERSPACE1 in MYDB has 98 pct used. Instance: MyInstance

 The first step I take is:

Db2 connect to MYDB

db2 list tablespace containers for 2 show detail

Tablespace ID                        = 2

 Name                                 = USERSPACE1

 Type                                 = Database managed space

 Contents                             = All permanent data. Large table space.

 State                                = 0x0000

  Detailed explanation:

 Normal

 Total pages                          = 81920

 Useable pages                        = 81888

 Used pages                           = 80288

 Free pages                           = 1600

 High water mark (pages)              = 80288

 Page size (bytes)                    = 4096

 Extent size (pages)                  = 32

 Prefetch size (pages)                = 32

 Number of containers                 = 1

First thing to note is that I’m not looking to reclaim space . The total pages are set at 81920 and the high water mark is 80288. What I’m interested in is the Used pages currently at 80288.This indicates the utilisation is high.

Even one row on a page will make that page as used . I’ve also noticed that REORG has not been executed on this database for a few weeks. Upon running a REORG on the database – this decreased the Used pages –

Tablespace ID                        = 2

 Name                                 = USERSPACE1

 Type                                 = Database managed space

 Contents                             = All permanent data. Large table space.

 State                                = 0x0000

   Detailed explanation:

     Normal

 Total pages                          = 81920

 Useable pages                        = 81888

 Used pages                           = 74784

 Free pages                           = 7104

 High water mark (pages)              = 80288

 Page size (bytes)                    = 4096

 Extent size (pages)                  = 32

 Prefetch size (pages)                = 32

 Number of containers                 = 1

 If the situation arises – I start an analysis for objects within the database that can be permanently deleted or look to adjust the tablespace size

Read More

Db2 reduce tablespace with ALTER TABLESPACE REDUCE - DBA

DB2 – How to check if autoresize is enabled