Complete Online REORG Suite
---------------------------

1. Introduction
This is a complete suite of generic Korn Shell and dynamic SQL source code. 
It will run online REORGS on any DB2 UDB LUW V9.7 database - visit the website for earlier V8, V9.1 and V9.5 versions.
It requires little customisation, but is aimed at a DBA audience on these platforms, 
so it does assume you understand what what you're doing!
The controlling program is written in Korn Shell (ksh93), so is ready to run on most Unix/Linux installations. 
The V9.7 version also makes extensive use of a ksh function library.
I hope this suite will save some reinventing of the wheel, educate and give new ideas. 


The suite was first developed at V8 for a predominantly Business Intelligence/Data Warehousing environment; 
suggestions for OLTP and 24*7 environments are included in the Customisation section.

2. Suite components

Reorg_Online.ksh ............ Korn Shell executable; takes instance name and dbalias as mandatory parameters. 
                              Runs all the remaining components and dynamically constructs and executes interleaved REORG/RUNSTATS statements;
reorg.schema_list.dbname .... ascii text list of all schemas in database sample containing tables you wish to REORG;
reorgchk_drop.ddl ........... Drops any tables left over from the previous run (table definitions change between DB2 releases).
progress.sql ................ Use this to monitor suite progress; run ad hoc from the command line or a daemon of your own making.

The next 4 components are in the TEMPLATES directory, suffixed .template;
Copy them to the REORGS directory, dropping the .template suffix, and edit according to the customisation instructions at section 3. below

reorgchk_tb.ddl ............. table definition to hold output from a call to sysproc.reorgchk_tb_stats; 
reorgchk_ix.ddl ............. table definition to hold output from a call to sysproc.reorgchk_ix_stats;
reorg.pick_table.sql ........ logic to select the most appropriate clustering index for a table level REORG;
reorg.pick_index.sql ........ logic to select the most appropriate type of REORG for each index. 
 
3. Customisation for your site.

The suite assumes catalog statistics are current at the start of the run.
- if so, remove the REORGCHK ... UPDATE STATISTICS in Reorg_Online.ksh;
- rename reorg.schema_list.dbname, replacing dbname with your own database name *in lower case*; create one file for each database in scope under the instance.
- edit reorg.schema_list.<dbname> to include all schemata with tables and indexes you would like REORG'd
  (for example you may wish to omit schemata with low churn tables, intermediate work tables and the system catalog);  
- edit the tablespace name in the 2 .ddl components from USERSPACE1 to your preferred single partition tablespace;
  on a DPF system, this will normally be on a non-catalog partition.
- edit the user temporary tablespace in the 2 .sql queries if you prefer not to use systoolstmpspace
- modify reorg.pick_table.sql to use INLINE reorgs if required (not recommended, but possibly unavoidable in 24*7 environments);
- Reorg_Online.ksh uses ALLOW WRITE ACCESS, unless the table is a MDC, range-partitioned or contains XML;
  ALLOW WRITE ACCESS is better suited for OLTP, write-intensive or high availability environments; 
  change to ALLOW READ ACCESS, if your environment is predominantly read-only.
- Add KEEPDICTIONARY, RESETDICTIONARY and USE TABLESPACE clauses if you like,to the code in reorg.pick_table.sql.
- optional: Data Warehouses usually contain history tables with a time dimension, whereby the oldest data is removed 
  and data for the next time frame is added on a regular cycle. Most queries against such history tables will reference
  the time dimension. It therefore seems a natural index key on which to cluster data in a REORG, since the table
  effectively becomes self-clustering. To take advantage of this, modify reorg.pick_table.sql to include 
  the names of the time dimension columns;  a commented example and further explanation is in place in that script.
- place the complete set of components under $INSTHOME/MAINTENANCE/REORGS, for integration with the rest of the maintenance suite. 

It would be quite simple to write a graphical instal wrapper to customise this suite; we'll be pleased to host it here.

4. Legal Stuff
This source code is freely available 'copyleft', that is, subject to the following terms:
- you may copy, modify, use and redistribute the code but you may not charge for it.
- you must keep the program descriptions, name and website of the author without alteration. 
- you must comment all modifications in an amendment history; this is to protect the author's reputation.
[ and I do have a reputation to live down to :-) ]
  
I take a pride in my work, test it exhaustively and would be horrified if it failed to please, but cannot be held 
responsible for modifications you make. 
I strongly recommend you read the source before running it, it is extensively commented for the reader's benefit. 
This software is provided 'as is', potential bugs, warts and all, and without any guarantee or warranty. 
It is current for the present DB2 release level but may become out of date with subsequent releases. 
It's up to you to check its appropriateness for your site and tailor it as you see fit.


Alex Levy, Sustainable Software Ltd. http;//www.sustainablesoftware.net
January 2008, revised May 2012.
