What is the difference between EXECUTE IMMEDIATE and EXECUTE WITH PREPARE in DB2?

The EXECUTE IMMEDIATE and EXECUTE PREPARE are the forms of dynamic SQL. In case of EXECUTE immediate, we can give the SQL statement in the host variable and pass this host variable in EXECUTE IMMEDIATE.

Following example demonstrates these forms.

Example

01 WS-SQL-DECLARE
   05 WS-SQL-LEN   PIC S9(04) COMP.
   05 WS-SQL-QUERY   PIC X(70).

MOVE +80 TO WS-SQL-LEN
MOVE “UPDATE ORDERS SET ORDER_PAID = ‘YES’ WHERE ORDER_DATE = ‘14-08-2020’” TO WS-SQL-QUERY

EXEC SQL
   EXECUTE IMMEDIATE :WS-SQL-DECLARE
END-EXEC

In case of EXECUTE PREPARE, the SQL statement is first prepared and then executed. We can use this form of dynamic SQL as shown below.

Example

01 WS-SQL-DECLARE
05 WS-SQL-LEN   PIC S9(04) COMP.
05 WS-SQL-QUERY   PIC X(70).

MOVE +80 TO WS-SQL-LEN
MOVE “UPDATE ORDERS SET ORDER_PAID = ‘YES’ WHERE ORDER_DATE = ‘14-08-2020’” TO WS-SQL-QUERY

EXEC SQL
   PREPARE RUNFORM FROM :WS-SQL-DECLARE
END-EXEC

EXEC SQL
   EXECUTE RUNFORM
END-EXEC