DB2 COMMIT top ideas to control Cursors

Cursor holdability refers to a characteristic in cursors that is concerned with whether a cursor is automatically closed when the transaction in which the cursor was opened is committed.

On COMMIT What happens to Cursors

  • On COMMIT cursors will be closed automatically

A transaction is an atomic unit of work. This means that all statements within the transaction must succeed or none of them can have any effect.

  • What happens when one statement fails in series of SQL statements

If some statements within a transaction are executed and then one statement fails, all executed statements are rolled back and the database remains unchanged.

Life Cycle of Cursor

Cursor Life cycle
Cursor Life cycle

2 Top Ideas to Control Cursors

WITH HOLD and WITHOUT HOLD.

  • If you specify WITH HOLD, your cursor will remain open after you commit the transaction, until you explicitly close it.
  • If you specify WITHOUT HOLD, your cursor will be automatically closed when the transaction is committed.
  • If neither option is specified, WITHOUT HOLD is assumed and your cursor is automatically closed.

Read More

Lead yourself to enhance your SQL skills and transform to Senior SQL Developer.

Author: Srini

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