open fetch and close cursor DB2

Question

I'm using DB2 cursors. My table contains 4 rows. Bellow is the list of instructions that i have done on my code:

1- OPEN CURSOR
2- FETCH CURSOR
3- FETCH CURSOR
4- CLOSE CURSOR
5- OPEN CURSOR
6- FETCH CURSOR
7- CLOSE CURSOR

Supposing i have 4 rows on my table that i want to have them in my results. In the 6th step (FETCH CURSOR) , will i have the 3rd row of the table or i will have the first row because i have closed the cursor. i mean the cursor is saved after closing or not.

Answer 1

i mean the cursor is saved after closing or not.

From the IBM documentation

If the cursor is reopened, it is again located at the beginning of the rows to be fetched.

However, you can code a row-positioned phrase on your FETCH statement using the ABSOLUTE option to position your cursor at a particular row in the result set.

        Working-Storage Section.
        77  C1-POSITION PIC S9(009) COMP-5 VALUE +1.
        EXEC SQL DECLARE C1 CURSOR FOR [...] END-EXEC.
        [...]
        Procedure Division.
            EXEC SQL OPEN C1 [...] END-EXEC
            *> fetch row 1
            EXEC SQL FETCH ABSOLUTE C1-POSITION C1 [...] END-EXEC
            ADD 1 TO C1-POSITION
            *> fetch row 2
            EXEC SQL FETCH ABSOLUTE C1-POSITION C1 [...] END-EXEC
            ADD 1 TO C1-POSITION
            *> fetch row 3
            EXEC SQL FETCH ABSOLUTE C1-POSITION C1 [...] END-EXEC
            ADD 1 TO C1-POSITION
            EXEC SQL CLOSE C1 END-EXEC
            [...]
            EXEC SQL OPEN C1 [...] END-EXEC
            *> fetch row 4
            EXEC SQL FETCH ABSOLUTE C1-POSITION C1 [...] END-EXEC
            ADD 1 TO C1-POSITION
            EXEC SQL CLOSE C1 END-EXEC

You will need appropriate error checking (not included here) to ensure you have not attempted to retrieve a row whose absolute number exceeds the number of rows in the result set.

The comment by @GilbertLeBlanc regarding the relative expense of OPEN and CLOSE is something to keep in mind.

Caveats: I don't have a mainframe to test this on, code is freehand and only provided as an example, I am just crafting an answer based on the IBM documentation.