Solved

Planning/Implementation Advice

Posted on 2004-09-06
5
469 Views
Last Modified: 2008-03-06
Hey all,

I Am about to set up a DB2 server and wanted to get a critique of what I plan to do. Please feel free to rip this to shreds

Server:
Win2k Server
IBM Netfinity
Xeon 2.4GHz
2.5GB RAM
IBM ServRAID 5i
RAID-5 (6 disks, stripesize 16k)
HDD 4.7 avg seek, 10,000 rotation TR 320

Database:
The database will be combined OLTP/Queries (Its a financial services system for a small/mid sized company, 70 users using a Java/Browser based front end). Reporting will also be done off this server (mostly out of hours).
I will have 2 massive fact tables which make up about 1/3 of overall data.

I have turned on DB2_STRIPED_CONTAINERS and set DB2_PARALLEL_IO=*

The area I am mainly interested in planning at the moment is physical layout (altho I welcome tips on any relevent area).

Here are my plans:
Put most tables in the userspace1, assigning most of the bufferpool to that space.
Create a DMS tablespace for the 2 big tables, giving eact one 3 containers of about 3GB each.
I intend to make the extent=16 and the prefetch=64.

I am using, does multiple containers give any benefit? Taking RAID into account etc. am I making the right chioce for tablespace/extent/prefetch sizes?
Any feedback on what I have so far? Am I totally deluded?
Cheers in advance.
0
Comment
Question by:meehange
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:BigSchmuh
ID: 11991116
After some years, my general (means for every dbms) rules of thumb are:
-Raid 5 (or 0+1 when possible because it's really faster) for all logical volumes (+hotswap, hotplug and a spare disk in the bay)
-The db Log files dedicated on a volume (many writes rare read)
-Application + System + db Temp on a volume (some big chunk read&write)
-Index on a small very fast volume (Lowest seek time)
-Data on a large volume (Highest bandwidth)
==> If I can not afford that many volumes :
-step 1 : share db Logs with application-system-temp
-step 2 : share Data and Index

With DB2, the fact that tablespaces are linked to memory cache (bufferpools) make it slightly different because you may imagine having as many tablespaces as pagesize you use...but in fact you don't really care.
Try having prefetch/extent adjusted on your physical page size (means use a medium multiple of your 16K stripesize).
==> Next step is : Monitor your db using the excellent freeware below instead of those terrible db2 "tools"
   http://members.tripod.com/chuzhoi_files/

Hope this helps.
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 11999497
what version of db2 are you going to use?
is this an ee setup or an eee setup?
what growth rate do you anticipate for the large tables?
how many indexes do you plan to have on the large tables?
are the users connecting via internet? intranet?
0
 
LVL 1

Author Comment

by:meehange
ID: 12002504
>what version of db2 are you going to use?
Currently 7.2 moving to 8.1 this week

>is this an ee setup or an eee setup?
Workgroup Server Edition

>what growth rate do you anticipate for the large tables?
Hard to say, I'd guess the whole thing will grow by about 1GB per year

>how many indexes do you plan to have on the large tables?
Will depend on tuning efforts once the application is finished

>are the users connecting via internet? intranet?
intranet
0
 
LVL 13

Accepted Solution

by:
ghp7000 earned 125 total points
ID: 12009758
I would say that in the scenario you have, there is no real benefit to DMS tablespaces, I would go with SMS, it is easier to manage. Set the DB2_PARALLEL_IO variable according to the scenario listed here:
http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/t0004987.htm?resultof=%22%44%42%32%5f%50%41%52%41%4c%4c%45%4c%5f%49%4f%22%20
I would put all the tables in the same tablespace, I would put the indexes in another tablespace.
I would mirror the logs (available in v8).
In version 8, DB2_STRIPED_CONTAINERS has no effect.

0
 
LVL 1

Author Comment

by:meehange
ID: 12050768
Thanks for the info, I found out this week that we'll be moving to Blades using SAN. So I guess I'll be doing some more research on that now :P
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
RPG to c# 3 340
ODBC connect from ms access to DB2 3 75
Can I insert a row into a MS SQL table from a SQLRPGLE program? 5 124
iSeries DB2 Query 2 91
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…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

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

16 Experts available now in Live!

Get 1:1 Help Now