I am very cool today reading Db2 magazine. I have got an idea why can’t I write a post on DB2 Read-only cursors. So I have written this excellent post. There are so many rules to say a cursors is Read-only. Below are the list of rules, that says if the cursor is Read-only or not.
Read-only cursors: If the result table is read-only, the cursor is read-only.
The first FROM clause identifies or contains any of the following:
- More than one table or view
- A catalog table with no updatable columns
- A read-only view
- A nested table expression
- A table function
- A system-maintained materialized query table
- A single table that is a system-period temporal table, and a period specification for SYSTEM_TIME is used
- A single view that directly or indirectly references a system-period temporal table in the FROM clause of the outer fullselect of the view definition, and a period specification for SYSTEM_TIME is used
- The first SELECT clause specifies the keyword DISTINCT, contains an aggregate function, or uses both
It contains an SQL data change statement
- The outer subselect contains a GROUP BY clause, a HAVING clause, or both clauses
- It contains a subquery such that the base object of the outer subselect, and of the subquery, is the same table
- Any of the following operators or clauses are specified:
– A set operator
– An ORDER BY clause (except when the cursor is declared as SENSITIVE STATIC scrollable)
– A FOR READ ONLY clause
– It is executed with isolation level UR and a FOR UPDATE clause is not specified.
– It is a VALUES clause.
So, in cursor declaration, if we mention the above clauses, then that cursor will become Read-only cursor.