In a pivotal scene
towards the end of the original Star Wars film, Obi-Wan Kenobi issues the iconic line: Use the
Force, Luke. The ghost of Obi-Wan is urging to Luke turn off his targeting computer
on his attack of the Death Star and instead rely on his feelings (i.e., the
Jedi Force) to guide his proton torpedoes.
I’m sure you’re probably thinking how in the world does the Jedi Force relates to Db2 objects on IBM i… Db2 tables and indexes, which includes physical files and logical files, actually have a legacy force setting. That force setting is the Force Write Ratio (FRCRATIO) parameter on Db2 for i tables and indexes have the Force Access Path (FRCACCPTH) parameter. If you’re using non-default values for these parameters, then effectively you’re turning off the targeting computer like Luke decides to do.
While turning off the computer works for Luke (I figured a spoiler alert wasn’t required for a movie that I saw as a kid) – hopefully, the “NOT!” in this entry title makes it clear that’s not a recommended practice on IBM i. These force settings were introduced at a time (in the 1980’s) when computer hardware was less reliable. Thus, there may have been situations in the old days where applications needed the ability to ensure critical data changes were forced from memory to permanent storage. While this may sound appealing for some applications, be aware that there is a performance cost to using these force settings.
If a non-default value of 1 is specified for the FRCRATIO parameter on a Db2 table, then every new row or changed row will result in the associated data being forced to disk while the application waits. The default behavior enables the program to continue processing while the row data is saved to memory and depends on the IBM i operating system to decide when the best time is to write the changed data to disk. The non-default behavior will also prevent other jobs from performing blocked reads on the Db2 table with the force setting which can also negatively impact system performance.
The FRCACCPTH setting available for Db2 indexes can have a similar negative impact on performance. A setting of *YES can degrade performance since it causes the application program to wait while Db2 forces every change to the access path (i.e., index tree) to disk. In contrast, the default setting of *NO allows the program to continue and on the IBM i operating system to decide when the best time is to write the changed index data from memory to disk.
If you’re really worried about losing critical database changes made by your application, a better alternative is to rely on the IBM i journaling support. With journaling enabled, data changes are first written to the attached journal receiver, and the journal receiver entries are written to storage before the changes are written to the memory copy of the Db2 object. Journaling provides protection against unexpected system outages and depends on the IBM i operating system to efficiently write change data to disk.
Despite the Db2 force parameters primarily being historical artifacts, our IBM Expert Labs team regularly finds Db2 objects with non-default settings for the FRCRATIO and FRCACCPTH parameters. on client systems when performing a Db2 & SQL Assessment for customers. The SYSFILES IBM i service in QSYS2 makes it easy to find Db2 objects using the non-default settings as the following query demonstrates:
SELECT table_name, table_schema, Force_Write_Ratio, Force_Keyed_Access_Path FROM qsys2.sysfiles WHERE system_table_schema = 'DBSCHEMAX' AND (Force_Write_Ratio IS NOT NULL OR Force_Keyed_Access_Path = 'YES')
Since SYSFILES is an IBM i service based on a user-defined table function, make sure you scope the search request as much as possible to minimize the performance impact of this query.
Hopefully, I’ve been successful in convincing you not to use the Db2 force parameters and save your intuition and feelings for other aspects of application development and design. Your applications and system will perform best when you rely on your IBM i computer to manage when changed data in memory gets written to permanent storage.
