Introduction
- In COBOL database interface, programs interact with DB2 database.
- It includes various terms, such as embedded SQL, DB2 Application programming, host variables, SQLCA, SQL Queries, Cursors.
Embedded SQL
- Embedded SQL statements are used in COBOL programs to execute standard SQL operations.
- These statements are preprocessed by SQL processor before the application program is compiled.
- COBOL is called as Host Language. COBOL-DB2 applications are those applications which consists of both COBOL and DB2.
- It works like normal SQL statements with some small changes.
DB2 Application Programming
The following are the rules to be considered while coding a COBOL-DB2 program:- All the SQL statements should be delimited between EXEC SQL and END-EXEC.
- The SQL statement should be coded in Area B.
- All the tables are used in a program that should be declared in the Working-Storage Section using with the INCLUDE statement.
- NCLUDE and DECLARE TABLE must appear in the Procedure Division.
Host Variables
- Host variables are used for inserting or collecting a data from the table.
- It is important to declare host variables for all values passed between the program and DB2.
- Host variables are declared in the Working-Storage section.
- It cannot be group items, but it may be grouped together in host structure.
- Host variables cannot be redefined or renamed.
Syntax
Following syntax defines how to declare host variables and include a table in Working-Storage section:
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE table-name
END-EXEC.
EXEC SQL BEGIN DECLARE SECTION
END-EXEC.
01 EMPLOYEE-REC.
05 EMPLOYEE-ID PIC 9(4).
05 EMPLOYEE-NAME PIC X(25).
05 EMPLOYEE-ADDRESS X(50).
EXEC SQL END DECLARE SECTION
END-EXEC.
SQLCA
- SQLCA is known as a SQL Communication Area.
- It acts as a communication area between COBOL and DB2 to validate the SQLs execution.
- It is a communication area which DB2 passes the feedback of SQL execution to the program. It notifies the program, whether an execution was successful or not.
- SQLCA is used to verify the return code of the specific execution once SQL execution is finished.
- In SQLCA, there are a number of predefined variables like SQLCODE that contains the error code.
i. If the SQLCODE is 0(ZERO) then successfully executed the query.
ii. If the SQLCODE is Positive then query executed successfully, but with a warning.
iii. If SQLCODE is Negative then displays error condition.
Syntax
Following syntax defines how to declare an SQLCA in the Working-Storage section:
DATA DIVISION.
WORKING-STORAGE SECTION.
EXCE SQL
INCLUDE SQLCA
END-EXEC.
Cursors
- Cursor is a data structure which holds all the results of the query.
- It is defined in the procedure division or working-storage section.
- It is used to handle multiple rows selection at a time.
- Declare, Open, Close, Fetch are the operations associated with the cursor.
Declare cursor
- DECLARE statement is the first statement which is non-executable statement.
- The declaration can be done in the Working-Storage section or the Procedure Division.
EXEC SQL
DECLARE EMPCUR CURSOR FOR
SELECT EMPLOYEE-ID, EMPLOYEE-NAME, EMPLOYEE-ADDRESS FROM EMPLOYEE
WHERE EMPLOYEE-ID >: WS-EMPLOYEE-ID
END-EXEC.
DECLARE EMPCUR CURSOR FOR
SELECT EMPLOYEE-ID, EMPLOYEE-NAME, EMPLOYEE-ADDRESS FROM EMPLOYEE
WHERE EMPLOYEE-ID >: WS-EMPLOYEE-ID
END-EXEC.
Open
Open statement performs before using a cursor. It prepares the SELECT statement for execution.Syntax:
EXEC SQL
OPEN EMPCUR
END-EXEC.
OPEN EMPCUR
END-EXEC.
Close
Close statement is necessary to close a cursor before ending a program. It releases all the memory occupied by the cursor.Syntax:
EXEC SQL
CLOSE EMPCUR
END-EXEC.
CLOSE EMPCUR
END-EXEC.
Fetch
Fetch statement recognizes the cursor & puts the value in the INTO clause and coded in a loop.Syntax:
EXEC SQL
FETCH EMPCUR
INTO : WS-EMPLOYEE-ID, : WS-EMPLOYEE-NAME, WS-EMPLOYE-ADDRESS
END-EXEC.
FETCH EMPCUR
INTO : WS-EMPLOYEE-ID, : WS-EMPLOYEE-NAME, WS-EMPLOYE-ADDRESS
END-EXEC.
