Re: db2, php, AUTOCOMMIT off rollback not working

On 07 Mar 2013 15:04, Sheldon Foster wrote:

On another note I tried interactive SQL on the iSeries to try the
rollback there and discovered a couple things.

Presumably referring to STRSQL.?

1. The file was not being journaled. (probably overlooked because of
how painfully obvious that should have been?)

If the connection had really been active for isolation other than NC, then the DELETE should have failed. That is because isolation other than NC should have failed the SQL with a 7008 error\sqlcode [SQL7008 RC3] when the database file is not journaled.

2. Even with the file being journaled, on the iSeries in interactive
SQL, I had to use the SQL command "SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ" in order for the rollback to work but at least it
worked - perhaps using the "with UR" will accomplish the same thing?

There are several levels of isolation other than RR; i.e. UR, CS, and RS. These can either be the default isolation level, or specified instead on the DML statement using WITH-isolation clause. The STRSQL has F13=Services and 1="Change session attributes" to set the isolation level.

So like with the connection, the DELETE issued in STRSQL *after* the SET TRANSACTION ISOLATION LEVEL RR should fail when directed against a non-journaled TABLE [database file]... *unless* the DELETE statement included the clause: WITH NC

Hmmm... I was not even aware that the SQL SET _statement_ was allowed in the STRSQL environment for the ISOLATION LEVEL. I thought the only way to [re]set the isolation was via the F13=Services. Or maybe, after thinking back, I had instead always *avoided* the SET, because the effect was not [IMO properly] reflected in the F13=Services. That is similar to how, if STRCMTCTL was issued before STRSQL COMMIT(*NONE) [i.e. as effected, whether a new session or existing], the frustrating effect for the mismatch between being able to COMMIT and ROLLBACK and that F13=Services says "Commitment Control = *NONE"; i.e. can lead to major confusion.