Database Design Following are some recommendations for creating a database for maximum concurrency. Most of the recommendations must be considered during design, before the tables and table spaces are physically implemented, because changing after the data is in use would be difficult. Use segmented, not simple, table spaces. Doing so will keep rows of different tables on different pages, so the page locks will lock rows for only a single table. Use LOCKSIZE parameters appropriately. Keep the amount of data locked at a minimum unless the application requires exclusive access. Consider spacing out rows for small tables with heavy concurrent access by using MAXROWS =1. Row-level lock could also be used to help with this, but the overhead is greater, especially in a data sharing environment. Use partitioning where possible. Doing so can reduce contention and increase parallel activity for batch processes and can reduce overhead in data sharing by allowing for the use of affinity routing to different partitions. Locks can be taken on individual partitions. Use Data Partitioned Secondary Indexes (DPSI), which promotes partition independence and reduces contention for utilities. Consider using LOCKMAX 0 to turn off lock escalation. In some high-volume environments, this may be necessary. NUMLKTS may need to be increased, and the applications must commit frequently. Use volatile tables (discussed in Chapter 4). Doing so reduces contention because an index will also be used to access the data by applications that always access the data in the same order. Have an adequate number of databases. Reduce DBD locking if DDL, DCL, and utility execution are high for objects in the same database. Use sequence objects to provide for better number generation, without the overhead of using a single control table.
Application Design Following are some recommendations for lessening the number of locks taken by the application and for best concurrency. Access tables in the same order to prevent applications from deadlocking. Have commits in the applications. With a proper commit strategy, you can reduce contention, achieve lock avoidance, reduce rollback time for an application, reduce elapsed time for system restart, and allow other processes, such as online reorgs, to interrupt. Code retry logic for deadlocks. Bind with appropriate parameters. Use CURRENTDATA(NO) and ISOLATION(CS) to allow DB2 to attempt lock avoidance, which will also allow for releasing locks as soon as possible. Use ACQUIRE(USE), which takes locks only when necessary. Use uncommitted read where appropriate, especially at the statement level. Close all cursors as soon as possible, allowing for locks and resources to be freed. WITH HOLD will release locks at commit if RECURHL is set to YES.
Lock Monitoring A few tools are available for monitoring lock activity in DB2: Explain When using Explain to see what access path DB2 will choose, you can also view what object locks are planned for the query. (For information on how to run Explain, see Chapter 17.) The PLAN_TABLE has a column named TSLOCKMODE, which shows the initial lock mode for the table. This mode will apply to the table or table space, depending on the value of LOCKSIZE and whether the table space is simple or segmented. Accounting and Statistics Reports A DB2 monitoring tool produces accounting and statistics reports for reviewing the locking activity. NOTE In order to ensure that the correct information is available for the reports, it is advised to always have statistics classes 1, 3, and 4 and accounting classes 1 and 3 active.
In these reports, you can view counters for timeouts, deadlocks, suspensions, escalation, lock/unlock requests, and claim/drain requests. The following example shows the accounting-trace output for an application. LOCKING TOTAL ------------------- -------- TIMEOUTS 1 DEADLOCKS 0 ESCAL. (SHAR) 0 ESCAL. (EXCL) 0 MAX PG/ROW LCK HELD 5 LOCK REQUEST 0 UNLOCK REQST 0 QUERY REQST 0 CHANGE REQST 0 OTHER REQST 0 LOCK SUSPENS 2 IRLM LATCH SUSPENS 0 OTHER SUSPENS 0 TOTAL SUSPENS 2 DRAIN/CLAIM TOTAL -------------------- -------- DRAIN REQST 0 DRAIN FAILED 0 CLAIM REQST 5 CLAIM FAILED 0
The statistics report would show very similar counts, by subsystem, as well as information about events per second/thread/commit. Displaying Locks To help solve application concurrency problems, you can display the locks that are being held. By using the DISPLAY DATABASE (dbname) LOCKS command, you can see many properties about a given lock, as shown in Figure 16-2. Figure 16-2. DISPLAY DATABASE LOCKS 
|
