Hi All,
I am not clear about the benefits of DB2 partitioned database given the followong cons:
1. complicated to manage and handle
2. costs spended on inter-partition communication and coordination, and hashing
3. the only benefit - parallel processing of partitioned DB, can also be achieved by intra-partition parallelism.

Can you give any input? Thanks.

I am not a DBA. but we decided to partition our database as a hedge against DB2 contention failures (SQL -911 in Z/OS DB2).
Specifically, the database in question is shared by several applications who may be running concurrent batch processes against this database. Since our locking is only enforced at the 'page-level' and not the row-level, then there was a possibility that different applications would contend with each other.
The Application-ID was defined as a primary key column on all of the tables.
By partitioning the database based on the Application-ID, then we can protect applications from clobbering each other due to contention - as long as the data they load specifies their own Application-ID.
Now this solution will not protect against contention when the same application is running many concurrent jobs using the same Application-ID, but at least we can protect against contention between concurrent jobs from different applications.

I do agree with to some extent ... With the hardware advancements, the hash-partitioned database environment is becoming less attractive than in the past ..

But, for larger dw systems - 10-s of terabytes - which is not uncommon, I still think - shared nothing architecture is an option.

My 2 cents

There are a few regulars to this forum who are DPF users ... I hope they share their views

Cheers
Sathyaram

Range partitioning in DB2 for z/OS is completely different than hash partitioning in DB2 for Linux, UNIX, Windows.

DB2 for Linux, UNIX, Windows will add range partitioning in V9 later this year.

For large scale parallel processing it is often desirable to run the database on multiple physical nodes to obtain linear scalability. In a single large multiple CPU system, intra-partition parallelism must share the CPU's, memory, disk controllers, etc, and therefore does not scale in a linear fashion.

If your database is not too large then an intra-partition parallelism on a single node may be perfectly adequate for your application. But many companies have very large databases that need the linear scalability using multiple physical nodes that is provided by DPF.

Thanks for all replies.

Suppose I have an 8-CPU server, with 8G memory in total. By using intra-partition, one query may be split into 8 subqueries, each for one CPU, and they all share the 8G memory. By using inter-partition (e.g., 8 partitions), one subquery may have a "dedicated" CPU and 1G "dedicated" memory. Are these right? If so, why the second case is better by having "dedicated" resource than the first case which shares?

My second question is: When is a DB considered BIG enough to be partitioned, several 100G, or serveral tera-bites?

Intra-partition parallelism only works in certain cases, such as a UNION ALL view with multiple tables. Load balancing is usually not as good as with DPF.

With DPF all tables that are partitioned will have parallel query (which is primarily appropriate for table scans). But DPF requires an additional license fee on top of the ESE license.

With a single 8 CPU server, you need to evaluate how well any type of parallelism will benefit you. Besides having enough CPU's, you need to have a disk subsystem that has multiple controllers and arrays, or can handle concurrent I/O for each partition or degree of parallelism without being the bottleneck.

If you feel comfortable with the UNION ALL view on 4-8 tables, and have a disk subsystem that can handle simultaneious access to 4-8 containers at one time (you need a separate container for each degree of parallelism), then I would probably go with intra-partition parallelism. DPF is primarily for situations were you need multiple physical nodes either now or in the future(although I have seen some use it successfully on a single large SUN SMP box).

DPF helps get around tablespace size limits too e.g. 256GB using a 16K pagesize PER PARTITION.

Thanks for all the inputs.
Can somebody share the bebefits actually gained by using logical partitioning on one physical server?

logical partitioning is done in the o/s level also. through which you are getting the benefits for more than 4 GB memory per partition in 32 bit machine.

Typically, if you have more than 1-2 CPU's per server, you will want more than one partition per server, since each parallel agent is using only one CPU.

So if you have 4 physical servers, each with 8 CPU's, you might want 24-32 partitions (6-8 partitions per server).

Fairly inexpensive Intel/AMD based servers with 1-2 CPU's each can be used with 1 partition per server, but you would not want to do that with an expensive 8-way server.