To reorganise or not to reorganise
By Philip Carrington – Holiday Extras – A Triton Consulting Client
www.holidayextras.co.uk
From my blog statistics I can see that a common question which leads people to my door is “db2 reorganisation required” or the equivalent. So here I am assuming that you have your Health Monitor service turned on and you have received a warning from either the Health Monitor Screen in the DB2 Control Centre, an email from the service or by interrogating the service from the command line or C API. This is because you have the db.tb_reorg_req health monitor parameter set and to a certain threshold value and against the table. In this article I want to go through the reorganisation issues that have caught me out, and the considerations that have to be made:
1. You can’t reorganise the whole of a range partitioned table without taking it offline
Depending on the version of DB2 you are using this varies, if you are using V9.5 or prior then you will have to reorganise the whole of the range partitioned table offline unlike V9.7 that has a table partitioning clause section to the command. This allows you to do an online reorganisation of the table a partition at a time after you have identified which ones need doing.
I use this method to reorganise the production tables in an automated way every weekend. I have written code that uses several of the in built DB2 stored procedures to identify which tables and table partitions need reorganising. The other thing to note is that I have found that you will also need to reorganise the indexes associated with the tables to stop the monitors reporting that reorganisation is required. These automated procedures are available from my blog with all the normal caveats of using someone else’s code assumed.
2. Online table reorganisation only reorganises the table.
Online table reorganisation does not reorganise indexes,. As stated above it might not just be the table that needs reorganising to get the monitors to stop reporting a “problem”. A classic offline reorganisation will process the indexes as well but this will leave your data unavailable while it carries out the task . Here again it differs for partitioned tables and non-partitioned tables on a partitioned table if you reorganise one index at a time then you can do them online, if you do all the indexes at once then it would have to be done offline. Non partitioned tables do not suffer from this restriction. Online table reorganisation is also carried out asynchronously (see point 3 below). In the production environment where I work I have written code that uses the in built stored procedures to DB2 to identify the individual indexes and run them as separate commands. I find this works well and it only carries out work where needed. Again these automated procedures are available from my blog with all the normal caveats of using someone else’s code assumed (again).
3. Be patient
A classic reorganisation will do everything but will take your table offline (e.g. ALLOW NO ACCESS) when it does it, the good news is that it will return when it has done everything. An online reorganisation (e.g. ALLOW READ ACCESS) will allow you access to the table but it is asynchronous so will return but will be working in the background, in the mean time you want be able to do anything else maintenance wise while this is going on (e.g. a RUNSTATS).
So to find out where the reorganisation has got up to you can run the following command: db2pd -d -reorg from the command line and it will show you what has been done and the progress of work currently being carried out. Another free tool that is very useful for seeing what’s going on in many parts of your DB2 server is with Technology Explorer for IBM DB2. In the administration view it will give you a log much like db2pd, but in a far “nicer way” if you like that kind of thing. Another way to see where the process is up to is to look in the .nfy file.
4. Set the automated maintenance policies up correctly, or go for more controlled “manual automation” approach
DB2 does come with the ability to do its own maintenance, but in my experience unless you set it up correctly when your data is loaded and when queries on that data occur then it can cause some issues. For example, I have seen the automated policy decide that one of the largest tables in the database needed reorganising right in the middle of the batch loading in the morning! Don’t get me wrong, I think it is a good feature, but unless you set it up right with policy files it could potentially cause issues. Equally, where automatic runstats policy is “obsessed” with collecting stats on one large XML table all the time. To get around this I havedisabled automated runstats opting to collect stats manually instead.
I prefer to have a “manual automated” approach, identifying what needs doing by using the built in stored procedures and tables or use a rule of thumb approach as explained in a DB2 night show. Personally I like to use built in stored procedures (SYSPROC.REORGCHK_TB_STATS, SYSPROC.REORGCHK_IX_STATS) that are supplied by DB2 to identify the tables that need reorganising. You can then use the SYSIBM.SYSTABLES:STATS_TIME column to identify the tables that need runstat’ing or just runstat all the tables you have just reorganised. To round it all off you use the SYSCAT.ROUTINES and SYSPROC.REBIND_ROUTINE_PACKAGE to rebind all the stored procedures affected to get new up to date execution packages. It is possible to do this all in stored procedures, and then automate these stored procedures to run when you want too, and it is possible to build an element of time frame based working into them to fit a window.
I hope these four gotchas help and that it has given you some insight into a solution or at least the causes of your issues that you have with DB2 reorgs.