db2 bufferpool and tablespace review

Our company sells a software package that uses many different RDBMS for storage. Shown below is the default storage objects for DB2. This software package could require large database storage, but it all depends on each customers usage of the application.

Questions:
Should the pagesize of the LARGE tablespace be increased for 8K?
Does the value of 500 in the bufferpool mean that the total memory used will be only 4000k (500*8k)? What is a reasonal default value for SIZE in the bufferpool? I know that it "all depends", but what a general fair value, say 50, 500, 5000, 5000000?
Anything glaringly wrong with the storage shown below?


One buffer pool:
 
CREATE BUFFERPOOL XYZBUFF
    IMMEDIATE  
    ALL NODES
    SIZE 500
    PAGESIZE 8K
    NOT EXTENDED STORAGE;
 
Three tablespaces:
 
CREATE TABLESPACE XYZDATA
    PAGESIZE 8K  
    MANAGED BY DATABASE  
      USING (FILE '&2\XYZDATA.F1' 100M)  
    BUFFERPOOL XYZBUFF;
 
CREATE TABLESPACE XYZINDX
    PAGESIZE 8K
    MANAGED BY DATABASE
      USING (FILE '&2\XYZINDX.F1' 100M)
    BUFFERPOOL XYZBUFF;
 
CREATE LARGE TABLESPACE XYZBLOB
    PAGESIZE 8K
    MANAGED BY DATABASE    
      USING (FILE '&2\XYZBLOB.F1' 64000)
    EXTENTSIZE 32
    PREFETCHSIZE 32
    BUFFERPOOL XYZBUFF;

TABLES
All tables are created as follows:

CREATE TABLE X (COLUMNX INT) IN XYZDATA INDEX IN XYZINDX LONG IN XYZBLOB
mbevilacquaAsked:
Who is Participating?
 
mglxxxCommented:
That's correct, of course. With SMS separation of data and indexes into separate tablespaces is not possible.
However, one should think about whether this is actually necessary.
The last larger DB2 project I was working on used DB2 UDB as backing store for a workflow system. The size of
that DB was abt. 250GB with approx. 10 transactions per second. I think this shows that you can have a
DB2 DB with SMS and still very satisfactory performance. If you find that you need to separate data and indexes, then
by al means use DMS. The downside of that, however, is higher administrative overhead, i.e. more work for the
DBAs.
0
 
ghp7000Commented:
regarding bufferpool size, if pagesize=8k (8192  bytes) then 500*8192=4,096,000 or 4MB in memory.
if you are storing blobs in large tablespace, then set the pagesize of the table space to the expected size of the the blob. For example, if each blob is 16K in size, increase the tablespace size to 16K. If the expected average blob size is 5k, the set the tablespace size to 4 or 8k, it doesnt matter, either size will result in page splits.
Have alook at this link before you go ahead with your DMS tablepsaces:
http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/t0004953.htm?resultof=%22%63%72%65%61%74%65%22%20%22%63%72%65%61%74%22%20%22%62%75%66%66%65%72%70%6f%6f%6c%22%20
you may also want to read the "realted Concepts" at bottom of page
I think you should stick with the keep it simple principle and try not to get too fancy.
if you do
db2 create database test
the size of the default bufferpool is 250 pages*4k
So I think 500 pages*8k is ok, but your question is really impossible to answer. Why not run a few tests with what you expect an avergae size installation to be and set the parameters acccordingly?
0
 
mbevilacquaAuthor Commented:
If I change the pagesize to anything other than the bufferpool value, I get an error:

CREATE BUFFERPOOL XYZBUFF
    IMMEDIATE  
    ALL NODES
    SIZE 50000
    PAGESIZE 8K
    NOT EXTENDED STORAGE;

CREATE LARGE TABLESPACE XYZBLOB
    PAGESIZE 32K
    MANAGED BY DATABASE    
      USING (FILE 'c:\DB2\container\webm\XYZBLOB.F1' 64000)
    EXTENTSIZE 32
    PREFETCHSIZE 96
    BUFFERPOOL  XYZBUFF


ERROR:
 [Error Code: -1582, SQL State: 428CB]  The PAGESIZE of the table space "WEBMBLOB" does not match the PAGESIZE of the bufferpool "WEBMBUFF" associated with the table space.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
momi_sabagCommented:
hi

when you associate a tablespace and a bufferpool their page sizes must match, that is, be the same
for a 32kb pagesize tablespace, you have to define a 32kb pagesize bufferpool

momi
0
 
mglxxxCommented:
As ghp7000 already said, this question is impossible to answer unless we know something about the
expected usage of the database. As a side note this is one of the very few setups I've seen using DMS
tablespaces. I've been using SMS on DWH installations having approx. 6TB online.
0
 
ghp7000Commented:
welcome back mglxxx! please inform me (us) where you have been hiding all this time?
0
 
mglxxxCommented:
Hi ghp7000! Nice to see you still active on experts exchange. As to my long absence: that's a loooooooooooong story ;-)
0
 
ghp7000Commented:
ok, well I'd like to hear it one day but great to have your expertise back
0
 
mbevilacquaAuthor Commented:
How does one split data and index on separate tablespaces and NOT use DMS?
0
 
momi_sabagCommented:
one can not do that
look at the create table reference on the index in clause
http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0000927.htm

momi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.