Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: space issues and size limit of SMS tablespace

    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
    Last edited by MarkhamDBA; 05-11-09 at 15:10.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

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

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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?
    Last edited by MarkhamDBA; 05-11-09 at 16:53.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

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

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    thx Andy and Marcus
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

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