|
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.
|
|