Stored procedures behave differently regarding committing, depend ing on how they are called

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.