We have been using online reorg extensively since DB2 UDB 8.2. Many clients that I consult with for initial performance problems or for initial migration to DB2 have no reorg strategy. In fact, many don’t know the difference between offline and online reorgs. Offline reorgs should be a thing of the past, keep that in mind although they can be used to fix space problems in the tablespace. However, most tablespace orphan space issues have been fixed in 9.7. I have most of my clients doing online reorgs during slower periods of activity, extensively. Including index reorgs with “allow write access” specified. Some clients have been able to stay up for over a year using this strategy. So if you haven’t been using online reorgs and you are losing your down window, then online reorgs are the way to go. Size the logs appropriately before you start, doubling the size is a good starting point. Of course, experiment with starting, stopping and pausing online reorgs along with how monitoring of them before you start. Use db2pd -d <dbname> -reorgs and db2pd -d <dbname> -reorgs index to monitor status of index reorgs. So if you have been wondering if online reorgs are a good thing, in almost all cases they are. Get moving…..
Are you running DB2 pureScale? If so, let me know. DB2 pureScale first announced in DB2 9.8 uses shared disk, a cluster facility, InfiniBand and RDMA. I originally predicted the use of this technology in my book, DB2 for Linux, Unix and Windows in 2003 and have seen it come to pass with 9.8. I predict that DB2 pureScale is the wave of the future for DB2 and it will eventually result in more migrations of DB2 for z/OS to DB2 pureScale on LUW. However, due to requirement for a new infrastructure (InfiinBand, RDMA NICs, IBM Hardware) the adoption has been quite slow. This will change but I’m not sure what the tipping point will be.
With DB2 LUW there are many built-in tools to use to monitor instances and databases. You need to pick what works best for you. I use db2pd, table functions, administrative routines and convenience views along with a few custom scripts. The essential metrics to monitor are:
CPU — (during normal and peak operation to include utility execution)
IO — Disk wait, queuing, response times, hot disks, log write rate
Top 10 SQL – I have been using a Top 10 SQL script for over 10 yrs and have provided it
during DB2 Tech Conference presentations to users around the world. DB2 9.7 and 10 now have a similar report available through the MONREPORT.CURRENTSQL and MONREPORT.PKGCACHE stored procedure. The nice thing with mine is you can customize how it ranks the SQL (rows read, System CPU, User CPU, number of executions, etc). Other areas to monitor are overall memory usage, and select DBM and DB CFG shared memory
areas. For OS monitoring, use VMSTAT, IOSTAT, TOP, TOPAS, MPSTAT, SAR, and on windows Task Manage or Perfmon. You can get CPU and memory usage from the sysibmadm.env_sys_resources convenience view or associated table function. Also, through db2pd. I have done hundreds of presentations on this topic over the years. In the next month I will be writing an article on the new monitoring in DB2 9.7 and DB2 10 and how to transition from snapshot monitoring to the new monitoring infrastructure. Stay tuned!
A useful db2diag command that I use on a regular basis to check daily backups is as follows:
db2diag -g message=:”Backup complete”
This will return the time the backup completed and whether or not it was successfull.
FYI. Received a call from DB2 Support informing me that they have included db2caem code with Fixpack 2 which has been released. I had opened a PMR quite some time ago informing support that it was missing from DB2 10.
OPTIM Performance Manager for DB2 has a great historical capability. It has a history database which enables DBAs to travel back in time to answer those tough questions like, “What happened 3 days ago at 3:02 AM in the morning”. You know the kind of questions your boss wants the answers to so he can provide the root cause to his boss….:). Give it a try, you will be glad you did. Of course if you don’t need history, you can use the DB2 provided built-in admin views and table functions along with the MONREPORT reporting module…..
I’m writing an article on how to implement temporal tables in DB2 10, stay on the lookout for it at http://enterprisesystemsmedia.com/
IDUG EMEA is winding down, have about a day left. The CPC did a great job and the presentations were excellent. IDUG is setting the stage for technical presentations and they were much better than those at the recent IBM IOD 2012 conference. I had a pretty good crowd in my session considering I was up against Matt Hura’s part II session. Stay tuned for more!
Gunning Technology Solutions, LLC. THE WORLD LEADER IN Db2 LUW CONSULTING! 