Technical Blog Post
"Alter tablespace ...reduce..." command may not reclaim space if there are pending free pages
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.
Address Id TotalPgs Usa
0x00007F1A09445D40 0 167568 167552 164500
0x00007F1A09452EE0 1 633537 633537 633537
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:
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.