What is a UPDATE Statement?

The UPDATE statement modifies data in one or more columns of existing rows in a DB2 table. It can change specific column's values based on conditions or update all rows in a table if no conditions are specified. It can be applied conditionally (to specific rows that match a condition) or unconditionally (to all rows in the table). When used in a COBOL program, the UPDATE statement allows applications to dynamically modify data in DB2 tables.

Basic Components:

• Target Table: Specifies the table containing the data to be updated.
• Columns: The specific columns to modify.
• Values: New values to set for each specified column.
• Conditions (WHERE clause): Optional conditions to identify which rows to update.

Syntax - The basic syntax for an UPDATE statement in DB2 is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

• table_name: The name of the table where data will be updated.
• column1, column2, ...: Columns to update with new values.
• value1, value2, ...: The new values to assign to each specified column.
• WHERE: Optional clause specifying the conditions for updating only matching rows. If omitted, all rows in the table are updated.

Examples - Update the salary of employees in the SALES department by 10%.

UPDATE EMPLOYEE
SET SALARY = SALARY * 1.1
WHERE DEPARTMENT = 'SALES';

• EMPLOYEE: Specifies the table to update.
• SALARY: Increases the SALARY column by 10%.
• WHERE DEPARTMENT = 'SALES': Applies the update only to employees in the SALES department.

Using UPDATE Statements in a COBOL Program

In a COBOL + DB2 program, the UPDATE statement is embedded using EXEC SQL ... END-EXEC. COBOL host variables are used to pass values dynamically to the SQL statement, allowing data updates within the program's flow.

Examples - Embedding an UPDATE Statement in COBOL

EXEC SQL
	UPDATE EMPLOYEE
	SET SALARY = :NEW-SALARY
	WHERE EMP_ID = :EMP-ID
END-EXEC.

• EXEC SQL and END-EXEC enclose the embedded SQL statement in COBOL.
• SET SALARY = :NEW-SALARY: Updates the SALARY column with the value in the COBOL host variable NEW-SALARY.
• WHERE EMP_ID = :EMP-ID: Applies the update only to the row where EMP_ID matches the value in the host variable EMP-ID.

Steps to Code an UPDATE 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 values to the host variables in the PROCEDURE DIVISION, which will be used in the UPDATE statement.
3. Write the Embedded SQL UPDATE Statement: Use EXEC SQL ... END-EXEC to write the UPDATE statement, referencing the COBOL host variables.
4. Execute the UPDATE query to update 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.

Error Handling for the UPDATE Statement in COBOL Program

DB2 uses SQLCODE and SQLSTATE values to indicate the outcome of SQL operations. The SQL Communication Area (SQLCA) holds this information, allowing COBOL programs to handle errors appropriately.

Common SQLCODE Values for UPDATE:

• 0: Successful execution.
• 100: No rows found that match the condition (useful when a row is expected to be updated).
• Negative Values: Indicate various errors (e.g., -803 for duplicate key violations if there is a unique constraint).

Examples - Error Handling in a COBOL Program

EXEC SQL
	UPDATE EMPLOYEE
	SET SALARY = :NEW-SALARY
	WHERE EMP_ID = :EMP-ID
END-EXEC.

IF SQLCODE = 100
	DISPLAY 'No matching data found for the update'
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 updated.
• SQLCODE < 0: Handles other SQL errors, displaying the SQLCODE for troubleshooting. STOP RUN halts the program for severe errors.

---