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?

score 3 · Accepted Answer · answered Jun 16 '15 at 18:57

3

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

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

answered Jun 16 '15 at 18:57

mustaccio

18,234
16
48
57

It works, but something strange is happening. If I use `EXECUTE IMMEDIATE` in a `BEGIN`-`END` block it creates the table correctly. However, if inside the `BEGIN`-`END` block I put the `EXECUTE IMMEDIATE` and the `INSERT` commands, I receive the following error: `"SCHEMA.test" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER 3.66.46`. I think maybe, for some reason, it is trying to execute the `INSERT` first. Doesn't make sense. – matheusr Jun 16 '15 at 19:40
Well, to compile the `INSERT` statement DB2 needs to access the catalog information of the table into which you are inserting, and that table obviously does not exist. To avoid this you'll need to execute both `CREATE` and `INSERT` dynamically. – mustaccio Jun 16 '15 at 20:43
That's it, thank you sir! I executed the `INSERT` command using `EXECUTE IMMEDIATE`. The final result is here http://pastebin.com/BNT9hVVd – matheusr Jun 16 '15 at 21:08

Executing DDL in compound SQL using DashDB (DB2)

1 Answers1