Alternative could be to use PowerShell to grab the information. All you need is a few commands tied together:
$measure = Get-WmiObject Win32_LogicalDisk | Where {$_.DriveType -eq 5}
if ($measure.FreeSpace -lt 20 { "More logic"} else {"something else"}
You could add in the logic to measure for your limit (value is in bytes). Then use Send-MailMessage cmdlet to actually send out your message. Wrap all that up and then have a SQL Agent job call the script on a scheduled basis.
I'm going to take a guess that you are using automatic storage. (Not that this could happen otherwise...it is just easy to have this happen with automatic storage.)
The problem is most likely that your database reclaimed the space for itself but did not release the disk back to the operating system. This can be shown very easily by checking the High Water Mark for the tablespace.
Do a the following
db2 list tablespaces show detail
This will show you each tablespace and what it is using on disk. Used pages is how many pages of disk the database is using. Comparing that against total pages (the total claimed on disk) and the High water mark (pages) will show you if you are "claiming" more than you actually need. (ie, low used pages, very high total pages and a High Water Mark close to the total pages).
To get rid of this unused space and return it to the operating system you would issue the following (under automatic storage): db2 alter tablespace <tablespace name> reduce max.
example
db2 alter tablespace ts1 reduce max;
That will cause DB2 to lower the high water mark and release the unused disk back to the operating system. (Note you can only do this for regular and large tablespaces, not for system temporary, or user temporary tablespaces).
If you are using DMS without automatic storage you need to use a slightly different set of commands:
db2 alter tablespace <tablespace name> lower high water mark;
db2 alter tablespace reduce (<containter name> or [all containers] integer K|M|G or integer PERCENT);
example
db2 alter tablespace ts1 lower high water mark;
db2 alter tablespace reduce (all containers 500 M);
Where we work, we put this into some of our maintenance scripts so that we automatically run this after we do reorgs to make sure we reclaim disk space. In our case we use DB2 LUW 9.7 FP 4, so it doesn't hurt to double check Information Center for 9.5 to make sure you have access to the right information for your version.
EDIT: If your tablespaces came from a database upgraded to DB2 9.7, you probably will not have the reclaimable storage attribute set. This is true even if you upgrade from DMS to automatic storage. Either
way bites as you cannot actually lower the high water mark. You have
to dump the table and data out, drop the tablespaces. Then re-create
the tablespace using automatic storage and import the data for your
tables.
Best Answer
To get rid of a lot of rows there are various options, hopefully the brief descriptions below will help you choose which one might be a "fit".
Use Truncate Table - this removes all the rows in the table by deallocating the extents - and is therefore a fast operation
Partition your tables and then use a partition switch to switch out the oldest partition once you don't need it anymore - assumes your partitioning design will group rows of similar age together into the same partition.
Use an ordinary delete but do it many times, to avoid Lock Escalation and massive blocking problems, can still cause problems with transaction log growth - see the linked earlier answer
Delete rows older than x days without locking table