Solved

db2 bufferpool and tablespace review

Posted on 2006-11-09
10
959 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
OleDb Command Timeout setting is ignored when calling DB2 stored procedure 8 495
db2 - u 3 229
DB2 CONCAT FAILING 4 108
iSeries DB2 - Query with Sub Query? 7 103
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
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…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

895 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

15 Experts available now in Live!

Get 1:1 Help Now