In one of my previous articles I described what table properties could be used (CREATE or ALTER TABLE) for XML-based OLTP systems like Temenos T24. Today I will show and explain some DB2 registry settings that are typically in use for such (and other) systems.
A list with almost all (documented) registry and environment variables can be found in the DB2 Information Center. As you can see those variables are grouped into different categories, e.g., compiler, communication, performance, or general settings. As some settings require a restart of DB2, the configuration is typically applied once when setting up a system.
DB2_PARALLEL_IO=*
This tells DB2 on how to handle prefetch requests and what to assume about disks per container. Prefetching is used to bring in data "ahead before it is used", i.e., it reduces IO wait. The above setting is simple and a good comprise. All tablespaces are covered and it is assumed that all have the same layout of 6 disks per container and hence multiple smaller prefetch requests are issues in parallel. There are different design philosophies "out there" with sometimes contradicting recommendations. Also take a look at Configuring for good performance and Optimizing table space performance when data is on RAID devices.
DB2_USE_ALTERNATE_PAGE_CLEANING=ON
Page cleaning is the reverse of prefetching. It makes sure that the bufferpool (data cache) always has enough space available to bring in additional data pages from disk. This alternate page cleaning is more aggressive in writing out changed data, makes use of IO bandwith and helps to have slots available in the bufferpool for our OLTP system.
DB2_EVALUNCOMMITTED=ON
DB2_SKIPDELETED=ON
DB2_SKIPINSERTED=ON
All three registry settings are typically used together to improve concurrency for Cursor Stability and Read Stability isolation levels. When scanning data and evaluating predicates a slightly different, "less patient" approach is taken which helps throughput in OLTP systems.
DB2MAXFSCRSEARCH=1
When inserting new data DB2 has to search for a data page with free space. That space is managed with free space control records (FSCRs) that are located every 500 pages or so (see here for details on table organization and FSCRs). By default DB2 is searching up to 5 such records in order to find a page to squeeze the new record in. This helps to fill up pages and to efficiently reuse space left from delete or some update operations. However, it does not help insert performance which is critical in OLTP system. Hence a setting of 1 is a nod towards performance while still trying to reuse space. A more drastic setting would be APPEND ON for a table where new data is always inserted at the end.
DB2_FORCE_NLS_CACHE=TRUE
This setting only applies on AIX, not on Linux and is used to cache the codepage and territory information for a connection. As T24 and other OLTP systems do not change "national language support" (NLS, i.e., the codepage and territory information) this helps performance slightly and also avoids possible lock contention while accessing that information.
This already was my quick overview of few of the settings that should be considered for OLTP systems like Temenos T24 on DB2. What is left is to take a look at typical DB2 database manager and database configurations. But that is another article...