Be Committed to using Commit
A few weeks ago a call came in from a customer. A SQL delete statement had been executed on one of the tables on the production system.
Immediately after having pressed enter, the error was detected.
In order to determine what needed to be done to repair the damage, I did ask if commit control was used.
As the answer was no and since the table did contain more or less static data, a quick restore from the backup did repair the damage.
This made me wonder why commitment control was not used.
In order to use commit control a table needs be journaled. Journaling was in place, so I decided to have a look in IBM i Access Client Solution (ACS) Run SQL scripts, to see where not using commit is set.
Looking at all the options made me realise that the number of choices might be the cause of not using it.
It is there that my journey started.
As mentioned before journaling needs to be in place when using commit control.
In order to check if a table is being journaled the easiest place to check this, is ACS Schemas.
Note: In order to list a schema it must be added once using the Include... option by using a right-click on Schemas.
After selecting the option Journaling a window is shown.
The status Currently journaled is the one you are after. With or without using commit control, journaling adds an extra layer of control and can be of great help, to meet your RPO requirements, in case of a disaster.
With journaling in place, it is now time to have a look at ACS Run SQL Scripts. Within a JDBC Configuration, the level of commit control can be set. Below is an image of the place where this configuration can be accessed:
The commit control setting is controlled in the JDBC Configuration by specifying the Isolation level.
Below is a list of the possible values:
- Uncommitted read (*UR & *CHG)
- Cursor stability (*CS)
- Read stability (*RS & ALL)
- Repeatable rea (*RR)
For an overview of the different Isolation levels, please have a look at IBMs documentation, where the following table can be found:
Based on this table we can conclude that Uncommitted read is the closest to the default JDBC isolation level value No commit (*NC and *NONE).
My guess is, that it is what the majority are using.
It does seem logical that whenever a record has a change pending, it is blocked from being updated again.
Along this line, an updated record but still pending, can also not be read with an Isolation level other than No commit (*NC & *NONE) and Uncommitted read (*UR and *CHG).
In order to use a JDBC configuration, making use of commit control, the existing default configuration can be copied.
Select the correct Isolation level accordingly. There is no need to make any additional changes.
When done, press the Save button.
Switching between the JDBC configurations can be done as shown below:
The change of the JDBC configuration is logged in the Messages panel of Run SQL Script:
If a table is not being journaled, the use of the Uncommitted Read isolation level (UR or CHG) prevents the operation from proceeding.
Whenever you want to change a table not being journaled a message is displayed:
In this way you are made aware of the fact that this table is not being journaled, which seems wrong for production tables if you ask me.
If you want to continue making changes, all you have to do is switch back to the default JDBC configuration.
When changing a table with Isolation level set to Uncommitted read (*UR & *CHG), closing the window without having executed the Rollback or Commit command, the window shown below will be presented:
This will help you to remember you having pending changes.
Using commit control in combination with ACS Run SQL Scripts, just adds another option to the choices of how you want to do your job.
When working with databases, I think we have nearly all updated a production table. Having commit control as an angle sitting on your shoulder, allowing you to rollback updates, feels comfortable.
Yet an extra level of control, or call it security. If it helps, why not?