CURSORS in DB2

When we are using the DB2 in our applications we can only have one row of data at a time. So what we will do if we don’t know which row exactly we need?, what if we have more than 1 row to work with? well the answer is “CURSORS”.

Cursor is used when more than one row are to be selected.  Cursors has mainly 4 control statements.

1. Declare.

A name will be assigned for particular SQL statement. The name should be unique in the scope of the program. there are no limits for the number of cursors which we can have in one application program. We can declare cursor in Working storage section or Procedure division.
E.g.
EXEC SQL                                 
    DECLARE CURREAD1 CURSOR FOR          
        SELECT NAME,SEQ FROM IBMGRP.MYNAM
END-EXEC.                                

2. Open.

This statement builds the resultant table.
E.g.
EXEC SQL       
   OPEN CURREAD1
END-EXEC.  

3. Fetch.

Fetch statement will returns data from the resultant table (One row at a time) and assigns values to the specified host variables.
E.g.
EXEC SQL                              
   FETCH CURREAD1 INTO :WS-NAME,:WS-SEQ
END-EXEC                              

4. Close

Empty all the resources used by the cursor.
E.g.
EXEC SQL        
   CLOSE CURREAD1
END-EXEC.       

All these control statements will throw specific SQLCODES.

Few Snippets.

Read table.

EXEC SQL                                
    DECLARE CURREAD1 CURSOR FOR         
        SELECT NAME,SEQ FROM IBMGRP.MYNAM
END-EXEC.
Open
EXEC SQL       
   OPEN CURREAD1
END-EXEC.  

PERFORM UNTIL SQLCODE = 100                         
   EXEC SQL                                         
      FETCH CURREAD1 INTO :WS-NAME,:WS-SEQ          
   END-EXEC                                         
   MOVE SQLCODE             TO WS-SQLCODE           
   DISPLAY ‘SQLCODE FETCH ‘ WS-SQLCODE              
   IF SQLCODE = 0 THEN                              
    MOVE SPACES TO TEMP-MSG                         
    STRING                                          
    ‘NAME: ‘ DELIMITED BY SPACE ‘ ‘ DELIMITED BY SIZE
    WS-NAME DELIMITED BY SPACE ‘,’ DELIMITED BY SIZE
    ‘SEQ#’ DELIMITED BY SPACE ‘ ‘ DELIMITED BY SIZE 
    WS-SEQ DELIMITED BY SIZE INTO TEMP-MSG          
    DISPLAY TEMP-MSG                                
   END-IF                                           
END-PERFORM.

Fetch name and SEQ till we hit SQLCODE 100 and display the data.

Update Table

We need to mention FOR UPDATE OF and the field name in declare statement.

EXEC SQL                                
    DECLARE CURUPDT1 CURSOR FOR         
        SELECT NAME,SEQ FROM IBMGRP.MYNAM
        WHERE SEQ = :WS-SEQ             
        FOR UPDATE OF NAME              
END-EXEC.     
here I will be updating NAME field of MYNAM table                          
EXEC SQL                                
   OPEN CURUPDT1
END-EXEC.
MOVE ‘002’               TO WS-SEQ.       
EXEC SQL                                  
   FETCH CURUPDT1 INTO :WS-NAME,:WS-SEQ-TMP
END-EXEC                                       
EXEC SQL                 
                 
   UPDATE IBMGRP.MYNAM                    
   SET NAME = :WS-NAME                    
   WHERE CURRENT OF CURUPDT1                    
END-EXEC.   
“CURRENT OF CURUPDT1” statement will pick the current row to update.   
EXEC SQL                                  
   CLOSE CURUPDT1                         
END-EXEC.
  
Delete Record

Like cursor for updating a record we need to mention FOR UPDATE OF in cursor declaration statement.
    

EXEC SQL                                
    DECLARE CURDELT1 CURSOR FOR         
        SELECT NAME,SEQ FROM IBMGRP.MYNAM
        WHERE SEQ = :WS-SEQ             
        FOR UPDATE OF NAME              
END-EXEC.                               
EXEC SQL                                
   OPEN CURDELT1                        
END-EXEC.                                                              
EXEC SQL                                  
   FETCH CURDELT1 INTO :WS-NAME,:WS-SEQ-TMP
END-EXEC                                  
EXEC SQL                   
   DELETE FROM IBMGRP.MYNAM
   WHERE SEQ = :WS-SEQ     
END-EXEC.                  
EXEC SQL             
   CLOSE CURDELT1    
END-EXEC.
  

Sample DB2-COBOL-CURSOR Code
Please refer http://mainframegeek.wordpress.com/2011/05/12/steps-in-a-cobol-db2-program to get JCL for compiling and executing DB2-COBOL-CURSOR program

—-+—-1—-+—-2—-+—-3—-+—-4—-+—-5—-+—-6—-+—-7–
       IDENTIFICATION DIVISION.                           
       PROGRAM-ID. DBPGM01.                               
       AUTHOR    . SHIBU.T.                               
      *                                                   
       DATA DIVISION.                                     
       WORKING-STORAGE SECTION.                           
           EXEC SQL                                       
               INCLUDE MYNAM                              
           END-EXEC.                                      
           EXEC SQL                                       
               INCLUDE SQLCA                              
           END-EXEC.                                      
       01  WS-TEMP-VAR.          &
nbsp;                        
           05  TEMP                    PIC X(30).         
           05  TEMP-MSG                PIC X(60).            
       01  WS-TBLE-DTA.                                   
           05  WS-SEQ                  PIC X(3).          
           05  WS-SEQ-TMP              PIC X(3).          
           05  WS-NAME                 PIC X(15).         
       01  WS-SQLCODE                  PIC ——9.       
      *                                                   
       PROCEDURE DIVISION.                                
           PERFORM A00100-READ-PARA.                      
           PERFORM A00200-UPDATE-PARA.                    
           PERFORM A00100-READ-PARA.                      
           PERFORM A00400-INSERT-PARA.                    
           PERFORM A00100-READ-PARA.                      
           PERFORM A00300-DELETE-PARA.                    
           PERFORM A00100-READ-PARA.                      
           STOP RUN.                                      
       A00100-READ-PARA.                                  
           DISPLAY ‘         A00100-READ-PARA.           ‘
           EXEC SQL                                       
               DECLARE CURREAD1 CURSOR FOR                      
                   SELECT NAME,SEQ FROM IBMGRP.MYNAM            
           END-EXEC.                                            
           EXEC SQL                                             
              OPEN CURREAD1                                     
           END-EXEC.                                            
           MOVE SQLCODE             TO WS-SQLCODE.              
           DISPLAY ‘SQLCODE OPEN  ‘ WS-SQLCODE.                 
           DISPLAY ‘****DATA FROM TABLE***’                     
           PERFORM UNTIL SQLCODE = 100                          
              EXEC SQL                                          
  &nb
sp;              FETCH CURREAD1 INTO :WS-NAME,:WS-SEQ           
              END-EXEC                                          
              MOVE SQLCODE             TO WS-SQLCODE            
              DISPLAY ‘SQLCODE FETCH ‘ WS-SQLCODE               
              IF SQLCODE = 0 THEN                               
               MOVE SPACES TO TEMP-MSG                          
               STRING                                           
               ‘NAME: ‘ DELIMITED BY SPACE ‘ ‘ DELIMITED BY SIZE
               WS-NAME DELIMITED BY SPACE ‘,’ DELIMITED BY SIZE 
               ‘SEQ#’ DELIMITED BY SPACE ‘ ‘ DELIMITED BY SIZE  
               WS-SEQ DELIMITED BY SIZE INTO TEMP-MSG           
               DISPLAY TEMP-MSG                                 
              END-IF                                            
           END-PERFORM.                                         
           DISPLAY ‘****END OF TABLE DATA****’                  
           EXEC SQL                                             
              CLOSE CURREAD1                                    
           END-EXEC.                                            
           MOVE SQLCODE             TO WS-SQLCODE.              
           DISPLAY ‘SQLCODE CLOSE ‘ WS-SQLCODE.                 
      *                                                         
       A00200-UPDATE-PARA.                                      
           DISPLAY ‘         A00200-UPDATE-PARA.         ‘      
           EXEC SQL                                             
               DECLARE CURUPDT1 CURSOR FOR                      
                   SELECT NAME,SEQ FROM IBMGRP.MYNAM            
                   WHERE SEQ = :WS-SEQ                      
                   FOR UPDATE OF NAME                       
           END-EXEC.                                        
           EXEC SQL                                         
              OPEN CURUPDT1       &nb
sp;                         
           END-EXEC.                                        
           MOVE SQLCODE             TO WS-SQLCODE.          
           DISPLAY ‘SQLCODE OPEN  ‘ WS-SQLCODE.             
           MOVE ‘002’               TO WS-SEQ.              
           EXEC SQL                                         
              FETCH CURUPDT1 INTO :WS-NAME,:WS-SEQ-TMP      
           END-EXEC                                         
           MOVE SQLCODE             TO WS-SQLCODE.          
           DISPLAY ‘SQLCODE FETCH ‘ WS-SQLCODE.             
           MOVE ‘SHYAM-KUMAR’       TO WS-NAME.             
           EXEC SQL                                         
              UPDATE IBMGRP.MYNAM                           
              SET NAME = :WS-NAME                           
              WHERE SEQ = :WS-SEQ                           
           END-EXEC.                                        
           MOVE SQLCODE             TO WS-SQLCODE.          
           DISPLAY ‘SQLCODE UPDT  ‘ WS-SQLCODE.             
           EXEC SQL                                         
              CLOSE CURUPDT1                                
           END-EXEC.                                        
           MOVE SQLCODE             TO WS-SQLCODE.          
           DISPLAY ‘SQLCODE CLOSE ‘ WS-SQLCODE.             
           EXIT.                                            
      *                                                     
       A00400-INSERT-PARA.                                  
           DISPLAY ‘         A00400-INSERT-PARA.         ‘  
           MOVE ‘TEMP-NAME’         TO WS-NAME.             
           MOVE 007                 TO WS-SEQ.              
           EXEC SQL                                         
               INSERT INTO IBMGRP.MYNAM                     
               VALUES ( :WS-SEQ,:WS-NAME)                   
     &nb
sp;     END-EXEC.                                      
           MOVE SQLCODE             TO WS-SQLCODE.        
           DISPLAY ‘SQLCODE INSRT ‘ WS-SQLCODE.           
           EXIT.                                          
      *                                                   
       A00300-DELETE-PARA.                                
           DISPLAY ‘         A00300-DELETE-PARA.         ‘
           EXEC SQL                                       
               DECLARE CURDELT1 CURSOR FOR                
                   SELECT NAME,SEQ FROM IBMGRP.MYNAM      
                   WHERE SEQ = :WS-SEQ                    
                   FOR UPDATE OF NAME                     
           END-EXEC.                                      
           EXEC SQL                                       
              OPEN CURDELT1                               
           END-EXEC.                                      
           MOVE SQLCODE             TO WS-SQLCODE.        
           DISPLAY ‘SQLCODE OPEN  ‘ WS-SQLCODE.           
           MOVE ‘007’               TO WS-SEQ.            
           EXEC SQL                                       
              FETCH CURDELT1 INTO :WS-NAME,:WS-SEQ-TMP    
           END-EXEC                                       
           MOVE SQLCODE             TO WS-SQLCODE.        
           DISPLAY ‘SQLCODE FETCH ‘ WS-SQLCODE.           
           EXEC SQL                                       
              DELETE FROM IBMGRP.MYNAM                    
              WHERE SEQ = :WS-SEQ                         
           END-EXEC.                                      
           MOVE SQLCODE             TO WS-SQLCODE.        
           DISPLAY ‘SQLCODE DELT  ‘ WS-SQLCODE.           
           EXEC SQL                                       
              CLOSE CURDELT1                              
           END-EXEC.                  
                    
           MOVE SQLCODE             TO WS-SQLCODE.        
           DISPLAY ‘SQLCODE CLOSE ‘ WS-SQLCODE.           
           EXIT.

Screen Shots

image

image

image

Please tryout the code yourself and let me know if you have any concerns.

2 thoughts on “CURSORS in DB2

Leave a Reply

Your email address will not be published. Required fields are marked *

five × 1 =