COBOL DB2 Multi Row Fetch

Multi row Fetch Overview in COBOl Db2 program WITH EXAMPLE:

Cobol Db2 multi row fetch concept will be used to retrieve multiple rows with a single fetch statement as opposed with a normal cursor which fetches only single record at a time.

The multi-row fetch capability was introduced to DB2 z/OS in Version 8.

This can be achieved in two different ways

1. One way is to declare the cursor as normal one and in Fetch by using the row count
2. By using the Row set positioning cursors

First Method:

The operations used to define, open, and close a cursor for a multiple-row remains the same. Only the FETCH statement changes to specify the number of rows to retrieve and the storage where the rows are placed.

Need to declare a host structure of array to capture the fetched records. The size of the array depends on your requirement (if you need 10 rows at a time, declare the array with 10 occurrence).

As said earlier there are no changes in declare, open & close statement syntax. Only the fetch syntax differs.

Ex:

EXEC SQL FETCH CUR1 FOR 15 ROWS INTO: ww-host-array

Here CUR1 is the name of the cursor

ww-host-array is declared with 15 occurrences.

Multi-row fetch by using the row-set positioning cursors:

• Row-set is nothing but group of rows returned by a single fetch statement.
• The maximum size of row set is 32767

Procedure to use row-set positioning cursors:

1) declare the cursor with row-set option

Ex:

EXEC-SQL
     DECLARE cur1 CURSOR WITH ROW-SET POSITIONING
     FOR select * from ------
END-EXEC

Here we can specify either WITH ROW-SET POSITIONING or WITHOUT ROW-SET POSITIONING . WITHOUT ROW-SET POSITIONING is the default one.

2) Open the cursor , it is same as normal cursor open statement.

3) Fetch the cursor with row-set option

FETCH NEXT-ROWSET FROM cur1FOR 10 ROWS
       INTO :ww-host-array

Here there are multiple options while fetching

Fetch FIRST-ROWSET – Fetches first 10 rows

Fetch NEXT-ROWSET – fetches next 10 rows

We can also have the PRIOR-ROWSET, CURRENT-ROWSET, LAST-ROWSET & ROWSET STARTING AT options.

By using the rowset starting option we can specify from where to start fetching and how many records to retrieve from that position.

Ex:

FETCH ROWSET STARTING AT ABSOLUTE 15 FOR 10 ROWS.

In this case it retrieves 10 rows starting from the position 15.

Advantages of multi rowfetch:

1. reduces the number of program to database calls, thus decreases the burden on DB2.
2. As it also supports positioned updates and deletes, here also the performance gets increased.