Database performance

The FTM database is a key component of application performance. The FTM database contains a core set of indexes to facilitate good performance on select statements. However, the database may not always make efficient use of indexes unless it has good statistics at the time the statement access plans are computed and cached. Databases that are newly created with low volumes of operational data may perform poorly to start with, in particular deadlock conditions may occur frequently. It is imperative performance tests are run after the database has already processed some load (at least 100,000 single transaction messages) and has had a statistics job executed successfully against all operational tables. The following operational tables are most important:
  • OBJ_BASE
  • H_OBJ_BASE
  • BATCH_BASE
  • TRANSMISSION_BASE
  • TRANSACTION_BASE
  • TXN_PAYMENT_BASE
  • TXN_SECURITIES_BASE
  • FRAGMENT_BASE
  • OBJ_OBJ_REL
  • OBJ_VALUE
  • COUNTER
This same list of tables is also the set that will grow fastest and should be considered for maintenance and housekeeping tasks such as archiving and purge. Other database administration tasks should also be planned such as REORG jobs for tables, tablespaces and indexes with primary focus on the key operational tables.

DB2® Database Configuration Parameters that may need to be tuned per installation:
  • LOCKLIST = 2000 (4KB Pages). Set LOCKLIST to a large value to avoid row-to-table lock escalation. Lock escalation can cause deadlocks.
  • MAXLOCKS = 50. The maximum percentage of LOCKLIST before row-to-table lock escalation occurs.
  • Memory related parameters with their default FTM values:
    • APPLHEAPSZ = 2048
    • APP_CTL_HEAP_SZ = 1024
    • SORTHEAP = 512
In DB2 there are a number of registry settings that are set in FTM to help with concurrency:
DB2_SKIPINSERTED=OFF
If set, this can cause race conditions in event processing
DB2_EVALUNCOMMITTED=ON
Used to reduce locking if table scans are used
DB2_INLIST_TO_NLJN=YES
Used to force use of index when using IN clause to prevent deadlocks