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

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.
Who is Participating?
gmarinoConnect With a Mentor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.