f



commitment control - how introduce gradually

Is it possible to gradually introduce commitment control into an applicatio=
n? 90% of the code uses SQL for database updates. The other 10% is record i=
/o.=20

All the RPG SQL is now compiled with COMMIT(*NONE). If I recompile one RPG =
SQL at at time with COMMIT(*CHG) will there be problems in the application?=
  All the RPG programs are ACTGRP(*NEW).  The system will automatically com=
mit database changes when an ACTGRP(*NEW) program exits?

Most of the work in the application is done in a service program. If I firs=
t compile all the *PGM objects as COMMIT(*CHG) will the service program pro=
cedures function the same?  Is my only concern record locks held by the *PG=
M objects?=20

thanks,

0
Steve
6/4/2016 10:46:25 PM
comp.sys.ibm.as400.misc 9219 articles. 4 followers. Post Follow

8 Replies
782 Views

Similar Articles

[PageSpeed] 14

Hi Steve,

First of all:
- not using commitment controll for SQL updates is not state of the art and 
is only working correct at random in most applications!!!
-- records could have changed between read (not using cursors) and write.
-- records could stay locked after read operations (using cursors).
-- reports could show inconsistent results during write activities (missing 
or doubled quantities during stock exchange operations, for instance)

following some inline comments, followed by some further hints.

"Steve Richter" <StephenRichter@gmail.com> schrieb im Newsbeitrag 
news:6b426d85-c30a-4a23-9ce8-8583d748757c@googlegroups.com...
> Is it possible to gradually introduce commitment control into an 
> application? 90% of the code uses SQL for database updates. The other 10% 
> is record i/o.

yes, this is possible, but recompile without changes is not enough and not 
the way to go!

>
> All the RPG SQL is now compiled with COMMIT(*NONE). If I recompile one RPG 
> SQL at at time with COMMIT(*CHG) will there be problems in the 
> application?

yes, because you have no control over locks and making changes persistant.

> All the RPG programs are ACTGRP(*NEW).  The system will automatically 
> commit database changes when an ACTGRP(*NEW) program exits?

yes, because there is an idotical default OPTION(*NORMAL) for RCLACTGRP.

>
> Most of the work in the application is done in a service program. If I 
> first compile all the *PGM objects as COMMIT(*CHG) will the service 
> program procedures function the same?  Is my only concern record locks 
> held by the *PGM objects?

Commit could be defined (using SQL) at statement (with clause) and at module 
level (this sets the default for all statements without with clause), so 
changing the commit for one module, does not influence other modules.

>
> thanks,
>

Firts of all you would have to define your transactions.
a transaction could be simply one record (e.g. an article).
a transaction could be few records (e.g. two records of a stock echachange)
a transaction could consist of multiple records (e.ge. a complete order)
in a well modularized world, there will be a programm, controlling the 
transaction (the commit master) and other helper programms (or SRVPGMS) 
(commit slaves)
the commit slaves are simply compiled with an appropriate commit level 
(mostly the default is a good choice) and they will not issue a commit or 
rollback.
The commit master lets his helpers do the work and if all is successfull it 
will issue commit or otherwise rollback (it's very easy).

In your case, first step would be to stop immediately writing erronous 
programms without commitment controll!!!

then define the commit masters, changing this PGMS/SRVPGMS to commit(*CHG) 
and as they don't make updates themselves this will have no influence (it 
will stay buggy as before!!!). Let the commit master controll success and 
issue commit or rollback.

Next step would be to change the helpers (be sure that nobody is using them 
without controlling the transaction!!!) a simple recompile will do.

If all programms are corrected, you would have the ability to get consistent 
reports during change activities (here you would need commit level read 
committed) and you would have eliminated locked records of cursors and 
changed records for read operations. It would be a good idea to get some 
additional education or assistance by somebody who has done this before (not 
talking of features he/she doesn't use him/herself!!!)

Dieter Bender
 

0
Dieter
6/5/2016 8:17:02 AM
On Saturday, June 4, 2016 at 6:46:26 PM UTC-4, Steve Richter wrote:
> Is it possible to gradually introduce commitment control into an applicat=
ion?=20

Steve,
Read Dieter's reply.  Then read it again.  He has a lot of really good advi=
ce.  The problem, if you can call it that, is that his advice makes way mor=
e sense to those of us who have implemented CC rather than those who are tr=
ying to learn about it.  :-/ =20

The one thing I might be able to add is this: Why do you want to add CC now=
?  That's not sarcasm; the point of CC is to ensure atomicity of transactio=
ns.  In particular, what follows addresses this:

> If I recompile one RPG SQL at at time with COMMIT(*CHG) will there be pro=
blems in the application?

It's sort of like asking this: 'If I add a SELECT COUNT(*) FROM SYSTABLES i=
nto all of my RPG programs will I be using SQL?'  Well, yes, I suppose, tec=
hnically you will.  You won't be doing any real work with it though.

Commitment control isn't something that can be added on after the fact, any=
 more than 'using SQL' can.  By simply flipping to COMMIT(*CHG) you haven't=
 done anything at all to define what a transaction is.  CC is all about tra=
nsactions.

Here is what a transaction is not.  I have an end of day cash receipts proc=
ess which updates a half dozen tables.  The input is the daily cash receipt=
s transaction table; one row for each check coming in.  If I were to turn o=
n CC for this without touching the code at all, CC would treat the entire d=
ay's run as a single transaction - and that's wrong.  If I had a thousand c=
hecks come in, and each check did an average of 10 I/O operations, that wou=
ld mean a commit cycle of 10k records.  That makes no sense in terms of wha=
t the business thinks a transaction is.  Am I really going to back out my b=
iggest customer's checks because a different customer has an issue with his=
?  No.

A transaction in this example is all of the I/O that relates to processing =
a hsingle check.  Which means that my code needs to change to establist a c=
ommit cycle as I read an input check, and execute a commit or rollback afte=
r the last I/O is finished for that check.  Which means I need to keep trac=
k of whether each and every I/O operation did what I expect. =20

You can see that in order to actually use commitment control, I need to ana=
lyse what a logical transaction is, change the code to track I/O issues as =
well as issues where the program decides business rules might be violated, =
and further change the code to wrap all of the I/O inside a commitment cycl=
e - all for a single transaction, not for a day's batch of transactions.

So.  Why do you want to use CC now?  What are you hoping to accomplish, bus=
iness-wise?

  --buck
0
Buck
6/5/2016 2:08:11 PM
On Sunday, June 5, 2016 at 4:17:06 AM UTC-4, Dieter Bender wrote:
> Hi Steve,
> 
> First of all:
> - not using commitment controll for SQL updates is not state of the art and 
> is only working correct at random in most applications!!!
> -- records could have changed between read (not using cursors) and write.
> -- records could stay locked after read operations (using cursors).
> -- reports could show inconsistent results during write activities (missing 
> or doubled quantities during stock exchange operations, for instance)
> 

much appreciated Dieter.  I admit I had never given much thought to commitment control in terms of the application running correctly. Had always thought of it simply as what to do when code bombs halfway thru a transaction. 

thanks
0
Steve
6/5/2016 3:58:49 PM
On Sunday, June 5, 2016 at 10:08:13 AM UTC-4, Buck wrote:
> 
> So.  Why do you want to use CC now?  What are you hoping to accomplish, business-wise?
> 
>   --buck

every 6 weeks or so the WMS I work on has a problem in that a transaction was started but did not complete.  When ther user ships an order I want to turn on commitment control, run the ship transaction, then end commitment control.
0
Steve
6/5/2016 4:04:47 PM
.... using commitment controll from the beginning is very easy, if you use 
SQL for database access:
- don't change the command defaults for the CRTSQLxxx commands
- start creating your database by SQL with create schema
- create all database objects with SQL
Now you are prepared for using commitment controll!!!
- if a transaction is done successfully say commit otherwise rollback. If 
you don't know what's the transaction, issue a commit after every update.
two additional rules:
- if you are using select into and want to go on with an update add an 
isolation clause, something like that:
exec sql
select *
   into :myDS
   from myFile
   where mykey = :myKey
   with *all;
- befor you give back the controll to the user, add a commit operation to 
free all record locks

This is very easy, things get complicated, if you don't use commitment 
controll!!!

D*B

"Buck" <kc2hiz@gmail.com> schrieb im Newsbeitrag 
news:6d1bc122-6d22-4e4e-b1c6-1d7f45c46f39@googlegroups.com...
> On Saturday, June 4, 2016 at 6:46:26 PM UTC-4, Steve Richter wrote:
>> Is it possible to gradually introduce commitment control into an 
>> application?
>
> Steve,
> Read Dieter's reply.  Then read it again.  He has a lot of really good 
> advice.  The problem, if you can call it that, is that his advice makes 
> way more sense to those of us who have implemented CC rather than those 
> who are trying to learn about it.  :-/
>
> The one thing I might be able to add is this: Why do you want to add CC 
> now?  That's not sarcasm; the point of CC is to ensure atomicity of 
> transactions.  In particular, what follows addresses this:
>
>> If I recompile one RPG SQL at at time with COMMIT(*CHG) will there be 
>> problems in the application?
>
> It's sort of like asking this: 'If I add a SELECT COUNT(*) FROM SYSTABLES 
> into all of my RPG programs will I be using SQL?'  Well, yes, I suppose, 
> technically you will.  You won't be doing any real work with it though.
>
> Commitment control isn't something that can be added on after the fact, 
> any more than 'using SQL' can.  By simply flipping to COMMIT(*CHG) you 
> haven't done anything at all to define what a transaction is.  CC is all 
> about transactions.
>
> Here is what a transaction is not.  I have an end of day cash receipts 
> process which updates a half dozen tables.  The input is the daily cash 
> receipts transaction table; one row for each check coming in.  If I were 
> to turn on CC for this without touching the code at all, CC would treat 
> the entire day's run as a single transaction - and that's wrong.  If I had 
> a thousand checks come in, and each check did an average of 10 I/O 
> operations, that would mean a commit cycle of 10k records.  That makes no 
> sense in terms of what the business thinks a transaction is.  Am I really 
> going to back out my biggest customer's checks because a different 
> customer has an issue with his?  No.
>
> A transaction in this example is all of the I/O that relates to processing 
> a hsingle check.  Which means that my code needs to change to establist a 
> commit cycle as I read an input check, and execute a commit or rollback 
> after the last I/O is finished for that check.  Which means I need to keep 
> track of whether each and every I/O operation did what I expect.
>
> You can see that in order to actually use commitment control, I need to 
> analyse what a logical transaction is, change the code to track I/O issues 
> as well as issues where the program decides business rules might be 
> violated, and further change the code to wrap all of the I/O inside a 
> commitment cycle - all for a single transaction, not for a day's batch of 
> transactions.
>
> So.  Why do you want to use CC now?  What are you hoping to accomplish, 
> business-wise?
>
>  --buck 

0
Dieter
6/5/2016 4:08:35 PM
> much appreciated Dieter.  I admit I had never given much thought to 
> commitment control in terms of the application running correctly. Had 
> always thought of it simply as what to do when code bombs halfway thru a 
> transaction.
>

.... one of the main problems are concurrent record locks. An application 
could not go on, because a record is locked by an other user and aborts with 
a timeout. Using comit, you simply issue a rollback and you could try the 
transaction once more later on.

In your example it would be helpfull to bring some more details, what 
problem is occuring, is one programm related, or more than one...

Dieter
 

0
Dieter
6/5/2016 4:16:06 PM
On Sunday, June 5, 2016 at 4:17:06 AM UTC-4, Dieter Bender wrote:
> >
> > All the RPG SQL is now compiled with COMMIT(*NONE). If I recompile one RPG 
> > SQL at at time with COMMIT(*CHG) will there be problems in the 
> > application?
> 
> yes, because you have no control over locks and making changes persistant.
> 

the gradual approach did not work at all.  Records locked in a job by a COMMIT(*CHG) module cannot be read and locked in the same job by a COMMIT(*NONE) module.








0
Steve
6/7/2016 12:05:44 PM
.... looks like a problem with your ACTGRP design. Record locks are owned by 
the AGTGRP and within the same ACTGRP you don't need the same record locked 
a second time, the conflict is coming up with two modules, running in 
diffrent ACTGRPs.

D*B

"Steve Richter" <StephenRichter@gmail.com> schrieb im Newsbeitrag 
news:ac6533bc-9854-4e2a-80f6-38125e400a74@googlegroups.com...
> On Sunday, June 5, 2016 at 4:17:06 AM UTC-4, Dieter Bender wrote:
>> >
>> > All the RPG SQL is now compiled with COMMIT(*NONE). If I recompile one 
>> > RPG
>> > SQL at at time with COMMIT(*CHG) will there be problems in the
>> > application?
>>
>> yes, because you have no control over locks and making changes 
>> persistant.
>>
>
> the gradual approach did not work at all.  Records locked in a job by a 
> COMMIT(*CHG) module cannot be read and locked in the same job by a 
> COMMIT(*NONE) module.
>
>
>
>
>
>
>
> 
0
Dieter
6/7/2016 3:43:00 PM
Reply: