We are running V9.5 FP5 (single partition) and we have one table which is range partitioned that is about 500GB and this table is in SMS tablespace. In one of the docs I read the max size of a table in single partition is 512GB.Is it true that SMS tablespace can grow as long as there is space in filesystem but the max a table can be is 512GB?

Yes, it is true for a 32K page size tablespace. I believe that for a ranged partitioned table, this limit is for each table partition. And SMS will grow and shrink as needed unless there is no more disk space.

Andy

Thanks for the reply ARWinner. Is there a doc or some notes which says that it is per table partition? Its a huge relief if your info is true.Please let me know!

It's the tablespace's max size which limits the table's max size.If each table partition is in different tablespace,the max size of table is near the sum of every tablespace's max size.

Please see http://www.ibm.com/developerworks/da....html#capacity

"Larger table capacity: Without partitioning, there are limits on the maximum amount of data a storage object, and hence a table, can hold. However, by dividing the contents of the table into multiple storage objects or data partitions, each capable of supporting as much data as in a non-partitioned table, you can effectively create databases that are virtually unlimited in size."

DMS tablespaces have size limits, SMS tablespaces do not.

Andy

SMS tablespace also have max size limits.Please see:IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

Interesting, Then I guess the 4K tablespaces I have that are 133 GB, 220 GB, and 66 GB are broken. I believe that table refers to the maximum table size in the tablespace.

Andy

Yes,its per range partition of a table.The limit(512GB) provided in the db2 documentation is per object. Each range is a object and hence a range partitioned table in 32K SMS tablespace can grow to a maximum size of 512GB.
The maximum ranges (data partitions) in a single table is 32767.So,a range partitoned table in 32K SMS tablespace can grow 32767*512 GB as long as the filesystem has space.

That's right.

Thanks Guys for all your inputs.

So, SMS information documented here is wrong. And the temporary tablespace information doesn't look right either.