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.
"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."
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.