Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004

    Unanswered: 8.2 SMS tablespace max limit questions

    8.2 on Win 2000 Server

    Hey all,

    I'm trying to figure out something with regard to SMS tablespace maximums. I have a 4k SMS tablespace approaching 64GB. I'm under the impression that I can exceed 64GB as long as no single table is bigger than 64GB. Is this correct?

    What I actually have is:
    1 table with a long varchar 32700 (.DAT file is 15GB and .LF is 40GB :/)
    + 5.5GB of indexes
    1 133MB table

    So I'm wondering:-
    • Do I need to take action before the tablespace hits 64GB?
    • If the max is actually per table is my table above limit calculated against 15GB or 55GB (DAT+LF)
    • Given that these guys are moving to 9.7 in less than 12 months what's the easiest way to sidestep the space issue if I am approaching a hard limit?

    Last edited by meehange; 09-13-11 at 00:44.

  2. #2
    Join Date
    May 2003
    If I were you, I would check the SQL Limits Appendix of the SQL Reference Vol 1. Just a note, Long Varchar is depricated (still supported in 9.7 but support may be removed in a future release). I would switch to VARCHAR or CLOB.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2004
    Hey Marcus,

    Thanks for the reply... I've checked the Appendix already but I'm trying to ensure I'm understanding it correctly...

    I can't see it explicitly stated so based on the tables in the SQL Ref I'm infering that SMS can exceed 64GB based on the stuff in the Appendix A Table 33 (That a table can be max 64GB, likewise an index) but since there is no listed SMS Tablespace maximum size (in contrast to the 64GB max listed for DMS) that I could in theory have multiple tables and indexes of up to 64GB in one SMS TS.

    My question is - Am I correct in this interpretation?

    If so, then my next question becomes relevant - How is table size actually calculated when applied to this 64GB limit?
    Is it based on the DAT file or DAT+LF in my case... or is it based on the number of pages and if so which object pages are counted?

    The table snap shows this tables as:
    Data Object Pages = 3660033
    Index Object Pages = 1378478
    Long Object Pages = 10213678
    Is it calculated as Data+Long or just Data or all three? :/

    PS - totally in agreement on the Long Varchar... not my decision but think I'll be recommending they move to LOBs

  4. #4
    Join Date
    Jul 2004
    Anyone know if I'm right in my assumptions or wanna give their 2c?

Posting Permissions

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