Using partial commits in a CA GEN app targeting Oracle

[JULIO De Castro]

Hi,

I'd like to know if someone has guessed an idea of how to do partial commits in a CA GEN app that targets Oracle.

The problem is that the READs are generated as "SELECT ... FOR UPDATE"

Then, when we issue a commit via inline with: globdata.getDBMSData().getTransactionManager().commit();

that cursor is closed, so when doing the next fetch, we get an SQL error.

Initially our app was cobol/db2, where the cursors could exists "WITH HOLD" and keept open when doing partial commits.

But it seems that does not exists with Oracle.

Anyone with a similar situation or willing to share some ideas?

Thank you,

Best regards.

[Christian Kersters]

Julio,

We have helped another customer in such move a few years ago.

Cursors in Oracle are with hold by default, but are invalidated as soon as an update occurs in the tables targeted by the READ EACH. We have created a utility that converts Gen code accordingly, to profit as much as possible from the WITH HOLD feature, while coping with invalidation of cursors.

We can discuss this further offline. You can reach me at [e-mail].

Regards

------------------------------
Christian Kersters
------------------------------

[JULIO De Castro]

Hi Christian,

>> but are invalidated as soon as an update occurs in the tables targeted by the READ EACH.

That is not exaclty what We are experiencing. The whole program works if we don't do commits. So the update by itself does not seem to break anything.

However, if we do commits inside the readeach, then it breaks.

For example, using:

SQLStatement.setLength(0);
SQLStatement.append("commit");
PreparedStatement hstmt_commit_xxx = globdata.getDBMSData().getDBMSManager().getStatement(globdata, iefRuntimeParm2, "SIRIUSDB", SQLStatement.toString());
hstmt_commit_xxx.execute();

We get:

ORA-01002: fetch out of sequence SQLSTATE: 24000 SQLCODE: 1002

Can your utility help with this scenario?

Thanks,
Best regards.

[Christian Kersters]

You're correct, Julio.

(My memory of the symptoms was not very accurate, a few years already since we worked on it).

Of course, we cannot go beyond what Oracle allows.

What the utility does is to transform the READ EACH statement to provide restart points based on cursor invalidity.

This means that:

• For READ EACHes of entities that are not updated, the COMMIT points will be executed, and, the cursor remaining open, the statement will be executed only once
• FOR READ EACHes of entities "FOR UPDATE", the READ EACH will be re-executed (with resume constraints automatically added) as soon as the FETCH fails with ORA-01002

------------------------------
Christian Kersters
------------------------------