DB2, and relational databases in general, have a reputation of being (relatively) easy for users to understand; users specify what data to retrieve, not how to retrieve it. The layer of complexity removed for the users, however, had to be relegated elsewhere: to the code of DB2. And that means you sometimes have to dig into technical details of the DB2 optimizer or other arcane details to uncover performance issues.
DB2 also has a reputation as a large resource consumer. This reputation is largely because of DB2’s complexity. Because DB2 performance analysts must understand and monitor this complexity, they require an array of performance monitoring tools and techniques.
But I do not want to get into all of the potential tools and techniques in today’s short post. I plan to talk about the various types of DB2 performance and monitoring solutions that are available in upcoming posts.
Instead, today’s post just covers the high-level components of what is needed for an effective DB2 performance management strategy... An effective monitoring strategy includes the following:
- Scheduled batch performance reports on the recent performance of DB2 applications and the DB2 subsystem; a history of these reports would be useful, too.
- An online monitor that executes when DB2 executes to enable quick monitoring of performance problems as they occur.
- Online monitors for all teleprocessing environments in which DB2 transactions execute (for example, CICS, IMS/TM, or TSO).
- A monitoring solution that can track and report on dynamic distributed traffic.
- End-to-end transaction monitoring capability, sometimes called Application Performance Management.
- SQL query monitoring and explain analysis.
- Regular monitoring of z/OS for memory use and VTAM for network use.
- Scheduled reports from the DB2 Catalog and queries run against the RTS tables
- Access to the DB2 DSNMSTR address space to review console messages.
- Use of the DB2 -DISPLAY command to view databases, threads, and utility execution.