Are you still using the DB2 cursor to fetch a single row at a time? If yes, then consider upgrading the application logic to fetch multiple rows instead of a single row at a time. DB2 cursor multi-row fetch technique retrieves multiple rows at one time. This improves application performance and lowers COBOL+Db2 application running costs.
In this multi-row fetch tutorial, you'll learn the basics of DB2 rowset positioning (i.e. DB2 multi-row fetch technique). You'll learn how to fetch and process multiple rows (i.e. Multi-row fetch) in your COBOL+DB2 applications by using a single fetch statement.
Agenda.
- Introduction.
- What is a DB2 Cursor?
- How to fetch multi-row in the COBOL DB2 program?
- What is the DB2 rowset positioning?
- How does DB2 multi-row fetch work?
- Benefits of Multi-row fetch.
- Youtube: COBOL+DB2 Multi-Row Fetch Tutorial Video.
- Summary.
Introduction.
A COBOL-DB2 application program either uses the DB2 cursor or singleton select statements to access data from the DB2 database. However, the decision of using a cursor or SQL statement depends on business requirements and technical feasibility.
In the COBOL-DB2 program, the cursor is the preferred option to navigate through the set of rows returned by the SQL statement. You can use DB2 cursors to read or update data in the DB2 database. Most of the program logic still uses a cursor single-row fetch facility instead of a multi-row fetch. This leads to poor application performance and high application running costs and the Mainframe is all about cost!
What is a DB2 Cursor?
DB2 cursor was designed to bridge the gap between SQL and the host language (i.e. COBOL, JAVA, C++, etc) processing. These programming languages are capable of processing single records at a time but SQL operates on a set of rows. Without using the DB2 cursor it would be absolutely impossible for the COBOL program to navigate through the set of rows.
Thus, a cursor in DB2 is a mechanism by which you access data row-by-row from the result set. Cursors in DB2 keep track of records in the results set, and with the help of cursors, you can update, delete, or select individual rows from the table.
How to fetch multi-row in the COBOL DB2 Program?
Over the past few years, DB2 has evolved dramatically. In the beginning, the cursor could fetch a single row, but multi-row fetch functionality was added in DB2 z/OS version 8. DB2 multi-row fetch capability enables programmers to fetch multiple rows with a single fetch statement.
This reduces the number of I/O operations with the database and improves the performance of the COBOL DB2 application. Lastly, DB2 multi-row fetch capability help in reducing application running cost.
Now let's try to understand how to include the multiple-row fetch technique in your COBOL DB2 programs. There are two ways to include the multi-row fetch technique in COBOL+DB2 applications.
- Multi-row with serial or normal Cursor.
- Cursor with Rowset positioning.
Let's discuss each DB2 multi-row fetch technique with an example.
What is the DB2 rowset positioning?
Essentially, a row-set is a grouping of rows that are processed together. Cursor rowset positioning enables you to fetch multiple rows (i.e. set or group) instead of a single row by using a single fetch statement.
If you fetch multiple rows at once, your request becomes more efficient, especially for distributed requests. A single fetch call can return up to 32 767 rows. The cursor will be positioned on the last row retrieved after retrieving the data.
How does DB2 multi-row fetch work?
You can use the FETCH statement with both serial and scrollable cursors. A cursor for a multiple-row FETCH is defined, opened, and closed in the same manner as for a single-row FETCH. There is only one change, the FETCH statement, which specifies the number of rows to get and the storage that contains them.
You can define the storage location where rows are placed in two different ways:
- As part of a host structure array.
- As part of a row storage area with an associated descriptor.
Let's take a look at each technique one by one.
Example 1: Fetch Multi-row with the simple cursor.
In order to use a multi-row fetch with a simple cursor, you must define the appropriate structures. Therefore, an array of host variables must be defined into which the rows are put. There will be one variable array for each column fetched, where its values will be placed. Make sure the array size matches the row-set size.
In the following example,
IDENTIFICATION DIVISION.
*
WORKING-STORAGE SECTION.
*
01 WS-TBL-DPT.
02 HDEPT OCCURS 10 TIMES.
05 HEMPNO PIC X(6).
05 HLASTNAME.
49 HLASTNAME-LEN PIC S9(4) BINARY.
49 HLASTNAME-TEXT PIC X(15).
05 HWORKDEPT PIC X(03).
05 HJOB PIC X(08).
01 WS-TBL-IND.
02 HIND-ARRAY OCCURS 10 TIMES.
05 HINDS PIC S9(04) BINARY OCCURS 4
TIMES.
*
EXEC SQL
DECLARE CRSAL CURSOR FOR
SELECT EMPNO, COMM, SALARY, DEPT, JOB
FROM EMPLY
WHERE DEPT = “CS”
END-EXEC.
…..
…..
EXEC SQL
FETCH CRSAL FOR 10 ROWS
INTO :HDEPT
:HIND-ARRAY
END-EXEC.
Example 2: Fetch Multi-row with Cursor Row-set positing.
IDENTIFICATION DIVISION.
*
WORKING-STORAGE SECTION.
*
01 WS-TBL-DPT.
02 HDEPT OCCURS 10 TIMES.
05 HEMPNO PIC X(6).
05 HLASTNAME.
49 HLASTNAME-LEN PIC S9(4) BINARY.
49 HLASTNAME-TEXT PIC X(15).
05 HWORKDEPT PIC X(03).
05 HJOB PIC X(08).
01 WS-TBL-IND.
02 HIND-ARRAY OCCURS 10 TIMES.
05 HINDS PIC S9(04) BINARY OCCURS 4
TIMES.
EXEC SQL
DECLARE CRSAL CURSOR WITH ROWSET POSITIONING FOR
SELECT EMPNO, COMM, SALARY, DEPT, JOB
FROM EMPLY
WHERE DEPT = “CS”
END-EXEC.
…..
…..
EXEC SQL
FETCH CRSAL FOR 10 ROWS
INTO :HDEPT
:HIND-ARRAY
END-EXEC.
Benefits of Multi-row fetch.
DB2 z/OS Version 8 introduced the multi-row fetch capability. Since then, it's been very useful, and it would definitely help reduce costs. The following are the benefits of DB2 Multi-row fetch capability.
- DB2 application programs that access a lot of data can benefit from multi-row FETCH techniques.
- Reducing the number of statements issued between your program's address space and DB2. Thus, reducing CPU consumption.
Testing has shown that multi-row FETCH reduces CPU time by up to 50%. Multi-row FETCH works best at about 100 rows. Consequently, the percentage improvement will decrease as the number of rows fetched per call decreases.
Youtube Video: Watch COBOL DB2 Multi-Row Fetch Tutorial.
Youtube: COBOL DB2 Multi-Row Fetch Tutorial.
Summary.
Finally, this marks an end to the COBOL DB2 Multi-ROW Fetch or DB2 rowset positioning. In this session, we discussed what is multi-row fetch capability and how you can use Multi-row fetch capability in the COBOL DB2 programs. Do check out COBOL LEVEL 88 Condition.
►Subscribe to Topictrick & Don't forget to press THE BELL ICON to never miss any updates. Also, Please visit mention the link below to stay connected with Topictrick and the Mainframe forum on -
► Youtube
► Facebook
► Linkedin
► Reddit
Thank you for your support.
Mainframe Forum™
No comments:
Post a Comment