Summary -

In this topic, we described about the below sections -

Why Cursor?

While reading the DB2 table in the COBOL program, if the WHERE condition retrieves more than one row, the programmer should handle the scenario by validating SQLCODE.

The programmer needs to include SQLCA in the program to retrieve the SQLCODE from the executed query.

If the query condition retrieves more than one row, the query returns the first row from the result set of rows and returns the SQLCODE as -811.

If the programmer expecting the first row from the result set, handling -811 would satisfy the requirement.

But suppose the programmer is expecting the total result set, which is temporary and only exists when the SELECT query executes. In that case, the process needs a unique mechanism to retrieve all rows one by one.

The mechanism is known as "CURSOR" in DB2.

What is Cursor?

The cursor is used to process the rows one by one from the group of rows. The process is almost the same as pointing the finger at a particular line on the printed page.

We can use the cursor in two places to retrieve the data, and those are -

  • Application program
  • DB2 stored procedure

Here, we will discuss the application program with the cursor concept.

The application program retrieves and processes one or more rows that satisfy the WHERE condition in the SELECT statement using the cursor.

The application program process the rows at a time.

The result set is also referred to as the result table while using the cursor.

Cursor Life Cycle -

The cursor plays a very important in retrieving the rows one by one from the result table.

The cursor life cycle has the following phases -

PhaseDescription
Declare Cursor Declares a cursor in the application program.
Open Cursor Opens the cursor declared in the application program.
Fetch Cursor Retrieves the current positioned row data and assigns it to the corresponding variable(s) / host variable(s).
Close Cursor Closes the cursor specified with it.

Practical Example -

Scenario - The below example describes how the cursor is declared and used in the COBOL + DB2 program.

Requirement - Display employee id, name, designation from department 1000 in the employee_details table.

Input-

employee_details table

Code -

CURSOR Program Code
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
       IDENTIFICATION DIVISION.                                         
       PROGRAM-ID. CREADONL.                                            
                                                                        
       ENVIRONMENT DIVISION.                                            
       DATA DIVISION.                                                   
       WORKING-STORAGE SECTION.                                         
                                                                        
           EXEC SQL                                                     
             INCLUDE SQLCA                                              
           END-EXEC.                                                    
                                                                        
           EXEC SQL                                                     
             INCLUDE EMPLDET                                            
           END-EXEC.                                                    
                                                                        
      * DECLARING CURSOR                                                
                                                                        
           EXEC SQL                                                     
                DECLARE CSR1 CURSOR FOR                                 
                SELECT EMP_ID,                                          
                       EMP_NAME,                                        
                       DESIGNATION                                      
                FROM   EMPLOYEE_DETAILS                                 
                WHERE  DEPT_ID = 1000                                   
                FOR    FETCH ONLY                                       
           END-EXEC.                                                    
                                                                        
       PROCEDURE DIVISION.                                              
                                                                        
      * OPENING CURSOR                                                  
                                                                        
           EXEC SQL                                                     
                OPEN CSR1                                               
           END-EXEC.                                                    
                                                                        
      * FETCHING CURSOR LOOP UNTIL END OF RESULT TABLE                  
                                                                        
           IF SQLCODE EQUAL ZERO                                        
              PERFORM FETCH-EMP-DETAILS                                 
                 THRU FETCH-EMP-DETAILS-EXIT                            
                UNTIL SQLCODE NOT EQUAL 0                               
           END-IF.                                                      
                                                                        
      * CLOSING CURSOR                                                  
                                                                        
           EXEC SQL                                                     
                CLOSE CSR1                                              
           END-EXEC.                                                    
                                                                        
           STOP RUN.                                                    
                                                                        
       FETCH-EMP-DETAILS.                                               
                                                                        
      * FETCH CURSOR                                                    
                                                                        
           EXEC SQL                                                     
                FETCH CSR1                                              
                INTO   :EMP-ID,                                         
                       :EMP-NAME,                                       
                       :DESIGNATION                                     
           END-EXEC.                                                    
                                                                        
           EVALUATE SQLCODE                                             
               WHEN ZERO                                                
                    DISPLAY  EMP-ID, "|", EMP-NAME, "|",                
                             DESIGNATION                                
               WHEN 100                                                 
                    CONTINUE                                            
               WHEN OTHER                                               
                    DISPLAY "DB2 ERROR:  ", SQLCODE                     
           END-EVALUATE.                                                
                                                                        
       FETCH-EMP-DETAILS-EXIT.                                          
           EXIT.                                                        
**************************** Bottom of Data ****************************

Output -

CURSOR Program Output