mbevilacqua
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
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.
welcome back mglxxx! please inform me (us) where you have been hiding all this time?
Hi ghp7000! Nice to see you still active on experts exchange. As to my long absence: that's a loooooooooooong story ;-)
ok, well I'd like to hear it one day but great to have your expertise back
ASKER
How does one split data and index on separate tablespaces and NOT use DMS?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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\XYZ
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.