473,595 Members | 2,442 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Partitioning In DB2

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
Nov 12 '05 #1
7 7230
"Jane" <ja**********@i 2.com> wrote in message
news:75******** *************** ***@posting.goo gle.com...
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


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
Nov 12 '05 #2
Mark A wrote:
"Jane" <ja**********@i 2.com> wrote in message
news:75******** *************** ***@posting.goo gle.com...
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

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

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).

Nov 12 '05 #3
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
Nov 12 '05 #4
"Jane" <ja**********@i 2.com> wrote in message
news:75******** *************** ***@posting.goo gle.com...
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


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.

Nov 12 '05 #5
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
Nov 12 '05 #6
Thanks for the information.

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

-Jane
Nov 12 '05 #7
ja**********@i2 .com (Jane) wrote in message news:<75******* *************** ****@posting.go ogle.com>...
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?


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
Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

18
6337
by: Jeff Boes | last post by:
I'm sure this is a concept that's been explored here. I have a table (fairly simple, just two columns, one of which is a 32-digit checksum) with several million rows (currently, about 7 million). About a million times a day we do select * from my_table where md5 = ? to verify presence or absence of the row, and base further processing on that information.
2
8816
by: jorge | last post by:
I have just started working with DB2 Data Partition Feature.. Given a table name, how can I find out the partitioning key of that table? Is there a SYSIBM.* table that I can look at? Thanks, Jorge
0
1615
by: Robert Klemme | last post by:
Hi, this is just to verify: I checked the documentation and it seems that SQL 2005 does not support hash partitioning on a number of columns. As far as I can see only range partitioning is supported. Also, it seems SQL 2005 only supports partitioning on a single column. Is that true or did I miss something? Kind regards
10
3545
by: shsandeep | last post by:
DB2 V8.2 (not Viper yet and no range partitioning!!) I have created a table T1 (col1, col2) with col1 as the primary key. When I try to create a partitioning key on col2, it gives me error that it should have all primary keys included. So, I created table T1 again with col2 as the partitioning key. Now, I do not have col1 as the primary key. When I try to create col1 as the primary key, I get the following error: 1 The primary key, each...
8
3040
by: mitek | last post by:
Hi, All I have strange situation with table design for DB2 9.1 on Windows I have 3 tables with same structure : 1 table - is MDC 2 table - is partitioned MDC table 3 table - is compressed partitioned MDC table Each table in separate DMS tablespace
0
1983
by: harrylarenson | last post by:
Hi, Happy New Year. I am trying to insert a query to a partitioned view but the error is : Server: Msg 4436, Level 16, State 12, Line 1 UNION ALL view 'T' is not updatable because a partitioning column was not found. Here is all my query statements : create table t1 (ID int primary key, Code int check(code between 1 and 15 ))
0
1860
by: Nate Eaton | last post by:
According to the original whitepaper on UDB range partitioning (http:// www-106.ibm.com/developerworks/db2/library/techarticle/0202zuzarte/ 0202zuzarte.pdf), you can use a range as a criteria, either in the UNION ALL view or in constraints. One restriction it lists, though, is that the optimizer can't use a constraint that references a range versus a discrete value for SQL containing host variables or parameter markers. That was for V7 and...
15
3669
by: Piero 'Giops' Giorgi | last post by:
Hi! I have a question: I already have a DB that uses partitions to divide data in US Counties, partitioned by state. Can I use TWO levels of partitioning? I mean... 3077 filegroups and 50 partition functions that address
2
2010
by: mandor | last post by:
Hello, I need some advise in table design, and more specifically about table partitioning. I read some papers and there was mentioned that if a table is expected to hold millions of rows, it's a good idea to partition it. Vertical partitioning, as I understood it, is separating data that differs in some way in a separate table, adding a key field as an identifier to what segment it belongs. The particular table holds signal measurements...
0
7957
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
7883
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8262
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8379
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
6675
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5839
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5421
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3875
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
1226
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.