Much has already been written about IBM i journaling, Database journaling is as old as IBM i. In the SQL world, the setup of journaling is a no-brainer. In the world of Data Description Specifications (DDS), journaling is in many cases put out of play. Not often, it is not even considered or seen as extra performance burden. The question if this is still valid today needs to be asked, after all our IT world is changing, so once in a while a reevaluated of the situation, is good practice if you ask me.
When working with SQL we create a Collection, for that we can use the SQL statement below:
create collection thewhyofjournaling;
SQL names are not limited 10 characters, IBM i names libraries are so when looking at the content of the collection in IBM i Access Client Solutions (ACS) => Database => Schemas we see:
Please be aware that by in order to see additional Schemas, you need to add them yourself.
As we can see the name of the IBM i library created for the new collection is “THEWH00001”. When take a look inside this library, we see:
The objects needed for journaling were created by SQL.
When we create a file based on the file QCUSTCDT in the Library QIWS available on every IBM i LPAR, we reverse engineer the table:
After a slight modification of the script, we have the following result:
As you may know SQL has the option reuse deleted records switched on by default. DSS has not, so if you create a physical file and if you want to reuse the space of deleted records, you have to overrule the default parameter. This link “Reusing deleted records” will give some background information about this subject.
When running this script, we can find the table pop up in the Schema, we created earlier:
When the option “journaling” is taken, against this table we see a screen popping up:
So by default, without having to specify anything, this file is being journaled with the before and the after images. When looking at the library, you understand how that is being achieved, for this 5250 emulation is coming to the rescue:
This is part of a functionality in IBM i called “Automatically starting journaling”. If you open that link, you will find the command “Start Journal Library (STRJRNLIB)”. So if you are not ready yet to start defining your database with SQL, but you do want to journal to be automated, this is the command to consider using yourself.
Although some may doubt the use of journaling if commit control is not used, there are occasions when journaling has very good use. The best example is to retrieve information about records being changed. We recently were asked to tell which user did change a field in a record, only because journaling was around we could.
For more information about commit control, you best start with the link: “Commitment control concepts”.
Another aspect of journaling is the cleanup of the journal receivers. When we look at what was created by the SQL command “Create collection” we need to have a look at the attributes of the journal created:
When looking at the those properties, we see:
The option “Delete receivers when no longer needed” value set to “No”, does look much promising when thinking of disk space.
A simple prompt of the command CHGJRN will clear this matter:
When viewing the help text for the “Delete receivers” option:
When viewing the help text for both the delay time fields:
The maximum delay time possible is 1440 minutes, which is 24 hours. As most IBM i shops run a backup every day, we can rest assured that every journal receiver will only be deleted after it has been saved.
In case we want to be 100 % sure a journal receiver is never deleted premature, we need to give the help text have a better look. The parameter “DLTRCV” where the Exit Point “QIBM_QJO_DLT_JRNRCV” is mentioned. For this, we need to write code to tailor our need.
IBM i was designed with journaling in mind, so do not think that your performance will degrade drastic. Be aware however of the impact on disk space of the journal receivers. So when starting with journaling, it is something you need to monitor closely.
I see journaling, be it auditing of for data changes to an object in a library or Integrated File System, as an insurance. The saying is “it's too late to lock the stable door after the horse has bolted”. So with journaling, you are better prepared to deal with unexpected situations. “Better safe than sorry” is another valid saying spot on for having journaling implemented.
Allow me to give me you one last example. Recently we were called by a customer informing us that a developer did execute his delete SQL script against the production file. A whole bunch of records were deleted. With the journal, we were able to recover the situation.