Solved

db2 bufferpool and tablespace review

Posted on 2006-11-09
10
956 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:mbevilacqua
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 13

Assisted Solution

by:ghp7000
ghp7000 earned 250 total points
ID: 17908559
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
 

Author Comment

by:mbevilacqua
ID: 17909158
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17909273
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
 
LVL 9

Expert Comment

by:mglxxx
ID: 18417968
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
 
LVL 13

Expert Comment

by:ghp7000
ID: 18419392
welcome back mglxxx! please inform me (us) where you have been hiding all this time?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 9

Expert Comment

by:mglxxx
ID: 18420267
Hi ghp7000! Nice to see you still active on experts exchange. As to my long absence: that's a loooooooooooong story ;-)
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 18420644
ok, well I'd like to hear it one day but great to have your expertise back
0
 

Author Comment

by:mbevilacqua
ID: 18423678
How does one split data and index on separate tablespaces and NOT use DMS?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 18425648
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
 
LVL 9

Accepted Solution

by:
mglxxx earned 250 total points
ID: 18425900
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now