Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2002
    Posts
    1

    Unanswered: DB2 equivalent to Oracle's Partitioned Tables

    I have a table in Oracle that is designed using ranged partitions. I am converting the database to DB2 and wanted to know if version 7.2 has a similar function.

    Thanks,
    Jeff

  2. #2
    Join Date
    Jan 2002
    Location
    Manila, Philippines
    Posts
    71

    db2 for OS/390

    have partitioned table space, which can store a single table which will be partitioned via the partitioning index (range).

    i think there's a different partitioning concept in db2 for windows.

    hth,
    Oliver

  3. #3
    Join Date
    Sep 2002
    Posts
    30
    On Unix platform like AIX, HP-UX, Linux, Solaris and Windows NT/W2K, there is a DB2 version called Enterprise Extended Edition (EEE) which provide partitioning feature. It means the table could be partitioned on a nodegroup which may be across several machines or on a SMP machine.

    The record/row that will be distributed on which partition is based on a partitioning key defined on a table, it will use a hashing function to hashed the partitioning key to a partition number between 0 and 4095. That number is used as an index into the partitioning map to select the database partition for that row.

    For the detail, you may reference the DB2 UDB Admin. Guide.

  4. #4
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152

    Re: DB2 equivalent to Oracle's Partitioned Tables

    Originally posted by jcott28
    I have a table in Oracle that is designed using ranged partitions. I am converting the database to DB2 and wanted to know if version 7.2 has a similar function.

    Thanks,
    Jeff
    No. DB2 has no equivalent feature for range partitioning.
    DB2 EEE can partition the data based on a hash key,
    but EEE might not be the optimal choice for an OLTP
    environment because of the increased overhead due to the
    necessary communication between the nodes.

    For data warehousing DB2 EEE is is an excellent solution.

    You might consider thinking about Informix. This databaseserver
    has round-robin and range partitioning build into the engine
    for several years.

    HTH.

    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

  5. #5
    Join Date
    Nov 2003
    Location
    Bangalore
    Posts
    2

    Re: DB2 equivalent to Oracle's Partitioned Tables

    Is "Range Partitioning" supported in any of the latest releases of DB2?

    If we were to do a "Monthly Partition", hash is obviously not the best choice.

    Is there nothing equivalent to "Partition Pruning" in DB2? If not, then selective scanning of rows when a query comes in requesting data for a particular month is ruled out!!!

    How does DB2 handle this?

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Range partitioning is available on DB2 for z/OS and OS/390, but not other DB2 platforms. However, range partitioning with UNION ALL views is very close to range partitioning (and in some respects better), and it performs quite well (and enables parallel processing). Also, it does not require multiple partitions or nodes. Here is an article about it:
    http://www-106.ibm.com/developerwork...202zuzarte.pdf

    DB2 version 7 EEE and version 8 ESE with DPF work quite well in an OLTP environment. With buffered inserts, extremely high transaction rates can be achieved.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Dec 2002
    Posts
    134

    Re: DB2 equivalent to Oracle's Partitioned Tables

    Originally posted by raja_neelam
    Is "Range Partitioning" supported in any of the latest releases of DB2?

    If we were to do a "Monthly Partition", hash is obviously not the best choice.

    Is there nothing equivalent to "Partition Pruning" in DB2? If not, then selective scanning of rows when a query comes in requesting data for a particular month is ruled out!!!

    How does DB2 handle this?
    In 8.1 (ideally use fixpack 4) there is a notion of "union all" view, that can be used for insert,delete,update, select. The tables that are part of the union all view, must be created with check constraint uniquely identifying which table to use, like table1 - date betwen 2001 and 2002, table2 - date between 2002 and 2003 and so on.
    During DML operations optimizer will be able to figure out what table to use. You should be able to see what tables will be affected if you do an explain.
    This feature is similar to the oracle partitioning. You can drop and create "table-partitions", move data across using regular update(fixpack 4). The only disadvantage - you have to recreate the view if you do drop/create. There are also some limitations on the number of partitions you can have.

    In general we can say, that thre are 3 types(levels) of partitioning in DB2 (you can combine any of them):
    MDC/RCT - partitioning within a table, by extent
    "Union all" view - partitioning within a db
    EEE(ESE with DPF) - partitioning across computers

Posting Permissions

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