Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2005
    Posts
    108

    Unanswered: Why partitioned DB?

    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.

  2. #2
    Join Date
    Feb 2005
    Location
    United States
    Posts
    22
    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.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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


    Quote Originally Posted by DBA-Jr
    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.
    Last edited by sathyaram_s; 03-27-06 at 13:48.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2dcs
    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).
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by DBA-Jr
    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.
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Mar 2005
    Posts
    108
    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?

    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.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Dec 2005
    Posts
    2
    DPF helps get around tablespace size limits too e.g. 256GB using a 16K pagesize PER PARTITION.

  9. #9
    Join Date
    Mar 2005
    Posts
    108
    Thanks for all the inputs.
    Can somebody share the bebefits actually gained by using logical partitioning on one physical server?

  10. #10
    Join Date
    Jul 2002
    Posts
    48
    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.

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by DBA-Jr
    Thanks for all the inputs.
    Can somebody share the bebefits actually gained by using logical partitioning on one physical server?
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •