IBM Support

"Alter tablespace ...reduce..." command may not reclaim space if there are pending free pages

Technical Blog Post


Abstract

"Alter tablespace ...reduce..." command may not reclaim space if there are pending free pages

Body

ALTER TABLESPACE ... REDUCE... command can be used to lower high water mark and reclaim space to file system.

For instance, the following command move maximum number of extents:

db2 alter tablespace USERSPACE1 reduce max

 

However if there are pending free pages in the tablespace and pending free pages happen to hold on high water mark

You may find high water mark can't be lowered and space can't be released.

You can verify this from db2pd -tablespace command. The following is an example where tablespace 2 has pending free pages.

 

Tablespace Statistics:

Address      Id  TotalPgs  UsablePgs UsedPgs  PndFreePgs FreePgs  HWM    Max HWM  State   MinRecTime NQuiescers PathsDropped TrackmodState

0x00007F1A09445D40 0   167568   167552   164500   0     3052    164500   164500   0x00000000 0     0     No      n/a

0x00007F1A09452EE0 1   633537   633537   633537   0     0     -     -     0x00000000 0     0     No      n/a

0x00007F1A094960C0 2   16225536  16225408  14173792  320    2051296  15935968  17641632  0x00000000 1536591093 0     No      n/a

 

 

You can terminate all applications and use "db2 list tablespaces show detail" command to see pending free pages can be freed up.

For more details please refer to the following technote:

/support/pages/node/392775

 

If you cannot release pending free pages in the above steps, you can reactivate the database.

Once the pending free pages are removed, issue ALTER TABLESPACE...REDUCE again.

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm13285963