Db2 Statements in Cobol program

Integrating SQL statements into COBOL allows your program to interact with a DB2 database—such as querying, inserting, updating, or deleting data. Below is a step-by-step guide with detailed explanations and examples to understand how to code SQL statements in a COBOL + DB2 program.

Steps to Code SQL Statements in COBOL + DB2 Program -

Step1 - Identify the DB2 Table and Columns.

Before coding, identify the DB2 table and its columns to know what data you are interacting with.
Example table - Employees

CREATE TABLE Employees (
    Emp_ID     INTEGER PRIMARY KEY,
    Emp_Name   VARCHAR(50),
    Salary     DECIMAL(9,2),
    Hire_Date  DATE
);

Table: Employees and Columns: Emp_ID, Emp_Name, Salary, Hire_Date

Step2 - Include the SQLCA.

SQLCA is mandatory to receive the status of each SQL statement processed in the program.
Example -

EXEC SQL
	INCLUDE SQLCA
END-EXEC.

Step3 - Declare Host Variables in COBOL program.

Host variables act as placeholders for data exchanged between COBOL and DB2. These variables are declared in the WORKING-STORAGE SECTION and must match the DB2 column data types.

Example - COBOL Host Variable Declarations for Employee table

 WORKING-STORAGE SECTION.
 ...
 01 WS-EMP.
    05 WS-EMP-ID     PIC 9(5).   * Host variable for Employee ID (INTEGER)
    05 WS-EMP-NAME   PIC X(50).  * Host variable for Employee Name (VARCHAR)
    05 WS-SALARY     PIC 9(7)V99 COMP-3. * Host variable for Salary (DECIMAL)
    05 WS-HIRE-DATE  PIC X(10).  * Host variable for Hire Date 
	                             (DATE in YYYY-MM-DD format)
 ...

Step4 - Write SQL Statements in COBOL.

SQL statements are embedded in COBOL between EXEC SQL and END-EXEC. Host variables are prefixed with a colon (:) inside SQL statements.

Example1 - SELECT Statement in COBOL

 ...
MOVE 101 TO WS-EMP-ID.

EXEC SQL
    SELECT Emp_Name, Salary
    INTO :WS-EMP-NAME, :WS-SALARY
    FROM Employees
    WHERE Emp_ID = :WS-EMP-ID
END-EXEC.
 ...

The SELECT query retrieves the name and salary for the employee with ID 101.

Step5 - Handle Errors with SQLCODE and SQLSTATE.

After each SQL statement, it's important to check SQLCODE to determine if the operation was successful.

• SQLCODE = 0: Success
• SQLCODE = 100: No data found.
• SQLCODE < 0: An error occurred.

Error Handling Example: -

 ...
IF SQLCODE = 0
    DISPLAY 'Operation Successful.'
ELSE 
	IF SQLCODE = 100
		DISPLAY 'No Data Found.'
	ELSE
		DISPLAY 'SQL Error Occurred. SQLCODE: ' SQLCODE
	END-IF 
END-IF.
 ...

Step6 - Compile, Bind, and Execute the COBOL + DB2 Program

After each SQL statement, it's important to check SQLCODE to determine if the operation was successful.

• Compile the COBOL Program: Use the precompiler to compile our COBOL program (explained in next chapter).
• Bind the DBRM (Database Request Module): The compiler generates a DBRM. Use the DB2 bind utility to bind it to a plan or package (explained in next chapter).
• Execute the Program: Submit the batch job or run the program directly from the mainframe environment (explained in next chapter).