Turning Off ODBC Journaling Is Not a Good Idea

Hey, Joe:

After reading your column on "Curing the ODBC Blues," I came to the same conclusion that you did regarding how to avoid the SQL7008 error: You need to journal your OS/400 files to avoid ODBC update errors. But then someone showed me the ODBC advanced server option in the iSeries Access ODBC driver, where you can set your ODBC drivers' Commit mode to eliminate the need for journaling files updated by ODBC. This seems like a better solution.

--Brock

Here's my take on the situation.

By default, IBM sets iSeries Access for Windows ODBC drivers up with a Commit mode parameter equal to Read uncommitted (*CHG), which requires you to journal all of your target ODBC files. This means that all changed, added, or deleted rows referred to in an ODBC statement are locked until the end of the transaction, and then these transactions are either applied to or rolled back from the journaled OS/400 database as a group. You can change this setting by clicking on the Server tab of your ODBC driver and then selecting the Advanced button on the Server setup screen. On the Advanced Server options screen, if you change the Commit mode parameter to Commit immediate (*NONE), this removes the requirement that any OS/400 files updated through this ODBC driver must be journaled.

While it's true that journaling is not an absolute requirement for ODBC-enabled updates, journaling does enable two critical objectives when you use ODBC to update OS/400 data from a desktop program.

It insures that all ODBC statements are successfully completed via commitment control, maintaining data integrity by making sure all database changes are applied as intended. With commitment control, database changes are grouped together and processed as a single unit of work. If the statement completes successfully, all the changes are applied as a group, insuring that your database is accurately updated from a remote system (or at least as accurate as the SQL statement that produced them). If the statement doesn't successfully complete, the changes are rolled back as a group rather than being partially applied. Journaling is a requirement for commitment control and if journaling is turned off when a remote SQL operation fail, this could corrupt your database by partially applying individual changes that were meant to be applied as a group. So journaling and commitment control are critical for data integrity via ODBC.

To provide an audit trail for any changes that are made through ODBC. One of the nightmares of ODBC is the lack of auditability when someone changes data. As opposed to pre-written application programs, ODBC users have very few limits when updating data. With an ODBC connection and the proper authority, any users can change any fields they are authorized to in any fashion. If someone wants to (or accidentally) changes all your shipping dates to June 11, 2999, he or she can write and execute an ODBC program that does just that. So if you remove the journaling requirement for ODBC-enabled changes, you not only open your database to possible corruption if the connection goes down, you also remove an audit tool that can be used to isolate just when and where the data corruption occurred.

In my opinion, turning off the journaling requirement for ODBC is like watching a bad movie where a mad scientist performs a diabolical experiment that ends in disaster. Just because you can do something doesn't necessarily mean that you should do it. Yes, journaling ODBC changes is a hassle, a headache, and more work than an overworked administrator needs. But if you don't enable journaling for your ODBC connections, you lose two really valuable tools for insuring data integrity and providing an audit trail to determine who corrupted a database and what you should do about it.

Just my .02 and something to think about.

--Joe

RELATED STORY

Curing the ODBC Blues