How To Partition a Table, DB2 V9.5 database

Posted on 2009-04-30
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 45

    Accepted Solution

    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

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    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…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now