Re: Journaling, SQL updates/inserts/deletes, and COMMIT(*NONE)

[Dan <dan27649@xxxxxxxxx>]

O.k., apparently I completely misunderstood COMMIT(*NONE).  There's no help
on the RUNSQL command on v7r1, so that might have set me straight if it had.

I am *vaguely* recalling what our other shop did.  We compiled SQL
procedures that performed inserts, updates, and deletes, and called those
procedures in a CL program.  If the CL program did not have a COMMIT
command after those called SQL procedures, the changes would be rolled back
when the job calling it ended.  So, I suppose I translated that a missing
COMMIT command in those CL programs was equivalent to COMMIT(*NONE) on the
RUNSQL and in STRSQL, to mean that no commit would be "performed".

Thanks Alan!

- Dan

On Fri, Jan 13, 2017 at 6:07 PM, Alan Campin <alan0307d@xxxxxxxxx> wrote:

> Commit *NONE always meant update immediately. How else could it work? The
> normal way you process is *NONE so when you make an insert, delete or
> update it occurs immediately. If you turn on the commit, it will not update
> the database until you say commit.
>
> On Fri, Jan 13, 2017 at 3:04 PM, Dan <dan27649@xxxxxxxxx> wrote:
>
> > Our shop is in the process of turning on journaling for production
> tables.
> > I've been out of a journaling environment for several years.  I am
> > currently testing this on our dev box.  I am surprised to find that
> > specifying *NONE for the commitment control parameter of RUNSQL command
> and
> > in the attributes of STRSQL does not cause an error or a rollback when I
> > update, insert, or delete records from a journaled table.  I seem to
> recall
> > from the last shop in which I used journaling that, if we updated,
> > inserted, or deleted records from a journaled table and used
> COMMIT(*NONE),
> > the "changes" would last only as long as the job was active; the
> "changes"
> > would be rolled back as soon as the job ended.  I am not seeing that
> > behavior here.
> >
> > We have several CLLE programs that use RUNSQL COMMIT(*NONE), and I am
> > surprised to find that the updates made via these commands are retained
> > after the job has completed.  (We have SQLRPGLE programs, but all use
> only
> > SELECT queries.)  FWIW, we are at v7r1 with fairly recent PTFs and TRs.
> >
> > What am I missing?
> >
> > - Dan