Journaling: How to View and More Easily Audit Minimized Journal Entries on the IBM System i Platform
Web Doc
Note: This is publication is now archived. For reference only.
Abstract
The IBM System i platform provides a novel but often under-used feature known as "minimizing journal entries". This space-saving feature became available in V5R1 and has been enhanced in V5R4.
Support for Minimized Entry Data (*MINENTDTA) was announced in V5R1. Many users have said that this feature can save substantial space, but auditors do not let them use it because the auditors have trouble deciphering the resulting images. Now V5R4 offers Auditable (viewable) minimized journal entries, which makes it easier for the auditors to see the results.
While you may have already known this and tried using the new V5R4 MINENTDTA(*FLDBDY) setting, you might still be confused about how to decode the data in the resulting journal entries. This Technote explains how to:
- Determine which journals are good candidates for Auditable Journal Minimal Data (JMD)
- Enable Auditable JMD on your system
- Generate human readable minimized journal entries
- Decode the resulting minimized journal entries
Written by Amanda Fogarty, Software Engineer
IBM Systems & Technology Group
Development System i Journaling
Contents
A little background
Journal entries are stored within journal receivers. Journal entries contain data that tells the story about what happened on a system. The entries are written to disk before the corresponding database row images reach disk. As a consequence, journal entries play an important role, providing protection until the changed objects themselves reach disk or some other backup media. However, journal entries consume space, disk space costs money, and the amount of storage on the system is finite. Therefore, it is better that your journal entries consume less space.
The idea behind the minimized entry data setting is that the journal entries can be stored in a form that requires only modest amounts of space, allowing the system to interpret what needs to be done in a recovery situation. Less storage space for the journal receivers means more storage for something else.
Journal minimization has long been an attractive space-saving feature, as long as users are not trying to interpret the entries for auditing purposes. The problem prior to V5R4 was that users could not easily decipher the resulting minimized entries of V5R1. Now with V5R4, they can more readily read, understand, and recognize the minimized entries.
When Auditable JMD is most beneficial
While you can certainly enable JMD for all of your journals, consider which journaled files or tables will reap the greatest benefit from the use of JMD. You may want to start with journals that servicing large physical files and SQL tables, especially if many rows therein are updated often.
Not all types of journal entries are eligible to be stored in a minimized representation. In fact, the only journal entries that are minimized are those that are produced when a row is updated. The minimization affects both the “before” (R/UB) and “after” (R/UP) images that are produced. Therefore, batch jobs that update millions of rows within a large table, but only modify a few columns within each row, are likely to witness the greatest benefit.
In effect, the resulting R/UB and R/UP journal images house only the modified columns plus some surrounding metadata.
At this point you may be running replication software that is dependent on journaling, and you may be wondering if you can still use Auditable JMD. The good news is that use of the MINENTDTA(*FLDBDY) attribute reduces disk traffic on the source system in a highly available environment, and it reduces communication line traffic across a remote journal. Even better, the MINENTDTA(*FLDBDY) attribute actually makes the replay actions by the high availability (HA) vendor software more efficient. When they have fewer bytes to replay, they consume fewer cycles on the target side. It is a "win-win" situation.
Nearly all of the HA business partner applications have been modernized to recognize and properly replay the minimized journal entry representations. Be sure to check with your specific HA vendor to ensure that their application supports the MINENTDTA(*FLDBDY) setting. Both your auditors and your HA vendors will be pleased to know that you have enabled this option.
Enabling Auditable JMD on your system
To enable auditable JMD for your pre-existing journals and journaled files, you must use the Change Journal (CHGJRN) command and specify the MINENTDTA(*FLDBDY) parameter as shown in the following example:
CHGJRN JRN(JRNLIB/JRN) JRNRCV(*GEN) MINENTDTA(*FLDBDY)
To similarly enable auditable JMD for any new journals that you elect to create, you can specify MINENTDTA(*FLDBDY) on the Create Journal (CRTJRN) command as shown in the following example:
CRTJRN JRN(JRNLIB/JRN) JRNRCV(JRNLIB/RCV1) MINENTDTA(*FLDBDY)
Keep in mind the following rules or restrictions:
Now that you have Auditable JMD activated, let us see what an auditable, minimized journal entry looks like.
Generating human readable minimized journal entries
To see what a readable minimized journal entry looks like, we must generate one. For example, let us pretend that we have a retail store and keep track of both customers and products on-hand. To prime the environment for our store, we run the following commands and see what the results are:
CREATE COLLECTION VJMDTEST
We enter the following command to help identify the kind of journal minimization that we want to try:
CL: CHGJRN JRN(VJMDTEST/QSQJRN) JRNRCV(*GEN) MINENTDTA(*FLDBDY)
We now create the required tables:
CREATE TABLE VJMDTEST/CUSTOMERS
(CUSTID INTEGER NOT NULL PRIMARY KEY,
NAME VARCHAR (30) NOT NULL WITH DEFAULT,
ADDRESS VARCHAR (30) NOT NULL WITH DEFAULT,
CITY VARCHAR (15) NOT NULL WITH DEFAULT,
STATE CHAR (2) NOT NULL WITH DEFAULT,
ZIP CHAR (10) NOT NULL WITH DEFAULT,
PHONE CHAR (10) NOT NULL WITH DEFAULT)
CREATE TABLE VJMDTEST/PRODUCTS
(PRODID INT NOT NULL PRIMARY KEY,
PRICE DECIMAL (7,2) NOT NULL WITH DEFAULT 0.00,
DESC VARCHAR (30) NOT NULL WITH DEFAULT,
QTY INT NOT NULL WITH DEFAULT 100,
CATEGORY VARCHAR (15), AISLE INT)
Next we add some customers to our table:
INSERT INTO VJMDTEST/CUSTOMERS VALUES(1, 'JOE SCHMOE',
'123 SOME STREET EAST', 'ANYTOWN', 'NY', '12345-2222',
'1235551122')
INSERT INTO VJMDTEST/CUSTOMERS VALUES(2, ‘JANE DOE',
'987 ELM STREET NORTH', 'MYTOWN', 'CA', '98765-9999',
'9875555432')
INSERT INTO VJMDTEST/CUSTOMERS VALUES(3, 'SUZY QUE',
'5454 HARRIET AVE SE', 'CHICAGO', 'IL', '57463-3829',
'4834772299')
Then we stock our shelves with some products:
INSERT INTO VJMDTEST/PRODUCTS VALUES(1, 4.99, 'SHAMPOO', 100,
'HEALTH/BEAUTY', 5)
INSERT INTO VJMDTEST/PRODUCTS VALUES(5, 25.99, 'DOG FOOD', 50,
'PETS', 12)
INSERT INTO VJMDTEST/PRODUCTS VALUES(34, 10.99, 'TSHIRT', 200,
'MENS', 25)
INSERT INTO VJMDTEST/PRODUCTS VALUES(77, 5999.99, 'PLASMA TV', 10,
'ELECTRONICS', 30)
We put a fresh journal receiver in place before the store opens:
CL: CHGJRN JRN(VJMDTEST/QSQJRN) JRNRCV(*GEN)
It is important to note that so far no journal entries have been minimized. Remember that only row update journal entries are eligible for minimization. Now let us have some applications update the values in our tables. Suppose that PRODID=77 is a Plasma TV.
UPDATE VJMDTEST/CUSTOMERS SET PHONE='9875559301'
WHERE CUSTID=2
UPDATE VJMDTEST/PRODUCTS SET PRICE=59.99 WHERE PRODID=77 (The price for the Plasma TV was set incorrectly)
UPDATE VJMDTEST/PRODUCTS SET QTY=QTY-1 WHERE PRODID=77 (It seems that one Plasma TV was sold)
UPDATE VJMDTEST/PRODUCTS SET PRICE=5999.99 WHERE PRODID=77 (The Plasma TV price was set to the correct value)
In this example, it looks like someone got a good deal on the Plasma TV before the price was corrected. The good thing is that we have journaling turned on. Although our entries are minimized, we can tell what was changed.
Decoding the resulting minimized entries
Now we look at the journal entries that we have in the journal receiver by entering the following command:
DSPJRN JRN(VJMDTEST/QSQJRN)
You should see four sets of R/UB and R/UP entries (R is for record, UB is for “before update”, and UP is for “after update”). The first R/UB in the set reveals the before image, which was captured just before we updated Jane Doe’s phone number in the CUSTOMERS table. Enter option 5 next to the entry and press Enter. The contents of the entry are shown in the following figure; you can press F6 to see only the entry specific data.
Notice the section in the lower half of the screen, below the Entry specific data (see the red box in the figure above). It is labeled as “Null value indicators” (NVIs). The values listed here tell us many things. First, count the number of nines and zeros in our example. There are seven of them. Seven corresponds to the number of columns in our CUSTOMERS table. Therefore, there’s an indicator for each column.
What do the numbers mean? Refer to the following table for the meaning of each NVI value:
In our example, we know that Jane Doe’s phone number used to be 9875555432. That is what we see in the Entry specific data section shown in the screen. The fact that only the final NVI is zero reminds us that none of the other fields were modified.
What happened under the covers? The nines suggest that none of the first six columns were physically written to the journal entry. Therefore, we saved space. When the same leading six columns were displayed using the DSPJRN command, the default value for each of the six fields (blanks, in this case) was displayed instead.
Now look at the after image in the following figure for Jane as revealed by the contents of the first R/UP journal entry.
What do you notice? We can see that Jane Doe’s new phone number is 9875559301, but we do not see any other data. Instead, we see the blanks used as fillers at display time for the fields that were not captured in the journal entry.
Next, we look at the second pair of R/UB and R/UP entries, that is, the first instance of modifying the PRODUCTS table (the table that temporarily housed an erroneous price for plasma TVs). The CUSTOMERS table consisted of predominantly Char and Varchar fields, and the default values were blanks, so the character view was suitable for displaying those entries. However, the PRODUCTS table has a richer set of field types including Decimal. Consequently, it is helpful to display entries from the PRODUCTS table in hexadecimal format. Press F11 once to switch to the hexadecimal view.
The following figure shows the R/UB entry for the PRODUCTS table.
What do the Null value indicators reveal? Can you identify which field changed? Although the decimal points are not evident in this raw internal hexadecimal representation, we can clearly see that the original price was US$5,999.99.
Similarly, the following figure shows the matching R/UP entry.
In each of these screens, the changed values are highlighted in red boxes.
The strategy is simple. Look at the Null value indicators and match the column that each indicator corresponds to, working from left to right. Then, if the Null value indicator is 0, you know that the actual value will be present in the journal entry specific data. Find the offset in the entry (if it is not obvious like Jane Doe’s phone number was), and you will discover that both your programmers and your auditors can read it.
Summary
You should now understand how to view and audit minimized journal entries on the System i platform. In this Technote, you learned which journals are great candidates for Auditable JMD. You learned how to specify the MINENTDTA(*FLDBDY) option on the CRTJRN or CHGJRN commands to activate Auditable JMD. You learned how to use the Null value indicators to determine exactly which field in the record was changed, and know the new value for this column. When you have determined if you have good candidate journals for Auditable JMD, educate your auditors, give Auditable JMD a test drive, and activate it on your production system journals.
Journal entries are stored within journal receivers. Journal entries contain data that tells the story about what happened on a system. The entries are written to disk before the corresponding database row images reach disk. As a consequence, journal entries play an important role, providing protection until the changed objects themselves reach disk or some other backup media. However, journal entries consume space, disk space costs money, and the amount of storage on the system is finite. Therefore, it is better that your journal entries consume less space.
The idea behind the minimized entry data setting is that the journal entries can be stored in a form that requires only modest amounts of space, allowing the system to interpret what needs to be done in a recovery situation. Less storage space for the journal receivers means more storage for something else.
Journal minimization has long been an attractive space-saving feature, as long as users are not trying to interpret the entries for auditing purposes. The problem prior to V5R4 was that users could not easily decipher the resulting minimized entries of V5R1. Now with V5R4, they can more readily read, understand, and recognize the minimized entries.
When Auditable JMD is most beneficial
While you can certainly enable JMD for all of your journals, consider which journaled files or tables will reap the greatest benefit from the use of JMD. You may want to start with journals that servicing large physical files and SQL tables, especially if many rows therein are updated often.
Not all types of journal entries are eligible to be stored in a minimized representation. In fact, the only journal entries that are minimized are those that are produced when a row is updated. The minimization affects both the “before” (R/UB) and “after” (R/UP) images that are produced. Therefore, batch jobs that update millions of rows within a large table, but only modify a few columns within each row, are likely to witness the greatest benefit.
In effect, the resulting R/UB and R/UP journal images house only the modified columns plus some surrounding metadata.
At this point you may be running replication software that is dependent on journaling, and you may be wondering if you can still use Auditable JMD. The good news is that use of the MINENTDTA(*FLDBDY) attribute reduces disk traffic on the source system in a highly available environment, and it reduces communication line traffic across a remote journal. Even better, the MINENTDTA(*FLDBDY) attribute actually makes the replay actions by the high availability (HA) vendor software more efficient. When they have fewer bytes to replay, they consume fewer cycles on the target side. It is a "win-win" situation.
Nearly all of the HA business partner applications have been modernized to recognize and properly replay the minimized journal entry representations. Be sure to check with your specific HA vendor to ensure that their application supports the MINENTDTA(*FLDBDY) setting. Both your auditors and your HA vendors will be pleased to know that you have enabled this option.
Enabling Auditable JMD on your system
To enable auditable JMD for your pre-existing journals and journaled files, you must use the Change Journal (CHGJRN) command and specify the MINENTDTA(*FLDBDY) parameter as shown in the following example:
CHGJRN JRN(JRNLIB/JRN) JRNRCV(*GEN) MINENTDTA(*FLDBDY)
To similarly enable auditable JMD for any new journals that you elect to create, you can specify MINENTDTA(*FLDBDY) on the Create Journal (CRTJRN) command as shown in the following example:
CRTJRN JRN(JRNLIB/JRN) JRNRCV(JRNLIB/RCV1) MINENTDTA(*FLDBDY)
Keep in mind the following rules or restrictions:
- You must specify the creation of a new journal receiver along with the MINENTDTA(*FLDBDY) parameter; therefore, you must also specify JRNRCV(*GEN).
- You can specify only *FILE (for V5R1) or *FLDBDY (for V5R4) as positional values for MINENTDTA. That is, they are mutually exclusive.
- The auditable JMD feature only applies to *FILE objects.
Now that you have Auditable JMD activated, let us see what an auditable, minimized journal entry looks like.
Generating human readable minimized journal entries
To see what a readable minimized journal entry looks like, we must generate one. For example, let us pretend that we have a retail store and keep track of both customers and products on-hand. To prime the environment for our store, we run the following commands and see what the results are:
CREATE COLLECTION VJMDTEST
We enter the following command to help identify the kind of journal minimization that we want to try:
CL: CHGJRN JRN(VJMDTEST/QSQJRN) JRNRCV(*GEN) MINENTDTA(*FLDBDY)
We now create the required tables:
CREATE TABLE VJMDTEST/CUSTOMERS
(CUSTID INTEGER NOT NULL PRIMARY KEY,
NAME VARCHAR (30) NOT NULL WITH DEFAULT,
ADDRESS VARCHAR (30) NOT NULL WITH DEFAULT,
CITY VARCHAR (15) NOT NULL WITH DEFAULT,
STATE CHAR (2) NOT NULL WITH DEFAULT,
ZIP CHAR (10) NOT NULL WITH DEFAULT,
PHONE CHAR (10) NOT NULL WITH DEFAULT)
CREATE TABLE VJMDTEST/PRODUCTS
(PRODID INT NOT NULL PRIMARY KEY,
PRICE DECIMAL (7,2) NOT NULL WITH DEFAULT 0.00,
DESC VARCHAR (30) NOT NULL WITH DEFAULT,
QTY INT NOT NULL WITH DEFAULT 100,
CATEGORY VARCHAR (15), AISLE INT)
Next we add some customers to our table:
INSERT INTO VJMDTEST/CUSTOMERS VALUES(1, 'JOE SCHMOE',
'123 SOME STREET EAST', 'ANYTOWN', 'NY', '12345-2222',
'1235551122')
INSERT INTO VJMDTEST/CUSTOMERS VALUES(2, ‘JANE DOE',
'987 ELM STREET NORTH', 'MYTOWN', 'CA', '98765-9999',
'9875555432')
INSERT INTO VJMDTEST/CUSTOMERS VALUES(3, 'SUZY QUE',
'5454 HARRIET AVE SE', 'CHICAGO', 'IL', '57463-3829',
'4834772299')
Then we stock our shelves with some products:
INSERT INTO VJMDTEST/PRODUCTS VALUES(1, 4.99, 'SHAMPOO', 100,
'HEALTH/BEAUTY', 5)
INSERT INTO VJMDTEST/PRODUCTS VALUES(5, 25.99, 'DOG FOOD', 50,
'PETS', 12)
INSERT INTO VJMDTEST/PRODUCTS VALUES(34, 10.99, 'TSHIRT', 200,
'MENS', 25)
INSERT INTO VJMDTEST/PRODUCTS VALUES(77, 5999.99, 'PLASMA TV', 10,
'ELECTRONICS', 30)
We put a fresh journal receiver in place before the store opens:
CL: CHGJRN JRN(VJMDTEST/QSQJRN) JRNRCV(*GEN)
It is important to note that so far no journal entries have been minimized. Remember that only row update journal entries are eligible for minimization. Now let us have some applications update the values in our tables. Suppose that PRODID=77 is a Plasma TV.
UPDATE VJMDTEST/CUSTOMERS SET PHONE='9875559301'
WHERE CUSTID=2
UPDATE VJMDTEST/PRODUCTS SET PRICE=59.99 WHERE PRODID=77 (The price for the Plasma TV was set incorrectly)
UPDATE VJMDTEST/PRODUCTS SET QTY=QTY-1 WHERE PRODID=77 (It seems that one Plasma TV was sold)
UPDATE VJMDTEST/PRODUCTS SET PRICE=5999.99 WHERE PRODID=77 (The Plasma TV price was set to the correct value)
In this example, it looks like someone got a good deal on the Plasma TV before the price was corrected. The good thing is that we have journaling turned on. Although our entries are minimized, we can tell what was changed.
Decoding the resulting minimized entries
Now we look at the journal entries that we have in the journal receiver by entering the following command:
DSPJRN JRN(VJMDTEST/QSQJRN)
You should see four sets of R/UB and R/UP entries (R is for record, UB is for “before update”, and UP is for “after update”). The first R/UB in the set reveals the before image, which was captured just before we updated Jane Doe’s phone number in the CUSTOMERS table. Enter option 5 next to the entry and press Enter. The contents of the entry are shown in the following figure; you can press F6 to see only the entry specific data.
Notice the section in the lower half of the screen, below the Entry specific data (see the red box in the figure above). It is labeled as “Null value indicators” (NVIs). The values listed here tell us many things. First, count the number of nines and zeros in our example. There are seven of them. Seven corresponds to the number of columns in our CUSTOMERS table. Therefore, there’s an indicator for each column.
What do the numbers mean? Refer to the following table for the meaning of each NVI value:
NVI value | NVI meaning |
0 | The true field value is shown in the entry specific data. |
1 | The field value is null. |
9 | The field value did not change. The default value for the column will be displayed. |
In our example, we know that Jane Doe’s phone number used to be 9875555432. That is what we see in the Entry specific data section shown in the screen. The fact that only the final NVI is zero reminds us that none of the other fields were modified.
What happened under the covers? The nines suggest that none of the first six columns were physically written to the journal entry. Therefore, we saved space. When the same leading six columns were displayed using the DSPJRN command, the default value for each of the six fields (blanks, in this case) was displayed instead.
Now look at the after image in the following figure for Jane as revealed by the contents of the first R/UP journal entry.
What do you notice? We can see that Jane Doe’s new phone number is 9875559301, but we do not see any other data. Instead, we see the blanks used as fillers at display time for the fields that were not captured in the journal entry.
Next, we look at the second pair of R/UB and R/UP entries, that is, the first instance of modifying the PRODUCTS table (the table that temporarily housed an erroneous price for plasma TVs). The CUSTOMERS table consisted of predominantly Char and Varchar fields, and the default values were blanks, so the character view was suitable for displaying those entries. However, the PRODUCTS table has a richer set of field types including Decimal. Consequently, it is helpful to display entries from the PRODUCTS table in hexadecimal format. Press F11 once to switch to the hexadecimal view.
The following figure shows the R/UB entry for the PRODUCTS table.
What do the Null value indicators reveal? Can you identify which field changed? Although the decimal points are not evident in this raw internal hexadecimal representation, we can clearly see that the original price was US$5,999.99.
Similarly, the following figure shows the matching R/UP entry.
In each of these screens, the changed values are highlighted in red boxes.
The strategy is simple. Look at the Null value indicators and match the column that each indicator corresponds to, working from left to right. Then, if the Null value indicator is 0, you know that the actual value will be present in the journal entry specific data. Find the offset in the entry (if it is not obvious like Jane Doe’s phone number was), and you will discover that both your programmers and your auditors can read it.
Summary
You should now understand how to view and audit minimized journal entries on the System i platform. In this Technote, you learned which journals are great candidates for Auditable JMD. You learned how to specify the MINENTDTA(*FLDBDY) option on the CRTJRN or CHGJRN commands to activate Auditable JMD. You learned how to use the Null value indicators to determine exactly which field in the record was changed, and know the new value for this column. When you have determined if you have good candidate journals for Auditable JMD, educate your auditors, give Auditable JMD a test drive, and activate it on your production system journals.
Special Notices
The material included in this document is in DRAFT form and is provided 'as is' without warranty of any kind. IBM is not responsible for the accuracy or completeness of the material, and may update the document at any time. The final, published document may not include any, or all, of the material included herein. Client assumes all risks associated with Client's use of this document.