Hi all,

I'm new in using DB2 and I have question on creating/ altering table.

I created a file (DDL) with SQL statements, then the database
administrator (my client) to create or alter the table automatically
by running the command "db2 -tvf <filename>".

However, now, I would like to create the table if it doesn't exist. Or
Alter the table if it exists.

I found some posts mentioned the statement of:
IF NOT EXISTS (SELECT NAME FROM SYSIBM.SYSTABLES WHERE NAME="MYTABLE")
THEN
CREATE TABLE MYTABLE (......)
END IF

I tried to add such statements in the file, but it failed.

So, how can I do this?

Please help & thanks.
Vincent Ho

vincenthkh@gmail.com wrote:

> Hi all,
>
> I'm new in using DB2 and I have question on creating/ altering table.
>
> I created a file (DDL) with SQL statements, then the database
> administrator (my client) to create or alter the table automatically
> by running the command "db2 -tvf <filename>".
>
> However, now, I would like to create the table if it doesn't exist. Or
> Alter the table if it exists.
>
> I found some posts mentioned the statement of:
> IF NOT EXISTS (SELECT NAME FROM SYSIBM.SYSTABLES WHERE NAME="MYTABLE")

SQL uses single-quotes to delimit strings: NAME = 'MYTABLE'

> THEN
> CREATE TABLE MYTABLE (......)

You have to use dynamic SQL for that, i.e. create a string that contains the
CREATE TABLE statement, then use EXECUTE IMMEDIATE to execute the statement
in that string.

> END IF

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany