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

DB2 Move Table Partitions Automatically

June 4, 2011 2:08 pm / Leave a Comment / dangerousDBA

One of the hot topics in databases at the moment is temperature based data. This is basically where you put the data that is most often access on fast disks, and data that is accessed least often is put on slower disks. To accomplish this is pretty easy as you can set the containers for an “archive” tablespace to a different disk / directory mount point. This does similar things to my two previous posts on attaching table partitions and detaching table partitions

The problem comes when you already have data in a table and the table partition already is part of a tablespace, you cant just alter the partition to the new tablespace. You have to identify the table partition to move, detach it recreate it in the new tablespace and then reload the data. You can do this manually, but who has time to do this, I have written code that will allow you to move the tablespace of the partition quite easily, and can be part of an automated process. The stored procedure call looks a little like this:

CALL DB_MAIN.MOVE_PARTITION('INSURANCE', 'TRANSACTIONS', 10, '/home/db2inst1/detach-archive/', 'INSURANCE_ARCH_TS')

The stored procedure that I have created deals with all of this and creates “safe” copies of the data, and records what it has done and where it currently is, although there is no functionality to stop and start the process. The stored procedure and the table it uses can be found in the file at the bottom of the page, I will go through briefly below what it does.

Given the parameters passed in then the procedure works out if there are any viable partitions to move. Once it has done that it will detach the partition in question and will store it away in the location given in the parameters. As the stored procedure moves faster than DB2 can detach the data you need to use SYSIBMADM.SNAPUTIL a view that will show the progress of the utility, the stored procedures can’t continue till this is done. Once the data is detached and reorg of the indexes will get rid of the last vestiges of the partition and you can run the command to attach the new one in the archive tablespace. Then it reloads the data that was extracted into the new partition in the new tablespace.

When this finishes then you are left with a row in the DB_MAIN.MOVE_PARTITION (assuming you choose to keep it in that schema) and an IXF of the extracted data, that you can write of somewhere as part of a backup strategy.

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 moving table data 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-move-data-partitions-sps-dcp

Posted in: DB2, DB2 Administration, DB2 built in tables, DB2 built in Views, DB2 Built-in Stored Procedures, DB2 Maintenance, IBM, Move table partitions, SYSIBM.SYSDATAPARTITIONS, SYSIBMADM.SNAPUTIL, SYSIBMADM.SNAPUTIL, SYSPROC.ADMIN_CMD

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