Let us write a cobol program to read the EMPLOYEE table and get the details of the employee with name 'RYAN'.
I am assuming the table is already created in the user database like below:.
EMPLOYEE
EMPID EMPNAME DEPARTMENT SALARY DESIGNATION
1000 XXXXXXX XX 10000 SE
1001 YYYYYYY YY 9000 SE
1002 ZZZZZZZ ZZ 20000 TL
STEP1: We need to declare the Table structure in the Working Storage section. Ideally we should DCLGEN Tool to generate the structure for our DB2 table. The option to go to our DCLGEN tool depends on the ISPF settings. Generally it can be invoked using 'D' option on the ispf menu to display DB2I Default pannel.
DCLGEN Screen |
*****************************************************************This DCLGEN needs to be included into the Working Storage Section of our cobol program in the following way:
EXEC SQL DECLARE DEV.EMPLOYEE TABLE
( 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.
*************** COBOL DECLARATION FOR TABLE DEV.EMPLOYEE *********
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).
*********** THE NUMBER OF COLUMNS IN THIS DECLARATION IS 5 *****
EXEC SQLAlso, the most important copybook SQLCA needs to be included . Apart from this we wont be able to capture the SQL Return codes
INCLUDE EMPLOYEE
END-EXEC.
EXEC SQLAlso since our query in the program might return more than single row, we need cursors in our program. Read About cursor programming here
INCLUDE SQLCA
END-EXEC.
I am not going into the details of cursor programming here, since those are there in other posts.
Once the program is ready and compiled , we need to bind it to a plan in the test region. Once the bind is successful, we can run the program using the IKJEFT01 utility as below.
cobol db2 run jcl |