Buffer pools, locking, data sharing and troubleshooting
DB2 performance depends on how well data is cached in buffer pools, how much programs block each other with locks, and, in a data sharing group, how members coordinate through the coupling facility. Accounting and statistics traces tell you where the time is really going.
Buffer pools
A buffer pool is an area of memory where DB2 caches table and index pages. A getpage is a request for a page; if it is not in the pool, DB2 must read it from disk, a synchronous read that the program waits for. Prefetch reads pages ahead in the background for sequential and list access.
- Pools come in 4K, 8K, 16K and 32K page sizes (BP0 to BP49, BP8K0, BP16K0, BP32K and so on), and each table space or index is assigned to one.
- Separating objects into pools lets DBAs give hot, randomly read indexes plenty of memory while keeping large sequential scans from pushing them out.
-DISPLAY BUFFERPOOL(BP2) DETAILshows getpages, synchronous reads and prefetch activity.-ALTER BUFFERPOOLchanges size and thresholds, under change control.- Larger pools reduce I/O but use real memory, which must be available on the LPAR. Page-fixing pools (PGFIX) is common but depends on memory planning.
Locking and concurrency
DB2 uses the IRLM lock manager to stop programs from changing data others are using. Locks are taken at the size set by the table space's LOCKSIZE (ANY, PAGE, ROW, TABLE or TABLESPACE) and held according to the isolation level and when the program commits.
| Problem | What happens | Typical signs |
|---|---|---|
| Timeout | A program waits longer than the site's timeout limit for a lock | SQLCODE -911 or -913, reason 00C9008E, message DSNT376I |
| Deadlock | Two programs each hold a lock the other needs; DB2 picks a victim | SQLCODE -911 or -913, reason 00C90088, message DSNT375I |
| Lock escalation | Too many page or row locks on one object, so DB2 takes one table space lock | Message DSNI031I; other programs suddenly wait |
The main tuning levers are in the application as much as in DB2:
- Commit often in batch, using a checkpoint and restart design, so locks and claims are released. Long units of work also block online REORG.
- Use the lowest suitable isolation. Cursor stability (CS) with
CURRENTDATA(NO)lets DB2 avoid many locks; uncommitted read (UR) avoids almost all, but only where reading uncommitted data is acceptable. - Access tables in a consistent order across programs to reduce deadlocks.
- Review LOCKSIZE and LOCKMAX. Row locking reduces contention on hot pages but costs more locks; lock escalation thresholds should suit the workload.
Data sharing, at concept level
In data sharing, several DB2 subsystems (members) in a Parallel Sysplex share the same databases. Work can run on any member, so a member can be stopped for maintenance while others continue.
Data sharing adds global locking and coherency overhead, so lock contention and group buffer pool sizing become tuning topics. If a member fails, its locks become retained locks that protect uncommitted changes until the member restarts, so restarting a failed member quickly matters.
Troubleshooting performance
DB2 writes trace data to SMF: accounting records (type 101) per thread or transaction, statistics records (type 100) for the subsystem, and performance traces (type 102) for detailed problems. Monitors such as IBM OMEGAMON for Db2, BMC AMI and Broadcom tools format them.
| Accounting time | Means |
|---|---|
| Class 1 elapsed | Total time of the thread, in the application and in DB2 |
| Class 2 elapsed | Time spent inside DB2 |
| Class 3 suspensions | Time inside DB2 spent waiting: lock waits, synchronous I/O, log writes, and so on |
Compare them: if class 2 is small against class 1, the time is in the application or elsewhere, not DB2. If class 3 is high, its breakdown shows whether you are waiting on locks or I/O. High class 2 CPU points to access paths: go back to EXPLAIN.
Common mistakes
A good ratio can hide a few objects doing heavy synchronous I/O. Look at synchronous reads per object and per second, not just the overall percentage.
A longer wait only hides the holder. Find the program holding the lock and why it does not commit.
Uncommitted read can return data that is later rolled back. Use it only where the business accepts that, such as rough counts.
What you will see at work
- DBAs review accounting reports daily for the top consumers of CPU and wait time, and work with developers on the worst.
- Production support recognises -911 and -913 reason codes and checks which program held the lock before rerunning anything.
- In data sharing shops, planned maintenance moves work between members so DB2 stays available during upgrades.
Key terms
Check your understanding.
Take this lesson's quiz and save your progress. Free.