problems creating tables inside a stored procedure

[Ir0neagle]

I am generating upgrade/new install scripts for my project. I am able to do this in oracle and sql server but an running into problems in db2. What I am trying to do is to use some logic to only create table that have not been generated in prevous releases. I am trying to create a stored procedure to do this.
I am not able to create the stored procedure because it errors out with the error below. I am stumped as how to get around this error because the errorr is happening when I am creating the stored procedure and not when I am executing.

ERROR [42710] [IBM][DB2/NT] SQL0601N The name of the object to be created is identical to the existing name "MIKEBO.TBLROLES" of type "TABLE". LINE NUMBER=11. SQLSTATE=42710

here is the base test procedure with one table

CREATE PROCEDURE CREATETABLE
(
)
SPECIFIC CreateTable
LANGUAGE SQL
BEGIN
DECLARE table_count INTEGER 0;

select COUNT(*) INTO table_count from syscat.tables where tabname = 'TBLROLES';
IF( table_count=0) THEN
CREATE TABLE "TBLROLES" (
"ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (
START WITH +1
INCREMENT BY +1
MINVALUE +1
MAXVALUE [phone]
NO CYCLE
CACHE 20
NO ORDER ) ,
"ROLENAME" VARGRAPHIC(10) NOT NULL ,
"AUTHDESCRIPTION" VARGRAPHIC(50),
PRIMARY KEY ("ID"),
CONSTRAINT "IX_TBLROLES" UNIQUE ("ROLENAME"))
IN "USERSPACE1" ;
END IF;
END;

[mersis]

I am having exactly the same problem. I need to drop and recreate same stored procedure from time to time. And in the stored procedure I test if a table does not exist I create it. If it does, I drop it and then create a new one. But when I try to recreate the stored proc second time after some tables have already been created by the previous stored proc, creation fails.

Did you find a way to turn that stupid check (that is unable to see that I am actually droping the tables before creating them if they exist) to make the creation of a stored to continue?

Would be great to hear if you have solved this problem somehow (other than using dynamic SQL, which is very ugly).

Thanks

I am generating upgrade/new install scripts for my project. I am able to do this in oracle and sql server but an running into problems in db2. What I am trying to do is to use some logic to only create table that have not been generated in prevous releases. I am trying to create a stored procedure to do this.
I am not able to create the stored procedure because it errors out with the error below. I am stumped as how to get around this error because the errorr is happening when I am creating the stored procedure and not when I am executing.

ERROR [42710] [IBM][DB2/NT] SQL0601N The name of the object to be created is identical to the existing name "MIKEBO.TBLROLES" of type "TABLE". LINE NUMBER=11. SQLSTATE=42710

here is the base test procedure with one table

CREATE PROCEDURE CREATETABLE
(
)
SPECIFIC CreateTable
LANGUAGE SQL
BEGIN
DECLARE table_count INTEGER 0;

select COUNT(*) INTO table_count from syscat.tables where tabname = 'TBLROLES';
IF( table_count=0) THEN
CREATE TABLE "TBLROLES" (
"ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (
START WITH +1
INCREMENT BY +1
MINVALUE +1
MAXVALUE [phone]
NO CYCLE
CACHE 20
NO ORDER ) ,
"ROLENAME" VARGRAPHIC(10) NOT NULL ,
"AUTHDESCRIPTION" VARGRAPHIC(50),
PRIMARY KEY ("ID"),
CONSTRAINT "IX_TBLROLES" UNIQUE ("ROLENAME"))
IN "USERSPACE1" ;
END IF;
END;