The DELETE statement is used to remove data from a table. This command is essential when we need to delete outdated or unwanted data or remove specific rows based on conditions. We can specify which rows to delete using a WHERE clause or remove all rows if no conditions are provided.

DELETE statement is widely used to manage data cleanup, remove records that are no longer needed, and maintain data integrity. In mainframe COBOL programs, the DELETE statement can be embedded, allowing applications to delete data from DB2 tables as part of program execution.

Basic Components:

• Target Table: Specifies the table from which data will be deleted.
• Conditions (WHERE clause): Optional; defines the rows to delete. If omitted, all rows in the table are removed.

Syntax - The basic syntax for a DELETE statement in DB2 is as follows:

DELETE FROM table_name
WHERE condition;

• table_name: The name of the table from which data will be deleted.
• WHERE: An optional clause that specifies conditions to limit the rows to be deleted. If omitted, all rows in the table are deleted.

Examples - Delete all employees in the SALES department from the EMPLOYEE table.

DELETE FROM EMPLOYEE
WHERE DEPARTMENT = 'SALES';

• EMPLOYEE: Specifies the table from which rows will be deleted.
• WHERE DEPARTMENT = 'SALES': Deletes only the rows where the DEPARTMENT is SALES.

Using DELETE Statements in a COBOL Program

In a COBOL + DB2 program, the DELETE statement is embedded using EXEC SQL ... END-EXEC. Host variables, which are COBOL variables, can be used to dynamically specify values in the DELETE statement.

Examples - Embedding a DELETE Statement in COBOL

EXEC SQL
	DELETE FROM EMPLOYEE
	WHERE EMP_ID = :EMP-ID
END-EXEC.

• EXEC SQL and END-EXEC delimit the SQL command within the COBOL code.
• WHERE EMP_ID = :EMP-ID: Deletes the row where EMP_ID matches the value in the COBOL host variable EMP-ID.

Steps to Code a DELETE Statement in a COBOL Program

1. Declare Host Variables in the WORKING-STORAGE Section: Include the host variables DCLGEN or declare the host variables in WORKING-STORAGE SECTION that match the data types of the columns in the DB2 table.
2. Set values for Host variables: Assign a value to the host variable in the PROCEDURE DIVISION, which will be used in the DELETE statement.
3. Write the Embedded SQL DELETE Statement: Use EXEC SQL ... END-EXEC to write the DELETE statement, referencing the COBOL host variable.
4. Execute the DELETE query to delete the data.
5. Check for SQL Errors: After executing the SQL statement, check the SQLCODE in the SQLCA (SQL Communication Area) to ensure that the operation was successful. A SQLCODE of zero indicates success.

Error Handling for the DELETE Statement in COBOL Program

DB2 uses SQLCODE and SQLSTATE values to indicate the result of SQL operations. The SQLCA is populated with these details, allowing COBOL programs to handle errors effectively.

Common SQLCODE Values for DELETE:

• 0: Successful execution.
• 100: No rows found that match the condition (useful when you expect a row to be deleted).
• Negative Values: Indicate various errors (e.g., -532 for referential integrity constraint violation).

Examples - Error Handling in a COBOL Program

EXEC SQL
	DELETE FROM EMPLOYEE
	WHERE EMP_ID = :EMP-ID
END-EXEC.

IF SQLCODE = 100
	DISPLAY 'No matching data found for deletion'
ELSE 
	IF SQLCODE < 0
		DISPLAY 'SQL Error: ' SQLCODE
	END-IF 
END-IF.

• SQLCODE = 100: Indicates that no rows met the WHERE condition, so no rows were deleted.
• SQLCODE < 0: Catches other SQL errors and displays the SQLCODE for troubleshooting. The STOP RUN command terminates the program for severe errors.

---