Learn how to a build a cloud-first strategyRegister Now


How To Partition a Table, DB2 V9.5 database

Posted on 2009-04-30
Medium Priority
Last Modified: 2012-05-06
I want to partition a DB2 table so that archiving can be easy every year.
Can you please assist with documentation, scripts etc.
Question by:kbanuso
1 Comment
LVL 46

Accepted Solution

Kent Olsen earned 1500 total points
ID: 24273514
Hi kbanuso,

I suspect that you want to partition by date.  That's pretty common.  :)

A table is partitioned (or not) based on the options selected when it is created.

CREATE TABLE <NAME> (<column_name>  <data_type>  <null_attribute>)
IN <table space list>
PARTITION BY RANGE (<column expression>)
STARTING FROM <constant>
ENDING <constant>
EVERY <constant>

Here's an example (from IBM) that partitions a table in 3 month increments:

CREATE TABLE customer (l_shipdate DATE, l_name CHAR(30))
IN ts1, ts2, ts3, ts4, ts5
PARTITION BY RANGE(l_shipdate) (STARTING FROM ('01/01/2006')
ENDING AT ('12/31/2006') EVERY (3 MONTHS))

Jan/Feb/Mar are in 1 partition, Apr/May/Jun the next partition, etc.

Something like this should work for you,

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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 (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…
Integration Management Part 2
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

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