Partitioning In DB2

[Jane]

In Oracle we can partition a table as follows. What is the equivalent in DB2?

CREATE TABLE sales_list
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_stat e)
(
PARTITION sales_west VALUES ('a','b'),
PARTITION sales_east VALUES ('e', 'f'),
PARTITION sales_central VALUES ('c', 'd')
);

We are using DB2 8.1.3 on AIX.

Thanks,
-Jane

[Mark A]

Re: Partitioning In DB2

"Jane" <jane_estrada@i 2.com> wrote in message
news:75f068bb.0 407301431.725f4 9a5@posting.goo gle.com...[color=blue]
> In Oracle we can partition a table as follows. What is the equivalent in[/color]
DB2?[color=blue]
>
> CREATE TABLE sales_list
> (salesman_id NUMBER(5),
> salesman_name VARCHAR2(30),
> sales_state VARCHAR2(20),
> sales_amount NUMBER(10),
> sales_date DATE)
> PARTITION BY LIST(sales_stat e)
> (
> PARTITION sales_west VALUES ('a','b'),
> PARTITION sales_east VALUES ('e', 'f'),
> PARTITION sales_central VALUES ('c', 'd')
> );
>
> We are using DB2 8.1.3 on AIX.
>
> Thanks,
> -Jane[/color]

In DB2 for LUW you cannot partition a single table by ranges, only by a hash
key that is usually designed to randomly distribute the data evenly across
all partitions for improving parallel processing.

In DB2 you can create separate tables for each partition and create a UNION
ALL view which allows programs to see them as one table, and which has
almost of all of the advantages of range partitioning of a single table.
Please refer to the following article about this:

http://www-106.ibm.com/developerworks/db2/library/techarticle/0202zuzarte/0202zuzarte.pdf

[Mark Townsend]

Re: Partitioning In DB2

Mark A wrote:[color=blue]
> "Jane" <jane_estrada@i 2.com> wrote in message
> news:75f068bb.0 407301431.725f4 9a5@posting.goo gle.com...
>[color=green]
>>In Oracle we can partition a table as follows. What is the equivalent in[/color]
>
> DB2?
>[color=green]
>>CREATE TABLE sales_list
>> (salesman_id NUMBER(5),
>> salesman_name VARCHAR2(30),
>> sales_state VARCHAR2(20),
>> sales_amount NUMBER(10),
>> sales_date DATE)
>> PARTITION BY LIST(sales_stat e)
>> (
>> PARTITION sales_west VALUES ('a','b'),
>> PARTITION sales_east VALUES ('e', 'f'),
>> PARTITION sales_central VALUES ('c', 'd')
>> );
>>
>>We are using DB2 8.1.3 on AIX.
>>
>>Thanks,
>>-Jane[/color]
>
>
> In DB2 for LUW you cannot partition a single table by ranges, only by a hash
> key that is usually designed to randomly distribute the data evenly across
> all partitions for improving parallel processing.
>
> In DB2 you can create separate tables for each partition and create a UNION
> ALL view which allows programs to see them as one table, and which has
> almost of all of the advantages of range partitioning of a single table.
> Please refer to the following article about this:
> http://www-106.ibm.com/developerwork...202zuzarte.pdf
>
>[/color]
Just to be truely pendantic, the Oracle example above is showing list
partitioning, not range partitioning (although the example, as it stands
in it's simplicity, could be done with range partitioning).

[Jane]

Re: Partitioning In DB2

Thanks for the response.

1)The "UNION ALL" approach requires multiple tables with unique data.
Can we use a VIEW to do inserts and updates to the partitioned tables
in DB2 UDB 8.1.3? Or do we have to change the application to recognize
these patitioned tables?

2)Let me modify my original question ... what is the most common way
to partition a table within a DB2 database to improve performance?

Thanks Again.
-Jane

[Mark A]

Re: Partitioning In DB2

"Jane" <jane_estrada@i 2.com> wrote in message
news:75f068bb.0 408030943.1d4a1 961@posting.goo gle.com...[color=blue]
> Thanks for the response.
>
> 1)The "UNION ALL" approach requires multiple tables with unique data.
> Can we use a VIEW to do inserts and updates to the partitioned tables
> in DB2 UDB 8.1.3? Or do we have to change the application to recognize
> these patitioned tables?
>
> 2)Let me modify my original question ... what is the most common way
> to partition a table within a DB2 database to improve performance?
>
> Thanks Again.
> -Jane[/color]

A UNION ALL view may be updatable. Check the "SQL Reference" on CREATE VIEW
for more information about insertable, deleteale, and updateable views. Many
people use the LOAD command to add data to tables in a UNION ALL view, so in
that case the exact table would need to be known by the LOAD command.

Generally, range partitioning (or list partitioning) does not improve
performance of a single query (although in some cases it could increase
overall throughput if a lot of queries are submitted on different partitions
at once.

Hash partitioning (available in DB2 LUW) is the best way to increase
performance, since it makes use of parallel operations for each large query
(usually when there are large table or index scans). Parallelism usually
does not help much if the query will use an index to only return a few rows.

But in order to effectively use parallel processing, your hardware needs to
be set up to exploit it. This means having multiple processors, and
carefully spreading the data across multiple disks (or multiple arrays) in
order to exploit parallel processing. You also need the DPF option of DB2
version 8 ESE to be licensed on the machine (or have DB2 version 7 EEE). If
you plan to use multiple partitions on DB2 (as opposed to a UNION ALL view)
you will want to get some advice on how the partition the data for optimal
performance or take a class in how DB2 parallel database works.

[Serge Rielau]

Re: Partitioning In DB2

Jane,

DB2 V8.1 GA supports INSERT, UPDATE and DELETE through UNION ALL.
The requirement for INSERT is that you have check-constraints which
enforce the partitioning. Typically these constraints are fairly simple
(BETWEEN and <=, > kind of stuff).
In V8.1.4 support was enhanced in two ways:
1. UPDATE allows for row-movement.
You can update a the "partitioni ng" key of the view in a way that a
row gets moved from one table to another
2. Performance improvements for UPDATE/DELETE/INSERT to the point
that in common cases the overhead of the UNION ALL becomes
neglegible.

So: No you don't need your app, but moving up to teh latest fixpack may
be a good idea.

When talking about partitioning for performance you also should take a
look at
"Multi dimensional clustering". You may gain p to two orders of
magnitudes in the best case on queries.

Cheers
Serge

[Jane]

Re: Partitioning In DB2

Thanks for the information.

I'll look into Hash partitioning and multidimensiona l clustering.

-Jane

[Fred]

Re: Partitioning In DB2

jane_estrada@i2 .com (Jane) wrote in message news:<75f068bb. 0408030943.1d4a 1961@posting.go ogle.com>...
[color=blue]
> 1)The "UNION ALL" approach requires multiple tables with unique data.
> Can we use a VIEW to do inserts and updates to the partitioned tables
> in DB2 UDB 8.1.3? Or do we have to change the application to recognize
> these patitioned tables?[/color]

I implemented partitioning via UNION ALL and used an INSTEAD OF INSERT
trigger to handle inserts into the view. I had to do this because most
of the data was still in the original table, so I could not impose the
mutually exclusive column constraints that would automatically route
incoming rows to the proper partition table.

By implementing partitioning via UNION ALL, I was able to address the
performance challenges of a heavily skewed dimension column. I moved
the rows with the popular dimension values to an MDC table, and left
the other rows in a table with a clustered index. Had I used MDC on
all of the partitions, the less popular dimension values would have
wasted too much disk space.

Good luck,

Fred