Solved

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

Posted on 2011-03-07
5
624 Views
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.
0
Comment
Question by:wrenny_250106
5 Comments
 
LVL 4

Accepted Solution

by:
gmarino earned 500 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:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.partition.doc/doc/t0005440.html
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
0
 
LVL 142

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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

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 (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

810 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