What is the definition for host variables in case of multi row fetch? Explain with the help of an example.

DB2DatabaseBig Data Analytics

The host variable needs to be declared as an array for the multi-row fetch. Also, we need to define another variable in the working storage section with the configuration S9(4) COMP, which will store the value of the number of rows to be fetched in a single fetch call.We can give any name to this variable, here we have used MAX-ROW. Below is an example of declaring a host variable array and MAX-ROW.

01 ORDER-ID PIC X(25) OCCURS 25 TIMES.
01 MAX-ROW PIC S9(4) COMP VALUE 25.

We will fetch the cursor like below.

EXEC SQL
   FETCH NEXT ROWSET FROM ORDER_CUR FOR :MAX-ROW ROWS
   INTO :ORDER-ID
END-EXEC
raja
Published on 15-Sep-2020 14:25:35
Advertisements