Oldie, but Goodie - Journal Minimal Data

 On this blog, I often highlight recent Db2 for i enhancements to ensure that users are leveraging the latest Db2 functions and features to speed up performance, simplify app development, and ease management of their databases.

With all the great enhancements churned out by the IBM i development team in Rochester, it’s easy to forget about valuable features delivered in older releases. I think a good way to characterize these older features is the The Oldies, but Goodies phrase coined by a Los Angeles DJ.

In the last several SQL Performance Assessments that I’ve performed for IBM i clients, I’ve come to realize that Journal Minimal Data is an older feature (first delivered almost 25 years ago with V5R1!) that clients have either forgotten about or not aware of. This feature can speed up applications that are updating journaled tables or physical files by writing only the changed data to the journal instead of the entire record image. This performance boost can be substantial in the cases where the update operation is changing the value of a small number of columns. The following SQL statement that I recently analyzed in a client’s SQL workload definitely fell into that category. 

UPDATE table1 SET column1 = 'New Value' WHERE column1 = ' '

In the client’s case, the definition of table1 contained 50+ columns with a total record length of 1530 bytes. The column being updated, column1, was a fixed-length character column with a length of 50 bytes. Those metrics mean that 1530 bytes were being written to the journal every time this Update statement was executed - even though only 50 bytes were being changed.  With the Journal Minimal Data support enabled, 30 times less data would be written to the journal. 

There was a tradeoff with this initial Journal Minimal Data support because the journal entry wasn’t readable.  As a result, you couldn’t use the journal entry for audit purposes to determine who changed a column to a particular value. Knowing that could be a potential showstopper for some users, IBM development came to the rescue and added a Field Boundary setting (*FLDBDY) for the MINENTDTA parameter. This *FLDBDY value provides the performance benefits of minimizing the amount of data written to the journal entry along with enabling the journal entries to be readable for audit purposes. BTW, this functionality still falls into the Oldie but Goodie category since it’s been around since V5R4 which GA’d back in 2006.

It's still a good idea to keep up with the latest Db2 for i enhancements with this blog and the Db2 Technology Updates site, but also don’t forget to mine the IBM i documentation for classic “old” features that could boost performance or simplify your application development.