If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > tablespace limit

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 219
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 219
its a DMS tablespace
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 219
yes DMS has the limit 4k 64gb size of tablespaces
regds
Paul
Reply With Quote
  #4 (permalink)  
Old
∞∞∞∞∞∞
 
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 219
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 219
but is there any best Practices, any example for 450 tables to spread accross the file system
regds
Paul
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 219
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
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On