On 21-Feb-2012 10:43 , Jerry C. Adams wrote:
All of this stuff about commitment control reminds me that I created
the file with COMMIT(*NONE).
The isolation\commit is not an attribute of the database file. The
COMMIT\isolation attribute is a run-time attribute; either for an
effective SQL "session" [all statements scoped to either a job or ACTGRP
while isolation is in effect; see the CMTSCOPE parameter of STRCMTCTL]
or for an SQL statement [using the WITH isolation clause].
So, would changing the file to COMMIT(*CS) and then changing my
program, which reads the file and deletes records it used, to COMMIT
the deletes be sufficient, or would the vendor, also, need to use
COMMIT? Sounds like belt and suspenders, but no harm in that.
Dismissing any [lack of] merits for the ALCOBJ in the current coding,
the *minimal* change required to make the existing process functional to
prevent partial transactions, should be to have the POS perform all of
the insert activity in one unit of work under isolation other than
*NONE, and then COMMIT all of the work upon completion; i.e.:
≥ connect with isolation [not *NONE, no autocommit] <change>
≥ perform one or more inserts and\or other DML activity <no change>
≥ COMMIT <added>
≥ disconnect <no change>
In that manner, if the connection is interrupted, the incomplete work
will be rolled back; i.e. an implicit ROLLBACK. Note that just as the
processing for the ALCOBJ requester could have /jumped in/ between
multiple insert requests thus making a transmission appear incomplete,
prior partial transmissions of the POS data could have been the result
of a termination\interruption such that a retransmission would have been
necessary.
And while either or both establishing a parent and child relationship
for the data and\or ensuring participation within the proper agreed-to
isolation level to allow eliminating the allocation are worthwhile
objectives, neither should be necessary to avoid a partial transaction.
Note: As already mentioned by other(s), the database file must be
journaled in order to allow isolation\cmtctl [i.e. avoid SQL7008]. A
side benefit, if the file is not journaled already, I\O will be logged
so DSPJRN can review all of the transmission activity including seeing
any transactions that were rolled back for some reason... and because
the JOB is identified in the logging\journaling, the joblog of that
transmission might be available to be reviewed for the cause of the
interruption.
Regards, Chuck
midrange.com
