We have DB2 UDB v.7.1 on Windows 2k with SMS tablespaces.

I'm wondering whether the SMS space that have been allocated during a transaction (say, an insert caused a new page to be allocated, or an update to a LOB field...) would be de-allocated if the application initiates roll-back at some point, which will in turn cause resizing of an SMS container. I understand that page allocation does get recorded in the database log, but would it be reverted upon a roll-back, or just marked as free space?

Thanks for your thoughts.

You need to REORG to deallocate the space for SMS.

So, a newly allocated page will still remain allocated, although empty, even if the transaction that caused the allocation never commits?

This is the process how I see it:

1) The application issues an INSERT
2) The db agent searches for free space, doesn't find any, and requests new page allocation.
3) The container gets extended (does it, really, at this point though? or does it happen when the buffer page is written to disk?)
4) New page allocation is logged in the db log.
5) A bufferpool page gets latched.
6) New data is written to the db log.
7) New data is written to the bufferpool page.
8) Page is unlatched and marked as dirty.
9) Page is written to disk
10) The application issues a ROLLBACK
11) ? what happens next ?

Maybe someone will correct me (hope not!) but I don't believe the tablespace allocation is in any way transaction aware. Once allocated, it stays allocated until a REORG, regardless of what happens in the transaction.

Nick's point on

raises a question (Hope I'm not being stupid)

If the pages will be allocated only when the data is being written to disk, what will happen if the transaction is committed, and when writing the dirty bp pages to the disk, db manager finds that there is no space on the filesystem to allocate the new extent ?

I did a small test on a test database(tablespace is USERSPACE1, SMS) and have the found :

1) If a table is created, you have a page allocated in a container ... When rolled back, it releases the page back to the OS ...

2) If data is inserted, it allocated pages to write the data . When rolled back, this does not release the page ...

Cheers
Sathyaram

That's right. I was just doing similar tests, and can confirm this. This also includes LOBs.