Mainframe Tutorial
JCL Tutorial
Introduction
Mainframe Interview Q&A
Mainframe Quiz
Mainframe Memes
Contact us
About usModule 14:- Database Interface
Backward
SQL Queries
We already studied how we can use SQL statements in our program. In this section let’s see how few popular SQL statements like SELECT, INSERT, UDPATE DELETE can be used in our COBOL-DB2 Program
COBOL- DB2 SELECT EXAMPLE
Let’s assume there is one table EMP containing below records:-
| EMP_ID | EMP_NAME |
|---|---|
| 10000 | ANKIT |
| 20000 | STEVE |
COBOL program to display Employee name in SYSYOUT for EMP-ID=’20000’:-
IDENTIFICATION DIVISION.
PROGRAM-ID. SQLSEL.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-EMPL.
05 WS-EMPL-ID PIC X(05).
05 WS-EMPL-NAME PIC X(15).
******** INCLUDING SQLCA COPYBOOK **************
EXEC SQL
INCLUDE SQLCA
END-EXEC.
******** INCLUDING DCLGEN OF EMP TABLE*********
EXEC SQL
INCLUDE DCLEMP
END-EXEC.
PROCEDURE DIVISION.
MAIN-PARA.
EXEC SQL
SELECT EMP_NAME INTO :WS-EMPL-NAME
FROM EMP
WHERE EMP_ID = '20000'
END-EXEC.
EVALUATE SQLCODE
WHEN ZERO
DISPLAY 'EMPLOYEE NAME: ' WS-EMPL-NAME
WHEN OTHER
DISPLAY 'ERROR EXECUTING SELECT'
END-EVALUATE.
STOP RUN.
Mainframe Job Step to execute above COBOL-DB2 program:-
//STEP01 EXEC PGM=IKJEFT01
//STEPLIB DD DSN=USER.TEST.LOADLIB,DISP=SHR
//SYSOUT DD SYSOUT=*
//SYSIN DD *
DSN SYSTEM(SSID)
RUN PROGRAM(SQLSEL) PLAN(SQLSEL)
END
/*
SYSOUT Display after successful execution above job:-
EMPLOYEE NAME: STEVE
COBOL- DB2 UPDATE EXAMPLE
Let’s assume there is one table EMP containing below records:-
| EMP_ID | EMP_NAME |
|---|---|
| 10000 | ANKIT |
| 20000 | STEVE |
COBOL program to UPDATE Employee name to ‘RAMESH’ in EMP Table for EMP-ID=’20000’:-
IDENTIFICATION DIVISION.
PROGRAM-ID. SQLUPD.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-EMPL.
05 WS-EMPL-ID PIC X(05).
05 WS-EMPL-NAME PIC X(15).
******** INCLUDING SQLCA COPYBOOK **************
EXEC SQL
INCLUDE SQLCA
END-EXEC.
******** INCLUDING DCLGEN OF EMP TABLE*********
EXEC SQL
INCLUDE DCLEMP
END-EXEC.
PROCEDURE DIVISION.
MAIN-PARA.
MOVE 'RAMESH' TO WS-EMPL-NAME
EXEC SQL
UPDATE EMP
SET EMP_NAME = :WS-EMPL-NAME
WHERE EMP_ID = '20000'
END-EXEC.
EVALUATE SQLCODE
WHEN ZERO
DISPLAY 'NEW NAME UPDATED SUCCESSFULLY'
WHEN OTHER
DISPLAY 'ERROR EXECUTING UPDATE'
END-EVALUATE.
STOP RUN.
Mainframe Job Step to execute above COBOL-DB2 program:-
//STEP01 EXEC PGM=IKJEFT01
//STEPLIB DD DSN=USER.TEST.LOADLIB,DISP=SHR
//SYSOUT DD SYSOUT=*
//SYSIN DD *
DSN SYSTEM(SSID)
RUN PROGRAM(SQLUPD) PLAN(SQLUPD)
END
/*
SYSOUT Display after successful execution above job:-
NEW NAME UPDATED SUCCESSFULLY
Table view after Job execution:-
| EMP_ID | EMP_NAME |
|---|---|
| 10000 | ANKIT |
| 20000 | RAMESH |
COBOL- DB2 INSERT EXAMPLE
Let’s assume there is one table EMP containing below records:-
| EMP_ID | EMP_NAME |
|---|---|
| 10000 | ANKIT |
| 20000 | RAMESH |
COBOL program to Insert Record into EMP table for Employee whose EMP_ID is ‘30000’ and name is ‘BHAVIN’:-
IDENTIFICATION DIVISION.
PROGRAM-ID. SQLINS.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-EMPL.
05 WS-EMPL-ID PIC X(05).
05 WS-EMPL-NAME PIC X(15).
******** INCLUDING SQLCA COPYBOOK **************
EXEC SQL
INCLUDE SQLCA
END-EXEC.
******** INCLUDING DCLGEN OF EMP TABLE*********
EXEC SQL
INCLUDE DCLEMP
END-EXEC.
PROCEDURE DIVISION.
MAIN-PARA.
MOVE '30000' TO WS-EMPL-ID
MOVE 'BHAVIN' TO WS-EMPL-NAME
EXEC SQL
INSERT INTO EMP
(EMP_ID, EMP_NAME)
VALUES
(:WS-EMPL-ID, :WS-EMPL-NAME)
END-EXEC.
EVALUATE SQLCODE
WHEN ZERO
DISPLAY 'NEW REFCORD INSERTED SUCCESSFULLY'
WHEN OTHER
DISPLAY 'ERROR EXECUTING INSERT'
END-EVALUATE.
STOP RUN.
Mainframe Job Step to execute above COBOL-DB2 program:-
//STEP01 EXEC PGM=IKJEFT01
//STEPLIB DD DSN=USER.TEST.LOADLIB,DISP=SHR
//SYSOUT DD SYSOUT=*
//SYSIN DD *
DSN SYSTEM(SSID)
RUN PROGRAM(SQLINS) PLAN(SQLINS)
END
/*
SYSOUT Display after successful execution above job: -
NEW REFCORD INSERTED SUCCESSFULLY
Table view after Job execution: -
| EMP_ID | EMP_NAME |
|---|---|
| 10000 | ANKIT |
| 20000 | RAMESH |
| 30000 | BHAVIN |
COBOL- DB2 DELETE EXAMPLE
Let’s assume there is one table EMP containing below records:-
| EMP_ID | EMP_NAME |
|---|---|
| 10000 | ANKIT |
| 20000 | RAMESH |
| 30000 | BHAVIN |
COBOL program to Delete record from EMP table for Employee whose EMP_ID is ‘10000’ :-
IDENTIFICATION DIVISION.
PROGRAM-ID. SQLDEL.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-EMPL.
05 WS-EMPL-ID PIC X(05).
05 WS-EMPL-NAME PIC X(15).
******** INCLUDING SQLCA COPYBOOK **************
EXEC SQL
INCLUDE SQLCA
END-EXEC.
******** INCLUDING DCLGEN OF EMP TABLE*********
EXEC SQL
INCLUDE DCLEMP
END-EXEC.
PROCEDURE DIVISION.
MAIN-PARA.
MOVE '10000' TO WS-EMPL-ID
EXEC SQL
DELETE FROM EMP
WHERE EMP_ID = :WS-EMPL-ID
END-EXEC.
EVALUATE SQLCODE
WHEN ZERO
DISPLAY 'RECORD DELETED SUCCESSFULLY'
WHEN OTHER
DISPLAY 'ERROR EXECUTING DELETE'
END-EVALUATE.
STOP RUN.
Mainframe Job Step to execute above COBOL-DB2 program:-
//STEP01 EXEC PGM=IKJEFT01
//STEPLIB DD DSN=USER.TEST.LOADLIB,DISP=SHR
//SYSOUT DD SYSOUT=*
//SYSIN DD *
DSN SYSTEM(SSID)
RUN PROGRAM(SQLDEL) PLAN(SQLDEL)
END
/*
SYSOUT Display after successful execution above job: -
RECORD DELETED SUCCESSFULLY
Table view after Job execution: -
| EMP_ID | EMP_NAME |
|---|---|
| 10000 | ANKIT |
| 20000 | RAMESH |