• About Dangerous DBA
  • Table of Contents
Dangerous DBA A blog for those DBA's who live on the edge

DB2 Handle Table Partitioning

June 12, 2011 5:02 pm / Leave a Comment / dangerousDBA

This is the final post in the series on handling DB2 range partitioned tables, attaching table partitions, detaching table partitions and moving table partitions. In my production environments I have a number of partitioned tables, that once a quater need new partitions adding, old ones detaching and middling partitions moving to slower / cheaper storage as they are less hot. I will discuss data retention policy in an other article.

To do this I use the code from my previous posts (can be found at the top), using all three it is possible to detach, move and add partitions, although obviously can be changed to your needs. The code I use can be found below:

CREATE PROCEDURE DB_MAIN.HANDLE_PARTITIONING  (IN TABLESCHEMA VARCHAR(255),
						IN TABLENAME VARCHAR(255),
						IN DETACHPARTITIONNUM INT,
						IN DETACHEXPORTDIR VARCHAR(255),
 						IN MOVEPARTITIONNO INTEGER,
						IN MOVEPARTITIONDIR VARCHAR(255),
						IN MOVEPARTITONTABLESPACE VARCHAR(20),
						IN NEWTABLESPACE VARCHAR(20))
LANGUAGE SQL
BEGIN
   --Move the partition to the archive schema
      CALL DB_MAIN.MOVE_PARTITION(TABLESCHEMA, TABLENAME, MOVEPARTITONNO, MOVEPARTITIONDIR, MOVEPARTITIONTABLESPACE);

   --Detach the partition
      CALL DB_MAIN.DETACH_PARTITION(TABLESCHEMA,TABLENAME,DETACHPARTITIONNUM,DETACHEXPORTDIR);

   --Attach the partition
      CALL DB_MAIN.ATTACH_PARTITION(TABLESCHEMA,TABLENAME,NEWTABLESPACE);
END

The moving partitions occurs first so as other wise it will throw off the partition numbers that you put in. This procedure will create a backup copy of your data while it is moving it in the directory you define, it will not remove it and so you will be able to back this up to permanent storage safe keeping before you get rid of it or just get rid of it. Second comes the detaching of the old data  that is no longer relevant to the business, again a file is created of the detached data so that you can back it up to permanent storage. Thirdly is adding the new partition for the new data that you will be adding in the future.

The thing that I like about this code is that it will enable you too will produce extracts to two different locations, so on the server I have two locations one for archive data and one for moving data, I can then back these up to CD, DVD or external HDD for safe keeping.

DISCLAIMER: As stated at the top of the blog use this code in your production systems at your own peril. I have tested and know it works on my systems, please test and check it works on yours properly as handling partitions can potentially dangerous. The file is a .doc only as that’s the only way I could get it uploaded onto wordpress, it should open fine like that, or knock the .doc off and it will open in your favourite text editor

FILE WITH CODE IN: DB2 Handle Table Partitions SPs

Posted in: Attach Partitions, DB2 Administration, DB2 Maintenance, Detach table Partition, IBM, Move table partitions

Leave a Reply Cancel reply

Post Navigation

← Previous Post
Next Post →

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 757 other subscribers

Recent Posts

  • Self generating Simple SQL procedures – MySQL
  • Google Cloud Management – My Idea – My White Whale?
  • Position Tracker – The Stub – Pandas:
  • Position Tracker – The Stub
  • Position Tracker – In the beginning
  • Whats been going on in the world of the Dangerous DBA:
  • QCon London Day 1
  • Testing Amazon Redshift: Distribution keys and styles
  • Back to dangerous blogging
  • DB2 10.1 LUW Certification 611 notes 1 : Physical Design

Dangerous Topics

added functionality ADMIN_EST_INLINE_LENGTH Amazon Bootcamp colum convert data types db2 DB2 DB2 Administration DB2 Development db2advis db2licm Decompose XML Exam EXPORT Google IBM IBM DB2 LUW idug information centre infosphere LOAD merry christmas and a happy new year Recursive Query Recursive SQL Redshift Reorganisation Reorganise Reorganise Indexes Reorganise Tables Runstats sqlcode sql statement Stored Procedures SYSPROC.ADMIN_CMD Time UDF User Defined Functions V9.7 V10.1 Varchar XML XML PATH XMLTABLE

DangerousDBA Links

  • DB2 for WebSphere Commerce
  • My Personal Blog

Disclaimer:

The posts here represent my personal views and not those of my employer. Any technical advice or instructions are based on my own personal knowledge and experience, and should only be followed by an expert after a careful analysis. Please test any actions before performing them in a critical or nonrecoverable environment. Any actions taken based on my experiences should be done with extreme caution. I am not responsible for any adverse results. DB2 is a trademark of IBM. I am not an employee or representative of IBM.

Advertising

© Copyright 2021 - Dangerous DBA
Infinity Theme by DesignCoral / WordPress