EXECUTE IMMEDIATE
From SQL PL Guide for DB2
EXECUTE IMMEDIATE statement
The EXECUTE IMMEDIATE statement is performing the PREPARE and EXECUTE of a dynamic statement in a single request. Typically the EXECUTE IMMEDIATE will be used when the statement will executed just once or infrequently. For multiple executions you should use PREPARE and EXECUTE as two statements. The SQL statement is stored in a varchar variable.
Using a select or values statement in the execute immediate statement is not allowed. For this statements you've to prepare the select. Then use OPEN, FETCH, CLOSE. An example you will find here.
Syntax:
EXECUTE IMMEDIATE <sql-statement>
The following statements are supported as dynamic statements in the EXECUTE IMMEDIATE statement: The statement string must be one of the following SQL statements:
- ALTER - SAVEPOINT - CALL - SET CURRENT DEFAULT TRANSFORM GROUP - COMMENT - SET CURRENT DEGREE - COMMIT - SET CURRENT EXPLAIN MODE - CREATE - SET CURRENT EXPLAIN SNAPSHOT - DECLARE GLOBAL TEMPORARY TABLE - SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION - DELETE - SET CURRENT QUERY OPTIMIZATION - DROP - SET CURRENT REFRESH AGE - GRANT - SET ENCRYPTION PASSWORD - INSERT - SET EVENT MONITOR STATE - LOCK TABLE - SET INTEGRITY - REFRESH TABLE - SET PASSTHRU - RELEASE SAVEPOINT - SET PATH - RENAME TABLE - SET SCHEMA - RENAME TABLESPACE - SET SERVER OPTION - REVOKE - UPDATE - ROLLBACK
Example:
create procedure DUPLICATE_TAB (in v_old_tabschema varchar(128), in v_old_tabname varchar(128),
in v_new_tabschema varchar(128), in v_new_tabname varchar(128))
begin
declare dyn_stmt varchar(1024);
set dyn_stmt = 'create table ' concat v_new_tabschema concat '.' concat v_new_tabname
concat ' like ' concat v_old_tabschema concat '.' concat v_old_tabname ;
execute immediate dyn_stmt;
set dyn_stmt = 'insert into ' concat v_new_tabschema concat '.' concat v_new_tabname
concat ' select * from ' concat v_old_tabschema concat '.' concat v_old_tabname ;
execute immediate dyn_stmt;
end!