Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2007
    Posts
    220

    tablespace limit

    hi all

    db2 V 8 , regular tablespace 4k
    in the above tablespace a table can grow upto 64gb
    if i have one table in the tablespace and its reaching the limit of 64gb
    ---
    i getting this error when i trying to increase the size of the containers
    and tablespace total pages is reached 16777215.
    SQL1139N The total size of the table space is too big.

    Explanation:

    The size of the current table space is too big. The size of a
    REGULAR or USER TEMPORARY table space is limited to 0xFFFFFF
    (16777215) pages while the size of a SYSTEM TEMPORARY or LONG
    table space is limited to 2 terabytes (2 TB).

    User Response:

    Check the administration notification log for details. Reduce the
    size of the table space and correct the SQL statement.

    sqlcode : -1139

    sqlstate : 54047

    does a regular tablespaces have limit of 16million pages
    kindly suggest
    regds
    Paul

  2. #2
    Join Date
    Oct 2007
    Posts
    220
    its a DMS tablespace

  3. #3
    Join Date
    Oct 2007
    Posts
    220
    yes DMS has the limit 4k 64gb size of tablespaces
    regds
    Paul

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,361
    Looks correct. 16777215 * 4K = 63.99 GB. But should still be able to allocate one extra page to be exact 64GB, but the error is valid

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    I see you have answered your own question Those limitations are mentioned in the manual someplace, just in case you want to check on the other limitations
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Oct 2007
    Posts
    220
    if we have 450 tables and if i want to create DMS tablespace
    we can put tables according to transaction and lookup tables in one tablespace, how many tablespaces can we create according transaction and lookup for 450 tables. kindly suggest
    regds
    Paul

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    s many as you want.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Oct 2007
    Posts
    220
    but is there any best Practices, any example for 450 tables to spread accross the file system
    regds
    Paul

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Do you have real disks underneath or is it a storage system that exposes virtual disks? In the latter case, you don't even know where the data ends up, so you just create the tablespaces you need and try to minimize administration efforts by lumping everything together as much as possible.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Oct 2007
    Posts
    220
    thanks stolze,
    its a storage system, i will let you know once the physical structure is prepared like transaction tablespace, lookup tablespaces, 8k 4k tablepsace and indexe tablespace
    regds
    paul

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    As I said, I wouldn't worry about the tablespaces and where they reside. The page size is something you have to take care of, depending on the data. And you should design your buffer pools accordingly to separate data with different access patterns. Once you have the buffer pools and the assignment of tables to those buffer pools, the tablespaces are implicitly given (because a tablespace can map to a single buffer pool only).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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