matheusr matheusr - 4 months ago 30
SQL Question

Executing DDL in compound SQL using DashDB (DB2)

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

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

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