If you are not using this handy tool, get moving! The tcbstats option displays u/d/I activity for tables since last DB2 start. Also, with the index option you can see index activity such as ixscans, page splits, # of times an index used for ixonly access and page reorgs. You can use this info to identify your most active tables and indexes and then target them for improvement along with the SQL that accesses them. That’s the view from here!
IBM introduced SUPERASYNC mode in DB2 9.7 along with the ability to read on the STANDBY. Both of these settings provide clients with additional flexibility in implementing HADR. By setting the HADR_SYNCMODE to SUPERASYNC, the STANDBY remains in REMOTE CATCHUP and won’t cause back-pressure on the PRIMARY. The read only on the STANDBY (ROS) is enabled by setting the DB2_HADR_ROS registry variable to ON. ROS can be used in all HADR synchronization modes. That’s the view from here…..
I have had some recent engagements where someone set the DIAGLEVEL to 1 instead of the default 3. If not set to 3 you will miss many important entries that would have otherwise been written to the db2diag.log and you will not have the info you need to conduct problem determination efforts. You should keep it at 3 unless you have a real good reason to change it. Also, only ever change it to 4 for a short duration and when working with support. That’s the view from here. Stay tuned.
I know from time to time most applications experience lock timeout problems especially for new applications or when porting applications from other database platforms to DB2. By default, DB2 only reports on deadlocks and lock escalations. If you are running DB2 9.7, there is new capability to help you in resolving lock timeouts. You can enable this new support by setting the mon_lck_msg_lvl DB CFG parameter to 3. 1 is the default setting. This setting can be changed dynamically online, “db2 update db cfg for dbname using mon_lck_msg_lvl”. Once set to 3, additional lock timeout information will be captured and written to the admin notification log. Stay tuned!
A client of mine recently made some table changes along with some SQL changes for a frequently executed SQL statement. Made bufferpool changes to account for the larger page size and am seeing fairly low physical reads. However, right away I noticed from the disk busy graphs that the drives were busy at a steady 10% rate. This is unusual for this applications as most data is found in the bufferpool. I did some snapshots to try and find the SQL causing the disk IO but nothing jumped out at me. Ran Top SQL script and found a statement then I ran the “db2 call MONREPORT.CURRENTSQL” stored procedure and found the same SQL executing as I had from my Top SQL script. I drilled down and explained it and it had a cost of 688,000 timerons….Now looking at a rewrite recommendation. The point I want to make is that although I have had a Top SQL ranking script for over 13 years, I find the MONREPORT module to be pretty handy and you should make sure you use it along with CURRENTAPPS and PKGCACHE modules as part of you SQL tuning toolkit. It can also help you in identifying lock waits and other wait time events, such as log IO and bufferpool IO wait.
Check us out at IDUG NA 2013 at Orlando, FL 29 Apr – 3 May at our booth at the Caribe Royale. Phil will be doing presentations on Battle Proven SQL Tuning Techniques and DB2 Monitoring Essentials. IDUG is a great forum for users and consultants. Attend and get the latest from the world leader in DB2 for LUW!
We will be sponsoring this meeting http://www.idug.org/p/cm/ld/fid=185&ic11=12&commid=126 and I will be doing two presentations as follows:
1st – Time Travel with DB2 Temporal Tables
Learn what types of temporal tables are available for DB2 LUW and when to use what type. See examples of how to define and use system period, application period and bi-temporal tables. Insert, update and delete rules will be discussed. New special registers and keywords will be presented.
2nd – DB2 LUW Monitoring Essentials
Learn how to transition from snapshot based monitoring to the new monitoring infrastructure introduced in DB2 9.7 and DB2 10. Database Manager Configuration settings along with best practice settings will be discussed. Learn how to use the new table functions based on the new infrastructure. Use cases for the use of the MONREPORT reporting module will be presented. Understand the importance of interval based monitoring and identifying and tuning the Top 10 SQL using the new procedures.
Well, it is Monday morning, and you have probably finished your first or second cup of coffee and are logging on to your production databases to check the db2diag.log for any errors, to ensure backups completed successfully, etc. Well, instead of doing that, what you could do is use a script to tail or “watch” the db2diag.log say every 5 minutes and have it send email alerts to a specific or group DBA email alerting you of certain informational and or serious error messages. This can be a customized script or a script integrated to use the db2diag command. A script such as this is a must for any production environment and it will help you sleep better at night :). So get moving!
On a daily basis, I use a few regular checks to ensure the health of client databases as follows:
1. Top 10 SQL script
2. Review of all key database manager and database shared memory areas such as post threshold sorts, sort overflows, hash join loops, small and large overflows, package cache overflows, any type of log issues, errors in the db2diag.log, review of table space, space limits, check of file system capacity, disk space used and a review of CPU and IO used.
3. Properly scripted, this whole process can be done in 5 to 15 minutes.
Over the years, full offline backups have been replaced with a combination of delta and incremental backups. Or, even use of disk snapshot technology for larger databases. So, if you are still taking down time for regular offline backups, think again. I have most of my clients set up for a combination of deltas, incrementals and possibly 1 full weekly backup. And the larger ones use write suspend and disk subsystem snapshot technology. Of course, the number one priority of a DBA is to make sure the data is always recoverable….That’s the view from here…..
Gunning Technology Solutions, LLC. THE WORLD LEADER IN Db2 LUW CONSULTING! 