How is free space in a DMS tablespace managed?

Answer

When a table is dropped, or any transaction that returns used extents back to the tablespace, the extents are put in an intermediate state called the "pending delete" state. This is done to protect the scenario when some of these transactions need to be rolled back, then DB2 can immediately put these pending delete extents to in-use state. Otherwise, newer transactions will be able to use these extents and overwrite the content with their own data, making the rollback of the earlier transactions impossible.

Once the extents had been marked as pending delete, DB2 will mark them free again when there is no existing transactions requiring them to be marked as pending delete anymore. There are events that would trigger the scanning of the tablespace for "freeable" pending delete extents. For example,

1. When new space allocation request comes in, DB2 will try to search for free space in the tablespace, if not, then DB2 will search for "freeable" pending delete extents, free them and use the space.
   
   
2. Running db2 "list tablespaces show detail" command would free up the "freeable" pending delete extents which is done under the covers.
   Use db2pd -db <dbname> -tablespaces before and after the "list tablespaces show detail" command to verify how many extents were freed up. Specifically check 'PndFreePgs' column under Tablespace Statistics section.
   
   
3. In DB2 V9.5, online backup will attempt to free all "freeable" pending delete extents before starting.