DB2 v9.5.1/AIX 6.1. I have an SMS TS with parameters:

Tablespace ID = 2
Name = USERSPACE1
Type = System managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 34931110
Useable pages = 34931110
Used pages = 34931110
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 4

I am trying to figure out if and when we are going to start having space issues with this TS, as tables in it are growing.
How can I find out how much more space is available in this TS?
What limits the size of the SMS TS (available disk space, etc)?
Thanks

SMS tablespaces are limited to the amount of free space left on the filesyatems that the containers cover. For all limitations in DB2, look here:

IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

Andy

thx Andy.

for our case the important number from 'SQL and XML limits' is max table size for 4K page TS = 64GB. Our table is about 59GB size and growing. Would you suggest to move it to a separate TS with bigger page size?

Not necessarily. If I remember correctly, you can only have 255 rows per page. If you are already near that limit, then increasing page size will just waste space. You should look into using a LARGE DMS tablespace.

Andy

Please refer to SQL Reference, Volume 1, Appendix A. SQL and XML limits.

There are new (much larger) limits defined for a LARGE DMS tablespce in 9.5 (don't recall if this applies to 9.1 also), but there is no mention of any limit for SMS tablespaces, probably because an SMS tablespace consists of multiple files (for each table and index). However, you may be limited by your OS in terms of size of individual files (for 32-bit systems), or number of files.

So you may be OK if you want to exceed 64GB, but I am not 100% certain about this.

thx Andy and Marcus