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

DB2 Table maintenance automated

February 27, 2011 9:39 am / Leave a Comment / dangerousDBA

I have done several previous posts on this subject but I would like to bring them together here, and show you the process that I run to maintain my tables when I want too, not when DB2 decides is best. So I use my four processes that I have built so far from the articles Automated DB2 Table Reorganisation, Automated DB2 Index Reorganisation, Automated DB2 Runstats and Automated DB2 Stored procedure Rebinding and write a wrapper stored procedure to encapsulate them all.

The wrapper stored procedure is fairly simple:

CREATE PROCEDURE DB_MAIN.AUTOMATED_MAINTAIN_GLOBAL_SCHEMA()
LANGUAGE SQL
BEGIN
    CALL DB_MAIN.AUTOMATED_REORG('S','GLOBAL');
    CALL DB_MAIN.AUTOMATED_REORG_INDEX('S','GLOBAL');
    CALL DB_MAIN.AUTOMATED_RUNSTATS_TABLE('S', 'GLOBAL');
    CALL DB_MAIN.REBIND_PROCEDURES ('GLOBAL');
END

This is the stored procedure that runs and keeps in line the GLOBAL schema that we have, obviously you can change GLOBAL to anything that you like. The stored procedure can then be used to automate anyway you like, the only gotcha is that the user that runs the script has to have the authority to carry out the commands. This means you can run it from a query window in the likes of Control Centre or IBM Data Studio, a bash script or a file that is run by the db2 command.

As for the schedule that we employ this is still under some testing, but due to the fact that you can never tell how long a offline table reorganisation will take and that once it has started there is no way to pause or stop it, unlike the online table reorganisations I have the 4 R’s (Reorg Table, Reorg Index, Runstats and Rebind) process running on each schema every other week. The tables that are identified for processing should only be the ones that have been identified by the inbuilt assessment stored procedures SYSPROC.REORGCHK_TB_STATS and SYSPROC.REORGCHK_IX_STATS or the tables that have been identified in table in the runstats part of the process, this should shorted the time taken to process, but I have found in testing the timings the reorganisations are greatly affected by the other processes that are going on in the instance / box at the time.

Posted in: DB2, DB2 Administration, DB2 Maintenance, IBM, Rebind Stored Procedure, Reorg Index, Reorg Table, Reorganise Index, Runstats, SYSPROC.REORGCHK_IX_STATS, SYSPROC.REORGCHK_TB_STATS

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 Bootcamp colum convert data types DB2 db2 DB2 Administration DB2 Development db2advis db2licm Decompose XML EXPORT GCP Google IBM IBM DB2 LUW idug information centre infosphere IOT LOAD merry christmas and a happy new year Position Tracking python Recursive Query Recursive SQL Reorganisation Reorganise Reorganise Indexes Reorganise Tables Runstats 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