Quick Tutorial on DB2 Cursors

Cursors plays prominent role in application programming development. The five real uses of cursors explained in this post.

When to use Cursor is most important decision. If we use Cursors un-necessarily, it causes to take more CPU time.

1. Program requires a SELECT statement that returns more than one row, then we need to use cursors. Singleton SELECT cannot be used to return more than one row.
2. We always no need to go for cursor. Cursor takes more CPU time compared to Singleton SELECT. We can use Singletone by coding the program to accept -811 as a successful SQL call and process the returned row.
3. No limitation on number of Cursors declared in a program. We can Open and Declare any number of CURSORS at a given point of time.
4. When commit is issued, all cursors will be closed, except CURSORS with WITH HOLD. However, during ROLLBACK all WITH HOLD cursors also will be closed. WITH HOLD curors will created more problem in CICS that takes more system resources, and difficult to with hold. So better avoid using them.
5. We cannot use Cursor for Update or Delete, when we use the following in Declare SQL

• UNION clause
• DISTINCT clause
• GROUP BY clause
• ORDER BY clause
• Joins
• Subqueries
• Correlated subqueries
• Tables in read-only mode, ACCESS(RO)
• Tables in utility mode, ACCESS(UT)

Read-only views

• Execution of cursor will start with OPEN cursor, not from Declare cursor. All host variables we need to initialize properly. During OPEN time it validates all host variables.

Related Posts