DB2 Health Monitor v9 fixpack 4 still can be a BIG PROBLEM

jbenner

I have opened a PMR for this with IBM, and am not asking for advice from the DB2 DBA community. I am posting this as an FYI that DB2 Health Monitor, even at the latest version of DB2, still can cause huge problems with slow connect times and heavy resource locking in high concurrency / high transaction volume environments. I have an OLTP with 30-90 transactions per second activity, and start of Health Monitor every 2 hours was crashing our application. We only resolved the situation by setting DBM CFG parameter HEALTH_MON = OFF.

Note that other thescripts reports show problems with db2hmon going back to version 8.2.

My advice for high transaction volume databases is to not even think about leaving Health Monitor active. (I also see other autonomous features such as STMM giving undesirable effects, so we have deactivated self tuning memory manager as well.).

Here is more detail, from the doc I sent IBM on 2008.03.14:

======

Summary

We are a DB2 version 9 Fixpack 4 user, 64-bit, running on a Solaris 10 zone on a T2000. The production application is very high transaction volume, 20-90 transactions per second.

We have experienced three separate errors with DB2 Health Monitor that have forced us to deactivate it entirely. To summarize they are:

1. In high transaction environment, every time DB2 Health Monitor runs (every 2 hours) causes heavy new connection slowdown and heavy table locking.
2. In one server, DB2 Health Monitor crashes every time it runs with a sqlerRemoveAllIPCforRow violation. When it crashes it causes heavy connection slowdowns and heavy table locking which result in errors for the heavy concurrency user application.
3. DB2 Health Monitor CALL REORGCHK_IX_STATS fails with a SQLCODE “-2306” even though the table exists.

Detail

Problem #1 Health Monitor causes heavy locking and new connections slowdown

We originally opened this PMR to request IBM’s help in determining why our high transaction volume application was experiencing strong connection slowdowns every 2 hours. IBM's engineer said that it did not seem to be a DB2 process that was causing the problem. I did decide to fix MAXLOCKS and that made a marginal improvement, but the problem continued.

Yesterday, realizing that DB2HMON runs every 2 hours, we deactivated DB2HMON ( UPDATE DBM CFG USING HEALTH_MON OFF ) and the connection slowdown/locking related application failures immediately ended. It is obvious that the problem was entirely caused by Health Monitor.

Problem #2 Health Monitor crashes with IPC Resource Error

On the staging server (NOT the production server), every time DB2HMON attempts to run, it crashes. As a result we have decided to completely prevent DB2HMON from starting, using IBM's advice ( http://www-1.ibm.com/support/docview...id=swg21259046 ) , by setting the DB2_FMP_COMM_HEAPSZ to 0. Despite the fact that we are not using Health Monitor, we would like IBM to fix this crash behavior.

Here is an example from today’s db2diag.log on the staging database:


Problem #3 Health Monitor fails incorrectly with SQLCODE “-2306”


When I was running an event monitor to diagnose “Problem #1” above-referenced in this document, I noticed that Health Monitor was running CALL REORGCHK_IX_STATS with a valid table name, for example:

CALL REORGCHK_IX_STATS ('T', '"NEAT "."LOCATION_SETS_LOG"')

And returning SQLCODE -2306, “The table or index name does not exist” even though the table NEAT.LOCATION_SETS_LOG does exist.

Here is event monitor output illustrating the problem:

The matching db2diag.log output for this event was:

Mar 14 '08 #1

docdiesel

Hi jbenner,

thank you very much for sharing your experiences with the health monitor. I'm sure this may help some people out there. I for myself am still looking forward to migrate to v9.1. ;-) By the way, I took the liberty to add some code tags (to be set with the # button in the editor) to the logs for better readability.

Regards,

Bernd

Mar 14 '08 #2

jbenner

You are welcome! Thanks for helping me with the post readability.

Mar 25 '08 #3

dbpete

Thanks for this information on Health Monitor!

We are currently migrating from DB2 v8 on Solaris 8 to DB2 v9.1 on Solaris 10 and at the same time exploit the virtualization features of Solaris 10 (Zone concept). We first wanted to directly use DB2 9.5 but we ran into several problems. Thus we decided to go for v9.1, Fixpack 4. Still quite a step!

What makes things a bit more tricky is that for development and education platforms, we intend to use Sun T5120 servers. For these ones, we ran into nasty problems including intermittent instance crashes. It took us much time, firmware updates and fixes from Sund and IBM until things started running smoother. Please be aware of such problems if you intend to use this new Sun hardware for DB2.

We encounter the same problems with health monitor to what concerns the db2HmonEvalReorg error on certain tables. We have no clue yet why health monitor can evaluate some of the tables and others not.

We haven't used health monitor up to now, but intend to do so for the new version. Therefore it's good to know that it may cause such heavy performance problems - we can keep an eye on this.

And of course I will appreciate any hint on which health indicators are critical in terms of resource consumption.

Thanks,
pete

Apr 1 '08 #4

SuperKuper

While we are on the subject ... When in DB2 V9 a database is explicitly activated, two connections show up by default. One is STMM and the other I do not remember what. Do you know how to turn off both. You already mentioned that you turned off STMM. How did you do that? Thx

Apr 5 '08 #5

Shashank1984

Superkuper,

Regarding the new db2 agents...i.e.db2stmm and db2taskd..
New DB2 agents have been introduced in DB2 Version 9.1: db2stmm and db2taskd. The db2stmm agent is part of the new self-tuning memory feature added in DB2 Version 9.1. The db2taskd agent is an internal daemon that distributes background database tasks. These new agents remain connected to the database at all times and are not active if the database is activated in exclusive mode.
Starting with Fix Pack 3, agents for active event monitors are also connected to new databases by default.

Regarding disabling of STMM...
Self tuning can be disabled for the entire database by setting self_tuning_mem to OFF. When self_tuning_mem is set to OFF, the memory configuration parameters and buffer pools that are set to AUTOMATIC remain AUTOMATIC and the memory areas remain at their current size.

You can set self_tuning_mem to OFF using the UPDATE DATABASE CONFIGURATION command, the SQLFUPD API, or through the Change Database Configuration Parameter window in the Control Center.

Self tuning can also be effectively deactivated for the entire database if only a single memory consumer is enabled for self tuning. This is because memory cannot be redistributed when only one memory area is enabled.

Thanks,
Shashank Kharche
IBM.
DB2 for Linux, Unix & Windows -Information Management Software

Apr 15 '08 #6