Re: Stored procedures behave differently regarding committing, depend ing on how they are called -- MIDRANGE-L

Martin,

I know that for data access from java code, there is a parameter on the
connection called auto commit.

Could it be that the default for this is set to true in your case, because I
can remember having to set this to false so that I could could control when
the commits are done.

Are you running this stored procedure from java via a remote database
connection or from RPG?

cheers
Colin.W


On 12/09/06, McCallion, Martin <martin.mccallion@xxxxxxxxx> wrote:


Hi list,

This is one of my very infrequent returns to the list, when I have a
problem
that the web (including the archives) can't resolve.  More a nagging
curiosity than a problem, in this case.

Consider a stored procedure, written in SQL, which modifies some data in a
table.  It does not include an SQL COMMIT statement.  When created, it
exists as a program object, so you can call it from the command line.  If
you do so, you can then see the modified data.  However, if the job where
the CALL was executed ends (or if you issue a ROLLBACK command in that
job)
the changed data is rolled back.

Logical enough: we are running under commitment control, and haven't
committed the data.  However, if you create a source member that contains
an
SQL CALL statement, calling the same stored procedure, and you execute
that
SQL using the RUNSQLSTM command, the data is modified _and committed_.

It's clear that the RUNSQLSTM command is effectively doing a COMMIT
somewhere.  This turns out to be governed by the setting of its COMMIT
parameter, which is not surprising, but is undocumented, as far as I can
see.  The help text for that parameter begins "Specifies whether SQL
statements are run under commitment control", but goes on to say nothing
about how it affects the actual committing of transactions.  From my own
tests it would seem that *NONE causes it to behave like the direct call of
the program, while *CHG (the default) and *ALL appear to behave
identically.

Clearly the solution is for me to put a COMMIT statement in the SQL for my
stored procedure.  This is better because it is explicit, and removes the
tiny potential danger of somebody changing the command default on the
RUNSQLSTM command.  But I can't help but wonder whether I'm missing
something in all this.  Do IBM actually document this behaviour somewhere?
I certainly haven't been able to find it in any of the usual places, if
so.

Cheers,

Martin.

--
Martin McCallion
Senior Technical Consultant
Misys Wholesale Banking Systems
1 St George's Road, London, SW19 4DR, UK
T +44 (0)20 8486 1951
F +44 (0) 20 8947 3373
martin.mccallion@xxxxxxxxx
www.misys.com

This email and any attachments have been scanned for known viruses using
multiple scanners. We believe that this email and any attachments are
virus
free, however the recipient must take full responsibility for virus
checking. This email message is intended for the named recipient only. It
may be privileged and/or confidential. If you are not the intended named
recipient of this email then you should not copy it or use it for any
purpose, nor disclose its contents to any other person. You should contact
the Misys Banking and Securities Division as shown below so that we can
take
appropriate action at no cost to yourself.

Misys Banking and Securities Division, 1 St George's Road, Wimbledon,
London, SW19 4DR, England. Email: banking.postmaster@xxxxxxxxxx Tel: +44
(0)
20 8879 1188 Fax: +44 (0) 20 8947 3373
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.