IBM DB2 LUW Performance Tips

1. The following monitor switches should generally be turned on at all times:
   • Bufferpool
   • Table
   • Sort
   • UOW
   • Timestamp
   • And, if your database is prone to lock contention issues, Lock

   To have these monitor switches active at all times, run the command:

   db2 "update dbm cfg using DFT_MON_BUFPOOL on DFT_MON_SORT on DFT_MON_TABLE on DFT_MON_UOW on DFT_MON_TIMESTAMP on"

   If you are unable to run the monitors on by default, then they should be turned on manually at the command prompt (for each session):

   db2 "update monitor switches using sort on bufferpool on table on timestamp on uow on"

2. Once the monitor switches are active, use the GET SNAPSHOT commands to obtain performance data from DB2:
   • db2 "get snapshot for database on DBNAME" | more
   • db2 "get snapshot for tables on DBNAME" | more

   If you are having locking issues and have turned on the LOCK monitor switch, then:

   • db2 "get snapshot for locks on DBNAME" | more

   Instead of scrolling through the snapshot information with the more command, many DBAs prefer to direct the output to a file and view it with their favorite editor. For example:

   • db2 "get snapshot for database on DBNAME" > filename.txt
   • view filename.txt
3. Review the output of the "get snapshot for database" command and look for "Database Files Closed". As a rule of thumb, if this value is greater than zero, continue to increase database MAXFILOP by 25% until the number of files closed a) stops increasing, or b) becomes and remains zero.
   • Closing Files has very high CPU overhead and slows down SQL performance
4. Find out which tables, if any, are causing your database to have performance pain:
   1. Review the output of the "get snapshot for database" command and add together "Commit statements attempted" plus "Rollback statements attempted" to find the Number of Transactions attempted.
   2. Multiply the Number of Transactions attempted by 10 (ten)
   3. Review the output of the "get snapshot for tables" command and scan it to find Tables having "Rows Read" greater than "10 x Number of Transactions".
      • Tables with Rows Read > 10x Transactions are likely in need of physical design tuning (need new indexes to avoid scans, or make use of MQTs or MDCs)
5. For more DB2 LUW Performance tips, check out these popular blogs:
   • DB2 LUW Performance: Bufferpool Hit Ratios and Folly
   • DB2 LUW Performance: Are you REALLY ready for Production?
   • DB2 LUW Performance: The DNA Test of Performance Accountability
   • DB2 LUW Performance: Identifying Mosquito Swarms