Cusror in DB2 ---> Part III (Multirow fetch)




In this post ,we are going to discuss how to fetch and process multiple rows (Multirow fetch) at a time using single fetch statement in DB2 cursor.

The multirow fetch was introduced in DB2 version 8 onwards.

Why to use it ?

By fetching multiple rows at once, your request can become more efficient and it can improve the performance by reducing the CPU time.

How to use it ?

As we know with regular Cursor fetch statement we can fetch one row at a time, values will be fetched into host variables and processed later into the program.

Ex -

EXEC SQL
      FETCH  CURSOR_NAME
      IN TO :A,:B
END-EXEC.


Now if we want to use Multirow Fetch feature, First of all our Cursor should be defined with something called as "With Rowset positioning".

A rowset is a group of rows that are operated on as a set. Such a cursor enables your program to retrieve more than one row using a single FETCH statement

Define Cursor:

EXEC SQL
    DECLARE CURSOR CURSOR_NAME
    WITH ROWSET POSITIONING
    FOR
    SELECT EMP_NAME FROM EMPTABLE
END-EXEC.

Now with this cursor when we use fetch statement we will get multiple rows at a time which normal host variable structure will not be able to handle it as it was designed to receive only one row at a time.  

So,  to use a multi-row fetch you must have defined the appropriate structures to receive multi-row data. This means you must defined an array of host variables into which the fetched rows can be placed. Each column fetched requires its own host variable array into which its values will be placed. If column is nullable then make sure we have separate  host variable array defined for null indicator.

Be sure to match the array size to the rowset size. This way a single FETCH statements can be written to retrieve more than a single row from the result set.

Fetch Cursor :

EXEC SQL
     FETCH ROWSET FROM CURSOR_NAME
     FOR 10 ROWS
     INTO :HOST-VAR-ARRAY
END-EXEC.

So it looks exactly like normal fetch statement except the word ROWSET and FOR 10 ROWS.

ROWSET keyword indicates that this is a cursor with Rowset positioning.
The FOR 10 ROWS clause specifies the size of the rowset to be returned. The maximum rowset size is 32,767.

Rowset cursors are very useful when you need to retrieve many rows or large amounts of data.
By retrieving multiple rows with a single FETCH, multiple trips between the application and the database can be eliminated, thereby improving network performance.


 

LOB Data type in DB2.

We have various different type of data types in DB2.  For ex- SMALL INT,INT,CHAR,VARCHAR..

These data types seems to work fine and do their job perfectly .But if you want to store large audio,video, images or any other file which has much larger size than 32kb they cant handle it.

VARCHAR ,VARGRAPHIC,VARBINARY has the storage limit of 32 KB.

For data objects that are larger than 32 KB, you have to use something called as large object (LOB) data types to store these objects.

DB2 provides three different type of LOB data types to store these data objects as strings of up to 2 GB in size:
Character large objects (CLOBs) :
 
Use this data type if your data is larger (or might grow larger) than the VARCHAR data type permits. It can store up to 2GB. For example, you can store information such as an employee resume, or the text of book in a CLOB.
 
Double-byte character large objects (DBCLOBs)
 
Use the DBCLOB data type to store large amounts of DBCS data, such as documents that use a DBCS character set.
 
Binary large objects (BLOBs)
 
Use the BLOB data type to store large amounts of non character data, such as pictures, voice, and mixed media.
If your data does not fit entirely within a data page, you can define one or more columns as LOB columns.

Example :

CREATE TABLE EMPLOYEE_SALARY
     (DEPTNO   CHAR(3)      NOT NULL, EMP_RESUME  BLOB(1G) )