How to Declare Read-Only Cursors in DB2

5 New things on Db2 Cursors
[5 New things on DB2 Cursors]

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.

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.