• 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
    ARCAD Software

    Embrace VS Code for IBM i Development

    The IBM i development landscape is evolving with modern tools that enhance efficiency and collaboration. Ready to make the move to VS Code for IBM i?

    Join us for this webinar where we’ll showcase how VS Code can serve as a powerful editor for native IBM i code and explore the essential extensions that make it possible.

    In this session, you’ll discover:

    • How ARCAD’s integration with VS Code provides deep metadata insights, allowing developers to assess the impact of their changes upfront.
    • The role of Git in enabling seamless collaboration between developers using tools like SEU, RDi, and VS Code.
    • Powerful extensions for code quality, security, impact analysis, smart build, and automated RPG conversion to Free Form.
    • How non-IBM i developers can now contribute to IBM i projects without prior knowledge of its specifics, while ensuring full control over their changes.

    The future of IBM i development is here. Let ARCAD be your guide!

    Watch Now

    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

  • 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
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

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