Re: Object Allocation Problem - or Something Else?

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