Drop and re-creating tables in db2 LUW

Question

I am trying to write a sql script which checks if a table exists and if it does it deletes it, then re-creates the table I am trying to do this using an sql script for DB2 9.7 LUW using IBM Data studio 2.2

(1) DECLARE @tablefound INTEGER ;

SET @TABLEFOUND = (select COUNT(*) from syscat.tables where tabschema = 'AELUM' and tabname = 'PRODUCTS');

(2) IF @TABLEFOUND>0 THEN

DROP TABLE "AELUM"."PRODUCTS" ;

(3) END IF;

CREATE TABLE "AELUM"."PRODUCTS" (
    "Created" DATE NOT NULL DEFAULT CURRENT_DATE,
    "Author" VARCHAR(255),
    "ProductName" VARCHAR(255),
    "ProductComment" VARCHAR(255),
    "ProductDefinition" XML
)
DATA CAPTURE NONE ;

I get the following errors

(1) DB2 for Linux, UNIX, and Windows: End of text was reached after "INTEGER".
(2) DB2 for Linux, UNIX, and Windows: Unexpected text "IF @TABLEFOUND>0 THEN" encountered.
(3) Multiple markers at this line - DB2 for Linux, UNIX, and Windows: ""JOIN "" was expected to form a complete scope. - DB2 for Linux, UNIX, and Windows: """" was expected after "IF".

I have tried changing the statement terminator from ; to < and no luck Please let me know what i'm doing wrong. As all i want to achieve is a set of SQL scripts that i can run to "restore" my tables to a clean/virgin state. I'm using db2 LUW 9.7.2 and IBM DATA STUDIO Release 2.2.1.0

Regards

Is that DB2 SQL syntax, or MS SQL Server syntax?

Jonathan Leffler
– Jonathan Leffler

2011-01-30 21:58:40 +00:00
Commented Jan 30, 2011 at 21:58 — Jonathan Leffler
– Jonathan Leffler, Commented Jan 30, 2011 at 21:58

Erik Sorensen · Accepted Answer · 2011-05-10 20:19:38Z

0

I don't see a reason why you are checking whether or not the table exists... if you are just going to drop it anyway. Just have two statements, one drop table, then one create table. The first one may fail, but it really doesn't matter.

answered May 10, 2011 at 20:19

Erik Sorensen

684 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Drop and re-creating tables in db2 LUW

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related