DB2 USERSPACE1 is full

31 December,2010 by Jack Vamvas

 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

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on DB2 USERSPACE1 is full

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright