SQL (Structured Query Language) is used to retrieve the data from the database (DB2). In order to retrieve the data from DB2 in the program, SQL queries needs to be coded in the program. The SQL queries should needs to code in between EXEC SQL and END-EXEC.
If the SQL queries are not coded in between EXEC SQL and END-EXEC, COBOL compiler will treat them as COBOL statements and tries to recognize and will throw the compilation error. Coding SQL queries are mandatory to retrieve the data from database. So let’s discuss about what all the statements types in SQL are and then how it will be used in COBOL program.
Unlike a normal COBOL program COBOL DB2 program has some special requirements to be considered while coding and execution. For ex: In the coding it is required to include the SQLCA and the program needs to be executed via Terminal monitor Program etc.
You can code SQL statements in certain COBOL program sections. the allowable sections are shown in the following table.
| SQL statement | Program section |
|---|---|
| BEGIN DECLARE SECTION | WORKING-STORAGE SECTION1 or LINKAGE SECTION |
| INCLUDE SQLCA | WORKING-STORAGE SECTION1 or LINKAGE SECTION |
| INCLUDE text-file-name | PROCEDURE DIVISION or DATA DIVISION2 |
| DECLARE TABLE | DATA DIVISION or PROCEDURE DIVISION |
| DECLARE VARIABLE | WORKING-STORAGE SECTION1 |
| Other | PROCEDURE DIVISION |
If you use the Db2 coprocessor, you can use the LOCAL-STORAGE SECTION wherever WORKING-STORAGE SECTION is listed in the table. When including host variable declarations, the INCLUDE statement must be in the WORKING-STORAGE SECTION or the LINKAGE SECTION.
You cannot put SQL statements in the DECLARATIVES section of a COBOL program.
Each SQL statement in a COBOL program must begin with EXEC SQL and end with END-EXEC. If you are using the Db2 precompiler, the EXEC and SQL keywords must appear on one line, but the remainder of the statement can appear on subsequent lines. If you are using the Db2 coprocessor, the EXEC and SQL keywords can be on different lines. Do not include any tokens between the two keywords EXEC and SQL except for COBOL comments, including debugging lines. Do not include SQL comments between the keywords EXEC and SQL.
If the SQL statement appears between two COBOL statements, the period after END-EXEC is optional and might not be appropriate. If the statement appears in an IF…THEN set of COBOL statements, omit the ending period to avoid inadvertently ending the IF statement. You might code an UPDATE statement in a COBOL program as follows -
EXEC SQL
UPDATE DSN8C10.DEPT
SET MGRNO = :MGR-NUM
WHERE DEPTNO = :INT-DEPT
END-EXEC.
SQL has three types of statements mainly.
DDL - Data Definition Language statements.
DML - Data Manipulation Language statements.
TCL - Transaction Control Language Statements.
DCL - Data Control Language statements.
DDL - Data Definition Language statements:
Data Definition Language is used to define (CREATE), alters (ALTER) and deletes (DROP) the database objects. Normally these DDL statements will be handled by DBA. The database objects include schemas, tables, views, sequences, catalogs, indexes, and aliases. The main DDL statements are, CREATE, ALTER, DROP.
DML – Data Manipulation Language:
Data Manipulation language used to manipulate the data inside the table. DML also uses to control the data inside the database table. The main data manipulation statements are, SELECT, INSERT, UPDATE, DELETE.
TCL: Transaction Control Language:
Transaction control language is used to control the transactions performed on the database. TCL can save or revoke the transactions applied on the database from SYNC point. SYNC point is nothing but where thee COMMIT or ROLLBACK executed. TCL can have two statements, COMMIT, ROLLBACK.
DCL: Data Control language:
Data Control language is used for controlling of data by giving/revoking the access to retrieve the data based on the levels at users group level or individual user level. One of the main advantages of DCL is, User groups can be restricted by revoking the access for the sensitive data. DCL has mainly two statements, GRANT, REVOKE.
COBOL DB2 program will be executed thru a terminal interface utility IKJEFT01.
Example
//STEP1 EXEC PGM=IKJEFT01,REGION=0M
//SYSPRINT DD SYSOUT=*
//STEPLIB DD DSN=MGR.TEST.LOADLIB,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSABOUT DD SYSOUT=*
//SYSDBOUT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DT10)
RUN PROGRAM (MGLIST1) PLAN(PLAN1) PARMS('xxxx')
END
/