Submitted by Syd Nicholson on Tue, 07/19/2011 - 00:00
Forums
IceBreak
Hi Niels,
 
A little feedback for you.
 
Remember I was having strange problems with commitment control, in which
the commitment control environment was already active, even though I
have not started it. Well the problem is explained.
 
It is SQL. If one does not set the commitment control environment as the
very first SQL instruction, then SQL automatically starts commitment
control. Warning messages are often sent if files are not journaled
 
If one does not wish to use Cmt ctl in an an SQL program one should use
 
Set Option Commit = *None
 
as the very first SQL instruction
 
For some reason the pre-compiler directive
 
options="Commit(*NONE)"
 
doesn't seem to work - it is ignored
 
For example - my service program jcCusSupH.sqlrpgle - has the
Commit(*none) precompiler directive. - but - When I look at the service
program on the system, the cmt ctl value for the compiled module says
'*CHG'. This program has been compiled with:
 
<%@ language="SQLRPGLE" pgmtype="NOASPSRV" options="COMMIT(*NONE)
CLOSQLCSR(*ENDMOD) DBGVIEW(*SOURCE) USRPRF(*OWNER) DYNUSRPRF(*OWNER)"
modopt="DBGVIEW(*LIST)" pgmopt="MODULE(*SRVPGM) EXPORT(*SRCFILE)
SRCFILE(BJCS_S/QSRVSRC) SRCMBR(*SRVPGM) ACTGRP(*CALLER)
BNDDIR(JCCUSSUPH)" %>
 
 
The ignore of the Commit(*None)  certainly explains the strange Cmt Ctl
behavior of programs in the IceBreak server, and why some files are kept
open permanently.
 
Is there a bug in your IceBreak precompiler, or have I done something
wrong???
 
Regards
Syd

Niels Liisberg

Tue, 07/19/2011 - 00:00

Permalink

Hi Syd;
 
Thanx for the feedback. Actually it is because you have to use: SQLOPT="COMMIT(*NONE)" … 
 
Icebreak use:
 
SQLOPT to send to the CRTSQLRPGI  precompiler
MODOPT to send to the  CRTRPGMOD compiler
PGMOPT to send to CRTPGM or CRTSRVPGM command
 
 
The "options" was a super early feature before we had support for SQL, modules and service programs
 
Best regards
Niels Liisberg

Thanks Niels,

compile looks correct now.

I don't have any experience of using commitment control with SQL, can I pick your brains

In a program that uses a mixture of SQL and Native RPG files:

1. Does the RPG commit/rolbk also apply to SQL transactions
2. Vice versa - does the SQL commit.rollback apply to RPG native file transactions
 

I suspect the answer to both of these is - Yes.

In RPG the F spec has a the COMMIT(ind) keyword. Setting the value for ind determines whether or not a file is opened under commitment control. What is the SQL equivalent?
 

I understand that I should only use the "Set Option Commit = " once only. So:

1. Should I set up to do this on every transaction between browser and server?
2. Should I set the value at the beginning of the session and how would it be affected by logging on/off?
3. If a second browser window is opened in the session what is the SQL commimitment status?
 

Many thanks
Syd

Hi Syd;

As expected, the answers to your first two questions is yes: the RPG commit/rolbk does apply to SQL transactions and vice versa.


SQL has a concept of – "Unit of Work" - (the transaction). You can only set that behavior at compile time. SQL has the following types:

*CHG
*ALL
*CS
*NONE
*RR
*UR
*RS
*NC

And let me explain a few: 

*CS is "Cursor Stable" - is much like "normal" RPG behavior - which means: read records/row are locked until you update, delete or insert or you remove the lock with a "ROLLBACK" or "COMMIT"

*ALL is ALL your I/O are locked until you do a ROLLBACK or a COMMIT. – this includes any input operations. And you will have locks on multiple rows/records if you perform a number of fetches before you commit. Note that a close cursor will not release the locks – you ned to explictly issue a COMMIT or ROLLBACK to release locks!! This, however, is extremely use full if you think of a bank account system where you transfer an amount from one account (record/row) to an other:

You fetch the first account (now it is locked) subtract the amount from the total and update the row - (it is still locked) . Then fetch the other account (now this is also locked) an add the amount to the total and do the update (both rows are still locked) . Finally you COMMIT an both rows will be available to "the rest of the world" with new totals. You have transaction integrity – a complete "unit of work".

*NONE is no locks, journal nor commitment control what so ever. If you rethink the above example that without commitment control, the scenario will be rather dangeling, if for some reason the program abb-ends between the two rows – the amount will be lost.

*RR,*UR,*RS,*NC are variations where where you can have no locks on reads, locks on data managing operations like "drop table"  or "create table" etc.


Regards
Niels