IBM MAINFRAME GURU Forum
Online Tutorials 
MainfameGurukul - Discussion Forum 



DRONA SERIES
DB2
COBOL DB2 PROGRAMMING - 1



   
 

Writing a COBOL DB2 Program.

Let us assume we are writing a cobol program to read EMPLOYEE
table and get the details of employee with the name XXXXXXX.

Let us go in step wise.. 

create the following table in db2 or assume it is there in db2 database.


EMPLYEE

EMPID   EMPNAME     DEPARTMENT   SALARY   DESIGNATION
1000    XXXXXXX     XX	         10000    SE
1001    YYYYYYY     YY            9000    SE
1002    ZZZZZZZ     ZZ           20000    MA 





STEP 1.   We need to declare the table structure in the 
          WORKING-STORAGE SECTION or LINKAGE SECTION.

          EXEC SQL
               DECLARE DSNXXX.EMPLOYEE
                       ( EMPID          CHAR(10)   	NOT NULL,
                         EMPNAME        CHAR(30)   	NOT NULL,
                         DEPARTMENT     CHAR(2)    	NOT NULL,
                         SALARY         DECIMAL(10,2)   NOT NULL,
                         DESIGNATION    CHAR(4)    	NOT NULL  )           
          END-EXEC.


          we can use DB2 tool called DCLGEN to generate this declaration 
          for us and can include that copy book here.

          if you create a copybook using DCLGEN. Use following sntax to include 
      

           EXEC SQL
                INCLUDE  < copybookname >
           END-EXEC.




STEP 2.   Declare host variables in WORKING-STORAGE SECTION.

          HOST VARIABLES - A host variable is a data item declared in cobol to use
                           it in embedded SQL.  

          For EMPLOYEE table, host variable declaration is look like as follows...

          01  EMPOYEE-RECORD.
              05  HV-EMPID		PIC X(10).
              05  HV-EMPNAME		PIC X(30).
              05  HV-DEPARTMENT         PIC X(2).
              05  HV-SALARY             PIC S9(8)V99 COMP-3.
              05  HV-DESIGNATION        PIC CHAR(4).


         If you use db2 tool DCLGEN, it will automatically creates this structure also
         along with table declaration specified in step1.


STEP 3.  Include SQLCA as follows in WORKING-STORAGE SECTION.

         EXEC SQL
              INCLUDE SQLCA
         END-EXEC.

         What is SQLCA?

         SQLCA  - SQL communication area. 
                  When a SQL statement executes, DB2 places a value in SQLCODE AND 
		  SQLSTATE host variables or any other fields of SQLCA.  based on 
                  the values in these variables we can know whether sql ran 
                  sucessfully or not. 

                  SQLCA contains a declartion of fields like SQLCODE,SQLSTATE and 
                  SQLERRD  etc....



STEP 4.  Add a sql statement in procdure division to get the details of employee
                with the name XXXXXXX.



         DISPLAY ' PROGRAM STARTED .... '
 
         .........
 
         EXEC SQL
              SELECT   SALARY  
                 INTO  :HV-SALARY
              FROM EMPLOYEE
              WHERE EMPNAME = 'XXXXXXX'
         END-EXEC.

         IF SQLCODE = 0

              DISPLAY ' SQL EXECUTED SUCESSFULLY '
              DISPLAY ' EMPLOYEE SALARY IS       '   HV-SALARY 

         ELSE

              DISPLAY ' SQL FAILED '
              DIAPLY  ' SQL CODE   '  SQLCODE

         END-IF.

          ....
          ....

         DISPLAY ' PROGRAM ENDED'.
             

 
Here SQLCODE = 0 means, sql ran sucessfully without any issues. Hence
we are displaying the HV-SALARY into the spool.

If SQLCODE NOT = 0 , there is issue in executing the sql statement.

     

Now we have compeleted coding a cobol-db2 program. our next step is to
compile the program.


SAMPLE COMPILE JCL

Click here to see the compile JCL. Use this compile jcl to compile the program.


SAMPLE RUN JCL 
Click here to see the sample run jcl. Use this run jcl to run the program.


               

	
    



                                   









Home | Donations | Online Tutorials | Books | Entertainment | Contactme | privacy | sql tutorial  | db2 interview questions  | simple jcl tutorial | DB2 INTERVIEW QUESTIONS  | JCL INTERVIEW QUESTIONS
1