---
title: "Exclusive What is Cursor WITH HOLD in DB2"
description: "Point 1 - The best example for cursor WITH HOLD is, just imagine, you are processing Master table and Detail table in DB2, you need to retrieve a row from Master table and process the row data, and yo..."
url: https://srinimf.com/2014/07/09/4-traits-in-db2-cursor-with-hold/
date: 2014-07-09
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2014/07/pexels-photo-4195408.jpeg?fit=1880%2C1253&ssl=1
categories: ["DB2 V11"]
tags: ["Cursor WITH HOLD", "DB2 Cursors"]
type: post
lang: en
---

# Exclusive What is Cursor WITH HOLD in DB2

**Point 1 –** The best example for cursor WITH HOLD is, just imagine, you are processing Master table and Detail table in DB2, you need to retrieve a row from Master table and process the row data, and you need to update it in Detail table. To perform UPDATE in detail table we need to give commit. During the process we are HOLDING the cursor.

**Point 2 –** When you do COMMIT, the WITH HOLD cursor will not be closed. But , during ROLL BACK, all the cursors(including WITH HOLD) will be closed, and all the locks will be released.

## Syntax of cursor

```
>>-DECLARE--+-cursor_id------------+---------------------------->
            |  (1)                 |
            '--------cursor_id_var-'
 
                                         (1)                                  (2)
>--+-CURSOR--+------------------+--+-FOR-------| Subset of INSERT Statement |-----------------+----+-><
   |         |  (1)             |  +-| Select Options |---------------------------------------+    |
   |         '--------WITH HOLD-'  |                             (3)                          |    |
   |                               '-FOR--+-| SELECT Statement |----------------------------+-'    |
   |                                      +-statement_id------------------------------------+      |
   |                                      |  (1)                                            |      |
   |                                      +--------+-statement_id_var---------------------+-+      |
   |                                      |        |                                 (4)  | |      |
   |                                      |        '-| EXECUTE PROCEDURE Statement |------' |      |
   |                                      |  (5)                                  (6)       |      |
   |                                      '--------| EXECUTE FUNCTION Statement |-----------'      |
   |  (1)                                                             (3)                          |
   +--------SCROLL CURSOR--+-----------+--FOR--+-| SELECT Statement |----------------------------+-+
   |                       '-WITH HOLD-'       +-statement_id------------------------------------+ |
   |                                           |  (1)                                            | |
   |                                           +--------+-statement_id_var---------------------+-+ |
   |                                           |        |                                 (4)  | | |
   |                                           |        '-| EXECUTE PROCEDURE Statement |------' | |
   |                                           |  (5)                                  (6)       | |
   |                                           '--------| EXECUTE FUNCTION Statement |-----------' |
   |  (5)                                                          (7)                             |
   '--------CURSOR FOR--+-| SELECT with Collection-Derived Table |------+--------------------------'
                        |                                          (8)  |
                        '-| INSERT with Collection-Derived Table |------'
 
Select Options:
 
                                        .-FOR READ ONLY-------------------------.
                                  (9)   |  (1)                                  |
|--| Subset of SELECT Statement |-------+--------FOR UPDATE--+----------------+-+--|
                                                             |     .-,------. |
                                                             |     V        | |
                                                             '-OF----column-+-'
```

**Point 3 – **When you do issue CLOSE cursor, all the WITH HOLD cursors will be closed.

**Point 4 –** Cursors with WITH HOLD creates some problems in CICS, before end-of-transaction we need to close all HOLD cursors, else, DB2 force SIGNON to restore the thread to initial state. But, We can use WITH HOLD in CICS, only thing we need to close properly.

Related

Related[How to Resolve SQL Error -500 Quickly](https://srinimf.com/2015/12/05/sql-error-500-cursor-was-closed-when-the-connection-destroyed/)

- [Quick Tutorial on DB2 Cursors](https://srinimf.com/2015/02/24/5-new-things-on-db2-cursors/)DateFebruary 24, 2015
- [DB2: Cursor WITH HOLD, CLOSE, OPEN Ideas](https://srinimf.com/2015/07/07/db2-with-hold-close-and-open-of-cursors-in-commit-or-rollback/)DateJuly 7, 2015
- [SQL Rollback: How to Understand Its Background Process](https://srinimf.com/2011/09/18/db2-rollback-cursors/)DateSeptember 18, 2011

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2014%2F07%2F09%2F4-traits-in-db2-cursor-with-hold%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2014%2F07%2Fpexels-photo-4195408.jpeg%3Ffit%3D1200%252C800%26ssl%3D1&description=Exclusive%20What%20is%20Cursor%20WITH%20HOLD%20in%20DB2)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2014/07/09/4-traits-in-db2-cursor-with-hold/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2014/07/09/4-traits-in-db2-cursor-with-hold/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2014/07/09/4-traits-in-db2-cursor-with-hold/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2014/07/09/4-traits-in-db2-cursor-with-hold/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20Exclusive%20What%20is%20Cursor%20WITH%20HOLD%20in%20DB2&body=https%3A%2F%2Fsrinimf.com%2F2014%2F07%2F09%2F4-traits-in-db2-cursor-with-hold%2F&share=email)
-

### Like this:

Like Loading…