Ruby Stepansky with IBM North America Lab services was kind enough to remind me of a special DB2 registry variable: DB2_USE_ALTERNATE_PAGE_CLEANING
This variable can be unset (implicitly off) or set to the values OFF or ON. When it is set to ON, DB2 uses a proactive method of page cleaning, writing changed pages to disk, keeping ahead of LSN_GAP, and proactively finding victims. Doing this allows the page cleaners to better utilize available disk I/O bandwidth. Setting this to ON thus cancels out, or negates, any value set for CHNGPGS_THRESH rendering it inconsequential.
Further due diligence on this registry variable shows that it was introduced in V8.1.4, but there was an apar defect which was corrected in V8.1.8 ( V8.1 FP8 ).
If you follow Chris Eaton's blog on ITtoolbox, Chris also blogged about DB2_USE_ALTERNATE_PAGE_CLEANING and provided a helpful query in this blog post.
The bottom line
If you are running DB2 V8 Fixpack 9a or higher, you might consider running this command: db2set DB2_USE_ALTERNATE_PAGE_CLEANING=ON
Verification
Use a database snapshot, or your dbsnap2.txt file, to verify the alternate page cleaning method is working well for you. Generally, you should see zeros or small numbers for the snapshot items shown below:
- LSN Gap cleaner triggers = 0
- Dirty page steal cleaner triggers = 0
- Dirty page threshold cleaner triggers = 0
If you see large numbers for any of these values, then DB2_USE_ALTERNATE_PAGE_CLEANING is probably not set, or set to OFF, and the CHNGPGS_THRESH value is probably too high (for example, the default value is 60%).
The Shameless Marketing Moment
|
DBI's Brother-Eagle™ for DB2 LUW is a real-time monitor for DB2 database performance that automatically computes several key metrics for you. Best of all, the Standard Edition is FREE. Enterprise Edition enables additional drill downs to connection and lock activity. Chris Eaton had nice things to say about Brother-Eagle in this blog post. |
Just for Fun - FREE STUFF
Bonus for IBM Database Magazine blog readers! Here is an Enterprise Edition trial key for Brother-Eagle for DB2 that is valid through mid-June 2008: KIzFCi-KF3szK-6lEgCm-be7A
If you are unable to attend IDUG North America this year and would still like to improve the performance of your databases, DBI is offering free DB2 LUW Tuning Webinars every month. The next Webinar is May 8th, 2008, at 9am CDT - "Extreme Makeover: DB2 LUW Physical Design Edition". View all DBI Events or Register Now.
If you can attend IDUG NA in Dallas, I'd love to see you in my Ed Seminar "DB2 LUW Performance Diagnosis Lab". Here is how IDUG AP attendees rated this Ed Seminar:
Cheers,
Scott
Scott Hayes
President & CEO, DBI
IBM GOLD Consultant
www.Database-Performance.info
www.Database-Auditing.info
Your Performance IS Our Business
Please Note: DBI has moved to a new web address!
