Category Archives: Rebind Stored Procedure

Automated DB2 Reorganisation, Runstats and Rebinds – Version 2

A while back I did the first version of this code (can be found here). Over time I have been running this code on our production servers, it started out by working fine but sometimes it would over run and interfere with the morning batch, so a different solution was needed. In a previous article I discussed if it was better to let the included automated DB2 functionality take care of the maintenance of tables etc, or to create your own process that uses included stored procedures to identify the tables that need reorganising.

So this new version of the script will only work between certain times and only do offline reorganisations, but is still possible to just reorganise a single partition of a range partitioned table. The reason for the time restriction is to take a leaf from the included automated scripts having an offline maintenance window, and to stop the scripts that I have created before overrunning into the morning batch. The previous version of the reorganisation script attempted to be to “clever” and do an online reorg of non partitioned tables and an offline reorg of the partitions of the range partitioned tables. The problem with this is that capturing when the online reorgs have finished (as they are asynchronous), so that the table can have it statistics run so that it is not identified again by the SYSPROC.REORGCHK_TB_STATS stored procedure. Equally another issue is that you would have to reorganise the index’s on the tables that you have on-line reorganised as they would not have been done, where as an offline reorganisation also does the indexes at the same time.

So I made the decision to do all the reorganisations offline, followed by a runstats and a rebind. The main controlling stored procedure looks like:

 --This procedure is the wrapper for all the rest to tidy it up a little bit.
 --It will only run the reorgs tille the time specified, then will just finish the one
 --that it is on once the time has expired.
 --Similar thing for the runstats so that it does not impact on the running of the
 --morning loads.
 --Rebind the procedures so that they get new packages based on the updated statistics
 --from the reorg and runstats.
 --All Reorg done off line as this is what DB2 does.
 --MAINT_SCHEMA = The schema you wish to be looked at
 --REORG_FINISH_TIME = The time you wish the reorgs to run until
 --RUNSTATS_FINISH_TIME = The time you wish runstats to run till
 --DAY_TO_REMOVE = The number of day back you wish staging tables to be emptied from

 --Reorg the tables
 --Runstat the tables that have been reorged
 --Rebind the stored procedures to take advantage of the potentially new plans


This is now a three stage operation, the first two stages have time limits and so they will carry out new operations until this time limit is breached. What you have to realise here is that if the end time is 18:00:00 then it will start work right up until 17:59:59, this means if it picks up a particularly large reorganisation task at this last second then it will run till it has finished.

Some of the code especially the runstats stuff is quite a lot like the previous version just with a change for the time. As I cant upload a single .zip file as apparently it will be a security risk, and apparently a .sql file is also a risk please find a number of .doc files a the bottom of the article. Please just change the file extension and then you will be able to access them. I would very interested in having feedback from anyone who uses this code to see how you get on with it.

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 reorganising tables can potentially dangerous.










DB2 Automated Maintenance Vs Automated Maintenance Scripts

I am the first to admit when I am wrong and accept the consequences but IBM some times do not make it easy to work out what you are meant to be doing and answers of  ”it depends” are not entirely helpful, equally I understand that I know what I know about DB2 and I am more than willing to learn. Those of you that have come to my blog before have probably seen the articles that I have done before on stored procedures that you could automate to Reorganise tables and indexes, Runstats on the reorganised tables and then finally rebind the stored procedures. These recently have started to overrun and affect production systems that they were not meant too, so a new way of working needs to be found, therefore we are back to the automated maintenance found in the DB2 ESE product itself or editing the scripts, but from my research automated maintenance does not really do things in the right “order”.

The automated maintenance provided with DB2 from what I understand allows two time periods online window and an offline window, and in essence three different types of work method “do something” or “tell someone who’s bothered” or “do something and tell someone who’s bothered”. In the online window you can carry out runstats and activities that can be carried out on tables without talking them offline. In the offline window DB2 will carry out regorgs of tables in an offline classic reorg. At no point will it will it rebind the stored procedures and in no way are these joined up e.g. if a table is reorganised it will then have the runstats done on it unless DB2 formulas behind the scenes decide too. Where as the scripts and stored procedures I created will do everything in order, but is it needed.

I was listening to the excellent free webinar (live) from DBI on “DB2 LUW Vital Statistics – What you need to know” (replay download at the bottom) and listening to guest John Hornibrook explain how and what you can set in DB2 to gather statistics was elightening and I learned so all good. Having been researching the automated maintenance I thought of a question “Do you need to runstats after a table / index reorg?”, the host thought that he knew the answer, but I think John threw him a little bit of a curve ball by responding with (something like) “well the data has not changed but the locations and distribution on disk have changed” (would have liked to get the exact quote but no sound on replay I downloaded!), well I was even more confused. I would have loved to have submitted a follow up question but they drew it to a close in short order after that. My next question would have been “Will a table be marked for runstats after it has been reorganised?”.

So on the theme of that question I thought IBM developer works might know and if did have some very useful information on it Automatic table maintenance in DB2, Part 1 and Automatic table maintenance in DB2, Part 2. These articles are very good and explain how automatic table maintenance works, but equally left me with questions. A line in the Part 2:

“If you reorganize the table and do not update the table statistics by issuing a RUNSTATS command, the statistics will still indicate that the table contains a high percentage of overflow rows, and REORGCHK will continue to recommend that the table be reorganized”

But in Part 1 on runstats there is a list of decisions DB2 will make as to wether it needs to runstats:

  1. Check if the table has been accessed by the current workload.
  2. Check if table has statistics. If statistics hare never been collected for this table, issue RUNSTATS on the table. No further checks performed.
  3. Check whether UDI counter is greater than 10% of the rows. If not, no action on the table.
  4. Check whether UDI counter is greater than 50% of the rows, issue RUNSTATS on the table if UDI counter is greater than 50% of the rows.
  5. Check if the table is due for evaluation. No further action performed if the table is not due for evaluation. An internal table is used to track if tables are due for evaluation.
  6. RUNSTATS if the table is small.
  7. if table is large (more than 4000 pages), sample the table to decide whether or not to perform RUNSTATS.
So this seems that a table might not get runstat’ed if it did not fall into these criteria and then it would keep being targeted for reorganisation. Another thing that intrigued me was that:

“All scheduled reorganizations (and other automatic maintenance operations, like automatic runstats) are maintained in a queue. When the corresponding maintenance window begins, reorganizations are performed one after another until the end of the window”

So if your tables are large or your window when your tables can not be accessed is short then not a lot of work will be done. It is not multi threaded like the stored procedures that I wrote, but it does have one advantage that the reorganisation phase is to a window, something that is not built into my scripts. Equally the stored procedures have their disadvantages as the reorganisation is IO heavy and the runstats is CPU heavy, so if you have multiples of these things going off all could be at different stages and become quite a load on the server.

I think that the solution is that automatic maintenance is useful just to keep your runstats ticking over during the week because as explained by John this automation is very “light” and also can be set to evaluate before a query is run, but for reorganisation I think I am going to write a new version of the scripts and stored procedures that I blogged about before and build in time windows that work will be carried out under because it is a more joined up way of doing things and also will include the rebind which is essential for DB2 knowing the best execution plan for stored procedures.

I would love to know your experience with automatic maintenance or other methods of keeping your reorganisations and runstats up to date so please feel free to comment on this posting.

DB2 Table maintenance automated – Two weeks on

I am back from holiday and I have been reviewing the code that I have put in place for automating the table maintenance and have come up with the first adjustment. As I said before the DB2 system that I manage is a little bit more open than most of you probably have to deal with and so sometimes the stored procedures become invalid for no real “reason”.  So the first adjustment is to only rebind those that are still valid with a “AND VALID = ‘Y’” so it is now:

  FOR thisProc
       AND   ROUTINETYPE = 'P' 
       AND VALID = 'Y'

So this improves on the code from DB2 Rebind Stored Procedures Automation. I did not expect this to be an issue in the first version of the code. Equally I have created myself a little script based around this SQL to find me all the invalid stored procedures and notify me.