DB2 CURSORS

Using CURSORs in the COBOL programs.

In the part 1, we have seen how to access data using
singleton select statement. using singleton select
we can only select one record from the table. 


When there is a situation, like.. program needs to 
get all data in EMPLOYEE table and print all 
employee names and their respective salaries. 
With singleton select statement (refer part1) it is 
not possible.


Using CURSORs we can get all records in the table. 
Let us see step by step how we can do this in a 
cobol program.

TASK :  Get all records from EMPLOYEE table and
        display EMPNAME and SALARY in the spool


STEP 1.  DECLARE cursor in WORKING-STORAGE SECTION.

         EXEC SQL
              DECLARE EMPCUR CURSOR FOR
                SELECT EMPNAME, SALARY
                  FROM EMPLOYEE
         END-EXEC.


STEP 2.  Open the cursor in PROCEDURE division.

         EXEC SQL

               OPEN EMPCUR

         END-EXEC.

         At this time, sql specified in the step1
         executes and cursor is ready with data.


STEP 3.  Get data from cursor record by record.

         Following code should be in a loop till 
         we get SQLCODE = 100. Let us assume there
         are 5 records in EMPLOYEE table, every time
         when you execute this code, we will get a 
         record.  6th time when we execute the code
         we will get SQLCODE = 100, we can end the loop.


         fetching/retrieving data from cursor.

         EXEC SQL
              FETCH EMPCUR
               INTO :HV-EMPNAME,
                    :HV-SALARY
         END-EXEC.

         EVALUATE SQLCODE
            WHEN  0

                  DISPLAY  ' -----------------'
                  DISPLAY  ' Employee name   '  HV-EMPNAME
                  DISPLAY  ' Employee salary '  HV-SALARY
                  DISPLAY  ' -----------------'
                  
            WHEN 100

                 MOVE 'Y'     TO END-OF-LOOP

            WHEN OTHER
 
                  DISPLAY ' ERROR IN RETRIEVING THE DATA FROM EMPLOYEE TABLE'
                  DISPLAY 'SQLCODE : ' SQLCODE

           END-EVALUATE.



           TIP : SQLCODE = 100 is end of the cursor, in case of cursors.
                 SQLCODE = 100 is record not found in case of singleton select



STEP 4.   Once retrieving records from table completed, we need to 
          close the cursor using following command.

          EXEC SQL
               CLOSE CURSOR
          END-EXEC.



Other info about cursors....

 Your program can have several cursors. Each cursor requires its own:
 DECLARE CURSOR statement to define the cursor
 OPEN and CLOSE statements to open and close the cursor
 FETCH tatement to retrieve rows from the cursor's result table.

 You can use cursors to fetch, update, or delete a row of a table,
 but you cannot use them to insert a row into a table.





Also visit.....

COBOL DB2 PROGRAMMING - Primary
SAMPLE COBOL DB2 COMPILE JCL 
SAMPLE COBOL DB2 RUN JCL