New to DB2: Create table if exists? How?

[vincenthkh@gmail.com]

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.SYSTABLE S 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

[Knut Stolze]

Re: New to DB2: Create table if exists? How?

vincenthkh@gmai l.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.SYSTABLE S 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