Changing data in your DB2 for z/OS database can be an expensive process, especially when done in high volumes. While DB2 does provide some efficiency for updating data, there are also several things you can do in your database and process design to minimize the effect of change on the database, and improve application performance. Minimizing change is a great way to reduce the need for database REORGs and backups, and minimizing these types of utilities can improve database availability and reduce overall application CPU consumption.
To achieve high availability and high performance, we can target several areas such as minimizing logging, minimizing updates, and designing for ascending key inserts. The challenging part is knowing the volume of data and designing the tables and applications to support minimal change. Trying to do this after a standard implementation is nearly impossible.
Minimizing changes can dramatically reduce logging overhead and improve application throughput, and reduce the number of locks taken and contention between application processes. There are several database settings that directly impact the amount of logging that occurs during updates.
Use of DATA CAPTURE
For updates, DB2 records undo/ redo records in the log. There’s an option on a CREATE or ALTER TABLE definition called DATA CAPTURE. The two settings are DATA CAPTURE CHANGES or DATA CAPTURE NONE, the default being NONE. For inserts and deletes, DB2 is always recording a full image of the row affected, but for updates, DB2 can minimize the data recorded in the log. The DATA CAPTURE CHANGES feature tells DB2 you’re planning to use the log information for some sort of data replication or log analysis. There are several data replication tools that can read log records or use a DB2 log exit to replicate database changes to other databases or processes. In order for the replication to work properly for updates, it needs the entire before and after image of the rows changed. So, DATA CAPTURE CHANGES will cause DB2 to log the entire before and after images of rows changed via update statements in the undo/redo records in the log.
DB2 will minimize the information logged during updates if DATA CAPTURE NONE is set for a table. A good performance practice is to specify DATA CAPTURE NONE for a table unless you’re specifically planning on replicating the table via a product that reads log records.
Logging for Index Changes
When defining indexes, you can specify COPY NO (default) or COPY YES. COPY YES tells DB2 you’ll be using the COPY and RECOVER utilities on the index. This can be helpful for large, infrequently updated indexes, but it increases the logging for these indexes. Before setting COPY YES for indexes, make sure that recovering the index will be more efficient than rebuilding it. If rebuilding the index is faster (given available CPU), then setting COPY NO will reduce logging overhead for index changes.
Table Design for Minimal Logging