Introduction
DB2 Universal Database (UDB) Enterprise Server Edition (ESE) is a multiuser version of DB2 that allows you to create and manage single-partitioned or partitioned database environments. Partitioned database systems can manage high volumes of data and provide benefits such as increased performance and high availability.
Before attempting to understand how to monitor a DB2 UDB server, it is essential to get acquainted with its architecture.
On the client side, either local or remote applications, or both, are linked with the DB2 Universal Database™ client library. Local clients communicate using shared memory and semaphores; remote clients use a protocol such as Named Pipes (NPIPE), TCP/IP, NetBIOS, or SNA.
On the server side, activity is controlled by engine dispatchable units (EDUs). EDUs are implemented as threads in a single process on Windows-based platforms and as processes on UNIX. DB2 agents are the most common type of EDUs. These agents perform most of the SQL processing on behalf of applications. Prefetchers and page cleaners are other common EDUs.
- Prefetchers retrieve data from disk and move it into the buffer pool before applications need the data. For example, applications needing to scan through large volumes of data would have to wait for data to be moved from disk into the buffer pool if there were no data prefetchers. Agents of the application send asynchronous read-ahead requests to a common prefetch queue. As prefetchers become available, they implement those requests by using big-block or scatter-read input operations to bring the requested pages from disk to the buffer pool. If you have multiple disks for storage of the database data, the data can be striped across the disks. Striping data lets the prefetchers use multiple disks at the same time to retrieve data.
- Page cleaners move data from the buffer pool back out to disk. Page cleaners are background EDUs that are independent of the application agents. They look for pages from the buffer pool that are no longer needed and write the pages to disk. Page cleaners ensure that there is room in the buffer pool for the pages being retrieved by the prefetchers.
- A set of subagents might be assigned to process the client application requests. Multiple subagents can be assigned if the machine where the server resides has multiple processors or is part of a partitioned database. For example, in a symmetric multiprocessing (SMP) environment, multiple SMP subagents can exploit the many processors.
All agents and subagents are managed using a pooling algorithm that minimizes the creation and destruction of EDUs.
Buffer pools are areas of database server memory where database pages of user table data, index data, and catalog data are temporarily moved and can be modified. Buffer pools are a key determinant of database performance because data can be accessed much faster from memory than from disk. If more of the data needed by applications is present in a buffer pool, less time is required to access the data than to find it on disk.
The configuration of the buffer pools, as well as prefetcher and page cleaner EDUs, controls how quickly data can be accessed and how readily available it is to applications. Without the independent prefetchers and the page cleaner EDUs, the application agents would have to do all of the reading and writing of data between the buffer pool and disk storage.
All these integral components need to function like well-oiled machines in order to enable the DB2 server to quickly and efficiently process data requests. The slightest of problems in the configuration or operation of these components, if not swiftly resolved, can severely degrade database performance, and can even render the database server inaccessible to users. In order to prevent such adversities, it is imperative that the DB2 server is monitored round-the-clock, and problems brought to the attention of the administrators before irreparable damage is caused.
Moreover, a Database Partitioning Feature (DPF) is additionally available in the DB2 UDB Enterprise Server Edition (ESE). With DPF your database is scalable as you can add new machines and spread your database across them. This means more CPUs, more memory and more disks from each of the additional machines for your database.
If DB2 is installed in a DPF environment, the management challenges grow! Instead of monitoring a database on a single machine, each database now has to be monitored across all its partitons, which may be spread across multiple machines, to ascertain the health of the transactions to the database.
To address the unique monitoring requirements of the different DB2 installations, the eG Enterprise prescribes three exclusive monitoring models for the IBM DB2 server – one for version 8.0 (and above) of the DB2 server, one for versions 6.0 and 7.0, and one for the DB2 server that is installed in a DPF environment. These models thoroughly scrutinize every layer of a DB2 server for issues, and proactively alert administrators of probable problem conditions.
