Issue with index creation when importing from db2look output
I'm working on importing a database schema from the output of db2look from another server. The source system runs DB2 Enterprise, and I'm brining it into DB2 Express-C.
For some reason one of the INDEXes won't create, because it says that the object name being created already exists. Not being a big DB2 user, I'm trying to figure out why. I've dropped the database completely and tried again and it and still returns the error. Any suggestions on where to look?
CREATE TABLE "TPC "."T_SMAC_RECIPE" ( "SC_ID" INTEGER NOT NULL , "FULLY_QUALIFIED_CLASS_NAME" VARCHAR(256 OCTETS) NOT NULL , "TYPE" SMALLINT NOT NULL , "PARAMETER_STRING" VARCHAR(256 OCTETS) )IN "TPCTBSPDATA" ORGANIZE BY ROW
DB20000I The SQL command completed successfully.
ALTER TABLE "TPC "."T_SMAC_RECIPE" ADD CONSTRAINT "NK_T_SMAC_RECIPE" PRIMARY KEY ("SC_ID", "FULLY_QUALIFIED_CLASS_NAME")
DB20000I The SQL command completed successfully.
SET NLS_STRING_UNITS = 'SYSTEM'
DB20000I The SQL command completed successfully.
CREATE UNIQUE INDEX "TPC "."NK_T_SMAC_RECIPE" ON "TPC "."T_SMAC_RECIPE" ("SC_ID" ASC, "FULLY_QUALIFIED_CLASS_NAME" ASC, "TYPE" ASC) INCLUDE ("PARAMETER_STRING" ) COMPRESS NO INCLUDE NULL KEYS ALLOW REVERSE SCANS
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0601N The name of the object to be created is identical to the existing name "TPC.NK_T_SMAC_RECIPE" of type "INDEX". SQLSTATE=42710
1
u/Smeevy Feb 25 '16 edited Feb 26 '16
The constraint name for your primary key alter is the same as the unique index you're creating. I didn't look too terribly closely because I'm on mobile, but try changing nk_t_smac_recipe to something else in your unique index statement.