Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2011
    Posts
    33

    Unanswered: Max Size of Table

    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?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Aug 2011
    Posts
    33
    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!

  4. #4
    Join Date
    Nov 2011
    Posts
    8
    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."
    Last edited by vesli; 11-05-11 at 10:49.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by vesli View Post
    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 decided by every tablespace's usage.
    DMS tablespaces have size limits, SMS tablespaces do not.

    Andy

  6. #6
    Join Date
    Nov 2011
    Posts
    8
    SMS tablespace also have max size limits.Please see:IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by vesli View Post
    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

  8. #8
    Join Date
    Aug 2011
    Posts
    33
    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.

  9. #9
    Join Date
    Nov 2011
    Posts
    8
    That's right.

  10. #10
    Join Date
    Aug 2011
    Posts
    33
    Thanks Guys for all your inputs.

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by vesli View Post
    SMS tablespace also have max size limits.Please see:IBM DB2 9.5 Information Center for Linux, UNIX, and Windows
    So, SMS information documented here is wrong. And the temporary tablespace information doesn't look right either.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •