Optimizing journal bundle sizes in commitment-control applications – The hard way or the easy way

I was reviewing the wait signature of an IBM i batch application the other day using IBM iDoctor, and noticed that the job spent a significant portion of its time in Journal Wait status.

Journal waits happen when an application is delayed waiting for journal bundles to get written out to disk.   Sometimes that is a symptom of a very busy disk subsystem.  That wasn’t the case here.

This particular batch job was running on a lightly-loaded test system, so I immediately suspected commitment-control.

The associated journal was set up for caching (CHGJRN command), so in a job that doesn’t use commitment control, journal management will calculate an efficient journal bundle size, and cache journal entries until that bundle size is reached.  Journal management then pushes that bundle out to disk in a single operation.

It is much faster and more efficient to do a single large write to disk than to do a lot of small writes.  Journal caching is “record blocking for journal entries”.

Commitment control breaks journal caching

Commitment control, by default, and intentionally, breaks journal caching.

COMMIT operations, by default, cause journal management to flush journal entries to disk, regardless of caching or bundle size.  As a result, it is not uncommon, especially in high-volume batch programs that use commitment control, to see a lot of cumulative journal waits in iDoctor, unless the developer understood the impact of COMMITing too frequently and was careful to ensure an adequate journal bundle size.

The traditional fix for this problem is to have the developer to change the application to COMMIT less frequently.  Developers sometimes don’t like to hear that, since it can cut into their long lunches and involve tedious things like change control forms, specifications documents, approvals, funds authorizations – oh – and some small amount of actual coding and testing.

It can also requires some repetitive testing to determine the optimal bundle size (though I have a trick I’ve been using and it works pretty well – ask me nicely and I’ll tell you about it in a future post).

I know from experience that the optimal bundle size has grown larger over the years, so hard-coding is out.    If you only COMMIT every 20th transaction today, after your next upgrade the optimal bundle size may take 40 transactions.  So then you’ll start to see journal waits again, and you have to do some testing to figure out the optimal bundle size.

Soft Commit – journal caching for batch programs

Wouldn’t it be nice if you could choose to turn on journal caching for batch commitment control applications?  Maybe not on a system-wide basis.  You might have some critical applications that just must be allowed to flush every journal entry to disk for replication or audit purposes, but for the majority of applications, caching would be just the ticket.

And you can.  Starting in V5R4, IBM offers “soft commit” support using the environment variable QIBM_TN_COMMIT_DURABLE.  By turning OFF Durable commits, you ENABLE soft commits.  You can set this environment variable at either the system level so it applies to all jobs on the system, or at the job level.

Just a note: if you set an environment variable at the *JOB level, it doesn’t get copied down to child jobs submitted by the parent job by default.  If your parent job submits child jobs, you’ll want to specify CPYENVVAR(*YES) on each SBMJOB or add the ADDENVVAR command to each job in the job stream.


In a nutshell, “soft commit” allows batch jobs to use journal caching.  That’s it.  Turn on soft commit, and then COMMIT as early and often as you like.  Journal management will ignore those CM journal entries, and wait until a healthy bundle size has been reached, and then flush then entire bundle to disk in one large, efficient operation.  Check out the IBM article below.

Journal caching is also required for soft commit support.  This means installing option 42 of the IBM i Operating system “HA Journal Performance” in V6R1, and using CHGJRN JRNCACHE(*YES) for each journal that you want to allow to use caching.

Read more on Journal Performance, Soft Commit, and Journal Caching

Soft Commit: Worth a Try on IBM i5/OS V5R4


Striving for Optimal Journal Performance on DB2 Universal Database for iSeries


Journaling – Journal Receiver Diet Tip 1: Eliminating Open and Close Journal Entries


Journaling – Journal Receiver Diet tip 2: Consider using skinny headers


Journal Caching: Understanding the Risk of Data Loss


Posted in DB2 on i, IBM i, Journaling, Performance, Performance Tagged with: , , , , ,

Setting the Session Date Format in IBM DB2 for i (and AS/400, and iSeries)

Apparently in DB2 for Linux/Unix/Windows, the date format is a system-wide setting, and is not controlled at the session level. I’m not a DB2 LUW expert, so don’t take my word on that!

IBM DB2 for i offers more flexibility.

In DB2 for i, you can easily control the date format for each connection to the database, and you can even change it from one format to a different one between subsequent queries in the same session.

For example, I recently had a question about this SQL query:

SELECT someDateColumn FROM someTable

Which returned “01/12/09” (mm/dd/yy).

But the user wanted to see the date in mm/dd/ccyy format (01/12/2009).

First, it is important to understand that the session that this query ran in is configured for *MDY date format. The century information is still there, you just can’t see it on screen or paper when the date format is set to *MDY.

DB2 for i DATE data type

DB2 for i DATE data types (“L” in DDS) are stored internally as a serial number (“Lillian” date format, if you are interested, is a numeric value representing the number of days since 14 October 1582). Lillian dates are compact, and make date math and sorting easy, but they aren’t very user-friendly, so DB2 automatically converts DATE columns to the session date format at presentation time.

Session Date Format

When you connect to DB2 for i to run a query, one of the parameters that you supply is the DATE FORMAT to use for that SESSION. If you don’t specify a date format, then the system makes a default decision for you based on system values, the settings of the current job, the setting of the database server job that you are attached to, or other mechanisms depending on the connection method.

How you set the date format varies depending on the interface that you are using. For example in a JDBC session, you can control this using the “date format” connection property, plus a little optional help from the “date separator” property. ODBC uses a similar method. Most green-screen commands and utilities like STRSQL and CRTSQLRPGI have a DATFMT and DATESEP parameter.

JDBC connection string example

Connection c = DriverManager.getConnection(
"jdbc:as400://myAS400;date format=USA;errors=full",

Notice the “date format=USA” parameter.

CRTSQLRPGI command example


The following date formats are supported:

yy/dd (*JUL)
mm/dd/yy (*MDY)
dd/mm/yy (*DMY)
yy/mm/dd (*YMD)
mm/dd/yyyy (*USA)
yyyy-mm-dd (*ISO)
dd.mm.yyyy (*EUR)
yyyy-mm-dd (*JIS)

Documentation for your particular database interface (JDBC, ODBC, OLDEB, ADO.NET, STRSQL, embedded SQL in RPG/COBOL/C) will show you how to set the date format and separator.

If you are using the green-screen STRSQL command, you can prompt the STRSQL command and specify the DATFMT and DATSEP parameters, or once you have started the STRSQL utility, you can use F13, Option 1 to change the session parameters. Interactive help is available.

Date functions

DB2 on i offers some very nice date functions for extracting the various components out of date, time, and timestamp fields without having to resort to string parsing (which is a bad coding practice anyway, since a change in date format can break code that uses substring techniques to parse dates).

Instead, use the EXTRACT() function:


or the individual YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND(), or MICROSECOND() functions.

And on the subject of date formats, the CHAR() function can convert a date field back to character format, and even allows you to specify the date format on the fly in your query without changing the session date format. If you don’t specify it, you get the session format. For example, this will display the date in USA format (mm/dd/ccyy), even though the session date format is set to *ISO (ccyy-mm-dd):

SELECT char(date(‘2009-01-12’),USA) FROM sysibm/sysdummy1

The DATE() function converts the literal ‘2009-01-12’ to a DATE type, and the CHAR() function converts it back to a character literal, but this time forces it into USA format (mm/dd/ccyy).

The DB2 for i SQL Programmer’s Guide and Reference topics in the IBM Information Center have all of the details on date formats, functions, and just about anything else you need to know about IBM i SQL:

The IBM i Access for Windows Programmer’s Toolbox (an optionally installable component of IBM i Access) contains documentation and examples of how to set and change the session date formats for ODBC, OLE DB, and ADO.NET connections, plus links to documentation for JDBC.

If you need additional assistance, please Contact Us.

Posted in DB2 on i, DB2 on i ADO.NET, DB2 on i DRDA, DB2 on i Embedded SQL, DB2 on i JDBC, DB2 on I ODBC, DB2 on i OLEDB, IBM i, IBM i Access For Windows

Using IBM i Access for Windows with Windows 7 and Windows 2008

Only certain versions of IBM i Access for Windows (predecessor products include IBM iSeries Access for Windows and IBM AS/400 Client Access) are supported on Windows 7 and Windows 2008, and specific IBM Service Pack and Microsoft fixes required.

There are a long list of known incompatibilities and problems with older unsupported releases on Windows 7 and Windows 2008, and the solution to many IBM i Access problems on Windows 7 and 2008 is “Upgrade to a supported release”.

The follow up questions are often “Where can I find information about supported releases?”, and then “Where can I download the latest version?”

The links at the bottom of this article answer both questions.

Note that since IBM i Access for Windows is licensed software, there is no “public” download location. If you are an IBM customer set up for Entitled Software Support (ESS), or for Passport Advantage, you can download the product (or product refreshes) at no cost.

If you aren’t signed up for ESS (and you probably should!), contact IBM Customer Support or your IBM Business Partner for help. If all else fails, you can order physical media shipped to you for a small charge. See the ordering link below for instructions.

The main product web site is http://www-03.ibm.com/systems/power/software/i/access/

In this article, I’ve provided all of the specific links you need, in one place, to help you select the appropriate version, install, upgrade, select the correct service packs, and order the correct products (including download instructions).

Finally, note that documentation related to Windows 7 generally applies to Windows 2008, too – even if it is not specifically mentioned.

Supported IBM i Access for Windows / Windows OS combinations:


Supported IBM i Access for Windows / host OS release combinations:


IBM i Access for Windows Service Packs:


How to obtain newer versions of IBM i Access for Windows, usually at no charge:


Links to IBM i Access Informational APARs, including APARs containing Windows OS specific information:


If you need help with a specific problem, Contact Us.

Posted in IBM i, IBM i Access For Windows, Microsoft Windows, Microsoft Windows 2008, Microsoft Windows 7