CODEWITHSUNDEEP
databaseoracle-databasedb2isolation-level
dmitrievanthony
dmitrievanthony

Reputation: 1561

Does DB2 for z/OS have isolation level similar to READ ONLY in Oracle?

Does DB2 for z/OS have isolation level similar to READ ONLY in Oracle? I need to implement several big selects to DB2 and also I need to retrieve consistent data which was commited on time when queries were started, so I need something like 'snapshot isolation level'. As far as I know in Oracle it can be implemented by READ ONLY isolation level, but what about DB2 for z/OS?

Upvotes: 1

Views: 547

Answers (2)

mustaccio
mustaccio

Reputation: 19004

DB2 for z/OS does not have "read only" isolation level (nor does Oracle, as "read only" is a transaction state, not an isolation level).

You can avoid lock waits by queries if you use the currently committed concurrent access resolution option, however, note that it does not implement "snapshot isolation" per se -- a query that uses this option will see the latest committed changes, even if those changes were committed after the query started.

Upvotes: 1

bhamby
bhamby

Reputation: 15469

There is a FOR READ ONLY clause in DB2 z/OS. You add it at the end of your query.

For tables in which updates and deletes are allowed, specifying FOR READ ONLY can possibly improve the performance of FETCH operations as DB2® can do blocking and avoid exclusive locks. For example, in programs that contain dynamic SQL statements without the FOR READ ONLY or ORDER BY clause, DB2 might open cursors as if the UPDATE clause was specified.

Here is the Info Center article with more information.

If you're really looking for the DB2 version of "Serializable", then you are looking for Repeatable Read.

Upvotes: 0

Related Questions