• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Turning Off ODBC Journaling Is Not a Good Idea

    June 22, 2005 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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    LaserVault Boosts Compliance Efforts with New Audit Log Original Looks for Performance Problems with New TestLOAD Tool

    Leave a Reply Cancel reply

Volume 5, Number 25 -- June 22, 2005
THIS ISSUE
SPONSORED BY:

Advanced Systems Concepts
WorksRight Software
Patrick Townsend & Associates

Table of Contents

  • Execute SQL Statements on DB2 UDB for Windows from the iSeries
  • Case-Insensitive Sorting and Record Selection with Query/400
  • Turning Off ODBC Journaling Is Not a Good Idea

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle