Without journalling commitment control will not work
Commitment control is native mode starts with the command StrCMtCTRL (that doesn’t seem correct).
Commitment control is less obvious in Sql but one way to set it is with the set commit command.
If either form of commitment control is used and the file isn’t journalled the application may fail or act differently than with journalling.
Phil

and the file isn’t journalled

Of course, if the file isn’t journaled, then the problem in the question goes away!

Tom

OK .. TOM .. when he/she succeeds in using the command ENDJRPF
1. the file will no longer be journalled
2. commitment control will not work
But there are instances where you need to end journalling .. but don’t forget to restart it.
Phil

Yeah, I agree.

What might be missing here is a statement of the business objective. There might be alternatives if we know what needs to be accomplished. What specific actions are intended to be enabled by ending journalling in this case?

Tom

Doesn’t seem like a good thing to do while the applications are running.
I had to end it to make massive data changes to a very large file but that certainly wasn’t done while production was occuring and it included back-ups before and after.

The main thing here is, journalling is a very important tool of db/400 and should be used.
Phil

Here’s the full story. We have a batch process that would update about 120 files. This batch process allocates the files so that updates will be successful, i.e., no user would be locking the files by accessing some other program that opens the file for update. This runs as a night job. Now, we are implementing what we call a 24×7 project. That means the users can now access their programs at any time of the day at any time of the week. Our batch job can no longer allocate those files. We want to implement commitment control so that when one of the files that need updating is locked by some other user’s job or program we can “roll back” changes to files that have been updated and move on to the next group of transactions. Of course, that group of transactions that was rolled back would be “re-processed” on the next run. We then end commitment control after processing all rows of the driver file. However, we find that we cannot end journaling when even a LF of a PF that is being journaled is opened by some other program. We want to end journaling because we think that it is no longer necessary. We just journaled the files for commitment control. These files that are being journaled will continue to be journaled. And a lot of them are also used in other batch jobs. We are afraid the performance of those other batch jobs would be slowed down by the journaling of the files. We’ve tried a lot of things to end journaling. We find that we would have to ask all users who have all files concerned log off. Is that the only solution we have? If IBM improved the ENDJRNPF in V6R1, then they should also have a PTF for V5R4, right?

This batch process allocates the files so that updates will be successful,

I mean allocate with exclusive locks (*EXCL)…

We are afraid the performance of those other batch jobs would be slowed down by the journaling of the files.

Journaling can improve performance in a commitment-control environment.

It gets kind of tricky when access paths are also being journaled. For example, if SQL is supplanting DDS, one issue is that the SQL page sizes are much bigger. That can make a big difference in how many bytes get transferred into a journal receiver.

Lots of factors can contribute.

If IBM improved the ENDJRNPF in V6R1, then they should also have a PTF for V5R4, right?

Maybe. It happens. I wouldn’t expect it for this function, but a number of preparatory PTFs related to journaling were released last week. Those didn’t have a descriptions other than “Incremental enhancement” or “Enablement for Future Enhancement”.

I’ve never checked, but it feels like many of these PTFs back to previous releases come at version boundaries. With i6.1 being followed by 7.1, this is a little unusual. Maybe IBM will PTF this backwards.

AFAIK, the more customers ask for it, the greater the chance of it happening. It obviously depends on whether or not it’s even possible without the i6.1 LIC disk functions.

Tom

If journaling can improve performance for a commitment control environment, we would have no problem with the batch job in which commitment control will be implemented. But what about those other jobs where commitment control will not be used?

Commitment control doesn’t work without journalling. I think you only have to journal after image ..commitment control will force before/after images.

Yes, Journaling will take a little more resources. Make sure that the receivers get rolled.

Your plan is very complicated and complicated systems tend to have higher failure rates.
I wish I could propose an alternative. Are you running mirrored systems?
Phil

I’m not sure if we have mirrored systems. Although I have heard others here talking about a shadow environment. What’s the relevance of mirrored systems?

Your plan is very complicated and complicated systems tend to have higher failure rates.

I agree – I don’t know if this 24×7 concept is prevalent nowadays. What and how much benefit is it to a company? It is indeed a very complicated plan and I am already expecting high failure rates. Or maybe I’m just not that knowledgeable…

In mirrored systems a second 400 is used. The journals from the on-line system are used to keep the off-line system up-to-date. The updating process is delayed while the off-line system is backed-up. I think some of the mirroring software might accomodate your batch jobs running on the back-up system and then pushing it to the on-line system but this is only a guess.
Phil

I have sent an e-mail to Dave Owen (of IBM) and he says there will be no PTF of the ENDJRNPF improvement in V6R1 for V5R4.

I guess there won’t be any solution for this now…not until we get V6R1. Anyway, thanks Tom and Philip for your time…

Allan

But what about those other jobs where commitment control will not be used?

Why would it not be used?

By using a journal, commitment control avoids a requirement to apply updates physically to files until after the instant of commitment is reached. All related uncommitted I/O is done in contiguous entries in the receivers making for good efficiency. Physical table I/O can be handled totally by DB2 after that. SLIC routines can make updates faster than system calls.

Here is some code to play with to see what commitment control can do. (Taken from Vern Hamberg’s test in the midrange.com archives. Modified for more general usage, more specific example, etc.) This creates some test files:

DROP TABLE mylib.filecmt RESTRICT;
CREATE TABLE mylib.filecmt ( field1 INTEGER NOT NULL
                                    CONSTRAINT constraint1 PRIMARY KEY,
                             field2 INTEGER NOT NULL);
DROP TABLE mylib.filejrn RESTRICT;
CREATE TABLE mylib.filejrn ( field1 INTEGER NOT NULL
                                    CONSTRAINT constraint2 PRIMARY KEY,
                             field2 INTEGER NOT NULL);
DROP TABLE mylib.filenone RESTRICT;
CREATE TABLE mylib.filenone ( field1 INTEGER NOT NULL
                                     CONSTRAINT constraint3 PRIMARY KEY,
                              field2 INTEGER NOT NULL);

Create a default QSQJRN journal in mylib before creating the tables. After running the SQL, run ENDJRNPF for the FILENONE table so that two tables are journaled and one is not.

This is basic CL to control the test run:

pgm

   clrpfm    filecmt
   clrpfm    filejrn
   clrpfm    filenone

   strcmtctl lcklvl(*all) cmtscope(*job)
   call      TSTJRNCMT
   endcmtctl

endpgm

The ILE RPG to run the three updates:

     ffilecmt   if a e           k disk    commit prefix(C)
     f                                     rename( filecmt : FILECMTR )
     ffilejrn   if a e           k disk    prefix(J)
     f                                     rename( filejrn : FILEJRNR )
     ffilenone  if a e           k disk    prefix(N)
     f                                     rename( filenone : FILENONER )

     d i               s             10i 0
     d j               s             10i 0
     d k               s             10i 0 inz
     d sTimestamp      s               z
     d eTimestamp      s               z
     d duration        s             15p 6

     dTSTJRNCMT        pr

     dTSTJRNCMT        pi
      /free
       sTimestamp = %timestamp();

       for i = 1 to 100;
          for j = 1 to 1000;
             k += 1;
             cfield1 = k;
             cfield2 = k;
             write filecmtr;
          endfor;
          commit;
       endfor;

       eTimestamp = %timestamp();
       duration = %diff(eTimestamp : sTimestamp : *mseconds) / 1000000;
       dsply ('Commit type: ' + %char(duration) + ' seconds');

       sTimestamp = %timestamp();

       for i = 1 to 100000;
          jfield1 = i;
          jfield2 = i;
          write filejrnr;
       endfor;

       eTimestamp = %timestamp();
       duration = %diff(eTimestamp : sTimestamp : *mseconds) / 1000000;
       dsply ('Journal type: ' + %char(duration) + ' seconds');

       sTimestamp = %timestamp();

       for i = 1 to 100000;
          nfield1 = i;
          nfield2 = i;
          write filenoner;
       endfor;

       eTimestamp = %timestamp();
       duration = %diff(eTimestamp : sTimestamp : *mseconds) / 1000000;
       dsply ('Neither type: ' + %char(duration) + ' seconds');

       *inlr = *on;
      /end-free

The RPG inserts 100000 records into each of the three tables. FILECMT is updated under commitment control, FILEJRN is simply journaled and FILENONE is simply updated.

I ran three tests. Nothing special was done to alter performance — no journal caching, no receivers in a multi-arm dedicated user ASP, etc. The delays that force journal writes synchronously under basic journaling are obvious.

Results:

• DSPLY Commit type: 115.240000 seconds
• DSPLY Journal type: 750.756000 seconds
• DSPLY Neither type: 61.373000 seconds
• DSPLY Commit type: 117.267000 seconds
• DSPLY Journal type: 750.333000 seconds
• DSPLY Neither type: 59.932000 seconds
• DSPLY Commit type: 107.282000 seconds
• DSPLY Journal type: 745.358000 seconds
• DSPLY Neither type: 58.359000 seconds

Performance tuning for journaling could bring the first two types down a bit, but the clear differences won’t disappear. If journaling is important and performance is important, then commitment control becomes more important.

Tom

Tom
Wow–wonder if that improvement holds when multiple files are included in the commitment.
Phil

Sorry, we’re not allowed to accept gifts.