Use the PREPARE and EXECUTE statements

If non-SELECT statements contain no parameter markers, they can be run dynamically using the EXECUTE IMMEDIATE statement. However, if the non-SELECT statements have parameter markers, they must be run using PREPARE and EXECUTE.

The PREPARE statement prepares the non-SELECT statement (for example, the DELETE statement) and gives it a statement name you choose. If DLYPRP (*YES) is specified on the CRTSQLxxx command, the preparation is delayed until the first time the statement is used in an EXECUTE or DESCRIBE statement, unless the USING clause is specified on the PREPARE statement. After the statement has been prepared, it can be run many times within the same program, using different values for the parameter markers. The following example is of a prepared statement being run multiple times:

  DSTRING = 'DELETE FROM CORPDATA.EMPLOYEE WHERE EMPNO = ?';
 
  /*The ? is a parameter marker which denotes
    that this value is a host variable that is
    to be substituted each time the statement is run.*/
 
  EXEC SQL PREPARE S1 FROM :DSTRING;
 
  /*DSTRING is the delete statement that the PREPARE statement is
    naming S1.*/
 
   DO UNTIL (EMP =0);
  /*The application program reads a value for EMP from the
    display station.*/
     EXEC SQL
       EXECUTE S1 USING :EMP;
 
 
  END;

In routines similar to the example above, you must know the number of parameter markers and their data types, because the host variables that provide the input data are declared when the program is being written.

Note: All prepared statements that are associated with an application server are destroyed whenever the connection to the application server ends. Connections are ended by a CONNECT (Type 1) statement, a DISCONNECT statement, or a RELEASE followed by a successful COMMIT.