Use Pseudo Journal Toolset to Estimate Journaling Requirements

By:

Many System i administrators would like to enjoy the benefits of journaling their database objects that contain critical business data, but they're afraid of the overhead (in terms of disk and CPU resources) that journaling may add to their systems. IBM recently made available a free, downloadable tool that estimates the system overhead that journaling may generate. You can download the Pseudo Journal toolset, an installation guide, and tutorial here. [2]

The Pseudo Journal toolset monitors changes (e.g., Inserts, Updates, Deletes) made to your database object(s). After monitoring the database activity, you can use the toolset to provide an estimate of how much disk and CPU resources would be consumed if journaling had been active when the monitored activity occurred.

The toolset consists of two CL commands that are created by the install process. The Start Pseudo Journal (STRPSJRN) CL command is used to identify which database objects you want to monitor. Because this activation step can be a long-running operation, it's recommend that you execute the STRPSJRN command in a batch program. The following is an example of the command syntax used to monitor a single DB2 object (table TAB1 in the DATALIB library):

STRPSJRN FILE(DATALIB/TAB1)   +
         DTAFILE(QGPL/JRNEST1) MBROPT(*CREATE)  +
         NBRSAMPLE(75) INTERVAL(10)

Because you frequently want to monitor multiple DB2 tables (or physical files), the FILE parameter supports generic file names or the special value *ALL to monitor all the DB2 tables in a single library. Use the DTAFILE parameter to identify the name of the file that stores all the monitoring statistics. The amount of monitoring and overhead generated by the STRPSJRN command is controlled with the NBRSAMPLE and INTERVAL parameters. The NBRSAMPLE parameter controls how often the Pseudo Journal tool wakes up and collects activity statistics for the identified database objects. The associated INTERVAL parameter specifies how frequently (in seconds) the Pseudo Journal tool is activated. In this example, the tool collects 75 different activity samples that are captured every 10 seconds.

After all the activity statistics have been collected by the STRPSJRN command, use the other command in the toolset, Display Pseudo Journal Data (DSPPSJDTA), to analyze the collected data. The DSPPSJDTA command processes all the raw data and generates reports that show the estimated disk and CPU resources that would have been added if journaling had been active when the monitored changes were made to the specified DB2 objects.

Here's an example of how to execute the DSPPSJDTA command:

DSPPSJDTA DTAFILE(QGPL/JRNEST1) +
             FILE(*ALL) RPTTYPE(*SUMMARY)

The DTAFILE parameter needs to point to the same data file (DTAFILE) parameter specified on the STRPSJRN command. With the FILE parameter, you can view the journal estimate reports for an individual DB2 table, or use *ALL to generate journal estimate reports for all the database objects monitored by the STRPSJRN command.

The Report Type (RPTTYPE) parameter is used to select which of the three journal estimate reports you want to generate. The Summary report (*SUMMARY) is a simple report to begin your journal analysis. If you're analyzing journal estimates for multiple database objects, the Summary report shows the journal overhead estimates for all the DB2 objects. The List report (*LIST) returns the journal overhead estimate contributed by each individual database object.

Here's a snapshot of the more useful estimates returned by the Summary and List reports:

DISK WRITE SUMMARY========
   Disk Writes                2.014K
   Disk Writes W/Caching      3

JOURNAL STORAGE SUMMARY========
   Total Data                     287.044 KB
   Total Data (W/O Before Image)  279.873 KB (without Before Images)

JOURNAL DISK I/O THROUGHPUT SUMMARY========
  Average Throughput              316 B/s
  Maximum Throughput            4.639 KB/s

PERFORMANCE SUMMARY=======
  CPU Overhead                   43.168 ms
  CPU Overhead w/caching         20.084 ms

  Added Elapsed Time              1.004 ms
  Added Elapsed Time w/caching    0.001 ms

These reports not only provide raw estimates for the Disk and CPU resources required by journaling, but they also include more advanced estimates that take into account optional journal settings. For example, they include estimates on how much Disk and CPU performance can be improved with Journal Caching (i5/OS licensed feature number 42) installed or how removing the journaling of before images improves the journal disk storage requirements.

An available histogram report gives you a view of the journal resource estimates over time to see whether the tool is estimating any severe peaks in journal activity on your system. None of the report types produce a recommended system configuration. You are responsible for taking the CPU and disk resource estimates and determining the system configuration needed to accommodate journaling of the specified database objects.

Hopefully this gives you enough basic information about this new toolset to get started on your own. The Pseudo Journal toolset can be used on any system running V5R3 or V5R4. IBM is planning on improving the toolset and documentation, so please visit the website on a regular basis.

This toolset is NOT officially supported by IBM. The toolset is provided to you and your company "AS IS" without any warranties of any kind.

Editors Note: I want to thank Kent Milligan for sharing the great information on this cool new IBM tool. Most of you know that I am a big proponent of journaling. I firmly believe that all production systems should implement journaling over all production files and other objects.

I would, however, suggest that before you buy hardware based upon the estimates of this new IBM tool, that you run the tool and get your estimates, and then start journaling a big subset of your database files that are part of your sample. You can then use the Performance Tools LPP to see how accurate the estimates prove to be in your production environment. My guess is that the tool's estimates will be very close to reality, but as Kent says, this is an unsupported IBM tool that's provided "AS IS".

-- Dan Riehl