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
Quote:
Originally Posted by 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 +2147483647
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;
