Executing DDL in compound SQL using DashDB (DB2)

Question

I need to execute a DDL command (CREATE TABLE) with other SQL commands. See the code snippet below:

CREATE TABLE test AS
(
    SELECT duration AS NUM1
    FROM event 
    WHERE duration IS NOT NULL
) WITH NO DATA;

INSERT INTO test (   
    SELECT duration AS NUM1
    FROM  event 
    WHERE event_duration_tech IS NOT NULL   
);

I am creating a table, then populating it.

If I send this code via JDBC, it does not work due to a statement terminator (;) error.

If I wrap it with BEGIN and END to create a compound SQL block, it does not work because DB2 does not allow DDL commands on compound SQL blocks.

The thing is, I need to execute both commands in one shot. Any ideas?

Answer 1

You need to use dynamic SQL to execute some DDL statements:

EXECUTE IMMEDIATE 'CREATE TABLE test AS (SELECT...'