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.

Any feedback on what I have so far? Am I totally deluded?
Cheers in advance.

Was my initial post too broad? I am mostly interested at the moment of the consequences of using RAID-5, since there is one logical parition I am using, does multiple containers give any benefit? Am I making the right chioce for tablespace/extent/prefetch sizes?

Cheers.

You will not get any benefit from multiple containers on a single Raid array, especially with a single CPU. If you have multiple Raid arrays, put one container on each array (number of containers = number of arrays).

I would assign at least 1.5GB of RAM to the bufferpools. I would put small and medium size tables, all indexes, and syscatspace in one very large buffer pool (about 75% of total bufferpool memory). Then put the large tables and the system temporary tablespace in another bufferpool (about 25% of total bufferpool memory). You will need to assign different tablespaces for the indexes when you create the tables.

Make sure you have a very large tablespace for system temporary tablespace. The best way to do this is to use SMS on a volume with lots of free space. Assign this tablespace to the same bufferpool as the large tables. For OLTP applications SMS is actually better than DMS because there is less overhead in creating a table in an SMS tablespace, which is important in OLTP where the extra create table overhead is a larger percentage of the transaction than would be the case for a decision support query.

For best results, you will need to look at the other DB and DBM parms. For OLTP systems you should increase the locklist and the logbufsz dramatically. Make sure you increase the dbheap by the same amount as the logbufsz.

Also look at log placement, which ideally should be on different disks (or a different array) for the data.

OK so I only have one RAID array, so I'll only use one container per tablespace.

In my current test implementation I have assigned approx. 55% of the bufferpool to the main tablespace with the small mid-sized tables, 10% to the largest fact table/tablespace, 5% to the other large fact table/tablespace and 30% to the syscatspace. I will probably re-think this distribution with your suggestions in mind. Thanks.

Have you any suggestions or guidelines or even resources/articles I can look at to determin the best TBSpace page/extent/prefetch for my hardware setup (single RAID-5 array etc.)

Thanks for the input thus far

Gerry.

30% of the total buffer pool storage for the syscatspace is way too large. Most of the catalog information is cached in Catalog cache (catalogcache_sz). Like the logbufsz, catalogcache_sz comes out of dbheap, so be sure to increase dbheap by the same amount you increase catalogcache_sz. Also look at package cache size.

I would not put each of the fact tables in their own buffer pools (they can share a single buffer pool for large tables). Too many bufferpools result in inefficient use of storage. Although no two people will come up with the same buffer pool recommendations, and it is sort of an art (rather than science), I will stick with my earlier recommendation.

For an OLTP system, you want to discourage prefetch activity (under the assumption that almost all transactions will retrieve a small number of rows via index access). Usually, the prefetch size is the extent size times the number of containers (in your case they would be equal). You could make it larger, but you may not want to do that if you want to optimize for OLTP.

The Control Center Create Tablespace wizard does a pretty good job of recommended settings. You could try it out (you can just look do the “Show Command” at the end and you don’t have to submit it via the Control Center). OLTP performance is much more dependent on buffer pool memory, while queries that do a lot of tablespace scans depend more on tablespace configuration for performance.

If you are using version 8 fixpak 4 or later, you may want to look at MDC (multi-dimensional clustering) for your fact tables.

OK good stuff, I'll definitely be revisiting the allocation. It's actually been quite a while since I initially did the setup for this thing as the project went on hold for a while, to be honest, I can't remember the logic(if thre was any :P) behind allocating that much BP to the syscat.
Anyways, thaks a million for your help here and in the locking thread.

Cheers,

Gerry.