By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,004 Members | 1,222 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,004 IT Pros & Developers. It's quick & easy.

New to DB2: Create table if exists? How?

P: n/a
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

Oct 2 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
vi********@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
Oct 2 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.