[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Is DB2 partitioning a possiblity. for a database with 1TB size and single partition

Posted on 2011-03-07
Medium Priority
Last Modified: 2012-05-11
One of the database is approx 1TB in size and it is currently defined into 1 partition within a limited number of mountpoints.  
 As the database continues to grow, this large sized database would take an excessive amount of time to recover if that was ever necessary.  
Also,  DB2 performance is hindered as the database continues to grow without the aid of db2 partitions.

Can any one provide recommendation / suggestions that can be made to address this issue.  Also, provide a detailed plan in other ways to address this issue.
Few of the questions:
Is DB2 partitioning a possiblity.  If so, what is required to migrate the database to multi-partitioning?
What would be required from a migration or conversion effort?
Would the db2 backups work the same way?   Benefits in recovery?
What is required to test this?Are there other suggestions / definitions that can be made in the way the data is organized to improve backup times, performance and recovery SLA's ?

Additional questions would will be added as more info is known.
Question by:wrenny_250106

Accepted Solution

gmarino earned 2000 total points
ID: 35064019
A Partitioned database is really just multiple databases acting together as one.  You setup multiple databases (called Partitions or Nodes) and split the data in a logical manner across the Nodes.

If you already have a non-partitioned database, you will have a bit of work ahead of you to partition it.  I just partitioned a non-partitioned database last week.  You can read the manual:
However these steps are for adding partitions to an already partitioned instance.  Converting a non-partitioned db/instance is almost the same.

First, if you have never worked with a Partitioned Database/Instance, you will need to learn what it is and how it behaves differently than a non-partitioned db/instance.  Without this knowledge you are bound to get lost quickly and easily.

What I did last week was start with a non-partitioned instance with a non-partitioned database in it.  The first step is to change the instance to be partitioned.  This is fairly easy.  Your existing node is DB2NODE=0 (NODE0000).  You can add nodes by issuing the following commands:

db2start DBPARTITIONNUM 1 ADD DBPARTITIONNUM HOSTNAME <servername> port 1 without tablespaces
db2start DBPARTITIONNUM 2 ADD DBPARTITIONNUM HOSTNAME <servername> port 2 without tablespaces
db2start DBPARTITIONNUM 3 ADD DBPARTITIONNUM HOSTNAME <servername> port 3 without tablespaces

This will add 3 new partitions to your instance.  One thing this does is update the db2nodes.cfg file found in $INSTHOME/sqllib/.  The 'port' number in the command above refers to the LOGICAL PORT in the db2nodes.cfg (read the manual about this LOGICAL PORT.

Once this is done, recycle the instance.  You will also need to back up the database in the new nodes.

I used the WITHOUT TABLESPACES option since I wanted control over the container placement for the System Temp Tablespaces.   You will have to manually issue the ALTER TABLESPACE command to add containers to the System Temp Tablespaces for each partition  

This now gives you a non-partitioned Database living in a Partitioned Instance.  The next step is to determine the partitioning key for each table.   Saying that in one sentence is a gross oversimplification of what that really means.  You need to have an understanding of the data in each table and understand what queries will be executing against those tables in order to determine the most appropriate column to use to DISTRIBUTE the table across the partitions.  If you get this wrong, you will find that DB2 is shipping large amounts of data from one node to another just to join 2 tables together.  

Once you determine the proper partitioning keys, you will create new partitioned tablespaces (wtih containers in each node) and then copy the data from the old non-partitioned table into the new partitioned table.  If you are so lucky to be running DB2 v9.7 AND have your existing tables in tablespaces that were originally CREATED in v9.7, you can use the ADMIN_MOVE_TABLE command to move AND partition your tables at the same time - and do all of this ONLINE while users are accessing the table.  ADMIN_MOVE_TABLE is one of the greatest features of v9.7.  Read about how it works and your jaw will drop to the ground like mine did.

In the end, the actual conversion to a partitioned database is not really all that hard.  HOWEVER if it is not properly designed and planned, you will find yourself with a database that performs even worse than your existing 1TB non-partitioned DB.

1TB of data is NOT that much data in this day and age - especially with the latest hardware and 64-bit systems.  You will want to make sure you have exhausted all of the performance tuning efforts available to you before you  undertake moving to the crazy world of partitioned databases.  

Yes, you will see the maintenance benefits - Reorgs, runstats and Backup/Recovery will run faster.  You queries might run faster if the partitioning is properly defined.

If I was a freelance DBA, I'd suggest you drop me an email and we could talk about performance tuning on and cleaning up your existing database and/or about moving to partitioning (and I could make some money). But alas, I am happily employed...

- Greg
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35913519
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Screencast - Getting to Know the Pipeline
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question