I’ve had such good success with DB2 9.7 LUW under 64-bit Linux that I didn’t hesitate to install it on my new development laptop (Windows XP 32-bit, the corp-rat standard). I immediately upgraded to Fix Pack 2, of course: never install fix level zero of anything. I created and loaded a few databases and was on my way.
But it didn’t take long to notice some very bad behaviors. I saw frequent CPU, memory, and I/O spikes, mostly in db2syscs and db2dasstm. On two occasions, all database operations froze without recovering, and db2syscs suddenly became a diehard: I couldn’t kill it from the Service Control Panel, Task Manager, or even pskill. Windows shutdown then froze, requiring a hard power off.
This sent me on a fact-finding mission. First stops: the Windows Event Viewer and db2diag.log. Since I had not changed diagpath, I had to remind myself that instance information files are no longer under SQLLIB (with Windows, they’re now under Documents and Settings\All Users\Application Data\IBM\DB2). I spotted a huge and growing db2diag.log, at diaglevel 3. It was flooded with messages like:
The db2diag flooding would certainly account for the CPU, I/O, and memory spikes, but I’m not sure about the periodic freezes. But it’s one thing at a time, and couldn’t stop until I had a clean db2diag.
Fortunately, I found a fellow victim who reported the same issue just yesterday. The root cause was that the DB2 Admin Server (db2dasstm) did not have required authorities on toolsdb. This was surprising, since I let db2setup create and configure it. But I’ve been accustomed to chanting the grant dbadm mantra since the introduction of SECADM, so I typed it out.
But in this case, it wouldn’t work. I couldn’t get into toolsdb with an authorization ID that had SECADM in order to do the grant. I tried stepping into it first via SYSADM/SYSADM_GROUP, but no dice. And toolsdb was unique in that SECADM was only granted to some bogus “SYSTEM” ID. Thank you, db2setup, for painting me into a corner!
To fix it, I had to drop and re-create the toolsdb, following the proper steps to keep DAS happy. A couple of db2admin stop/starts later and I had a small and steady diag log.
Time will tell if additional problems remain that contributed to the freeze problem (so far, so good), but I learned an important lesson: never let the DB2 installation program create the toolsdb.