Hi Tharindu, I could successfully drop the constraint by following the steps. I'll use this in the migration scripts.
Thank You Very Much! On Fri, Feb 19, 2016 at 9:02 AM, Tharindu Edirisinghe <[email protected]> wrote: > Hi Chamila, > > IBM DB2 behaves little bit differently compared to other well known > databases when comes to constraints and indexes. So let me explain the > solution for your problem in detail. > > You have the following table. > > CREATE TABLE IDP_PROVISIONING_ENTITY ( > ID INTEGER NOT NULL, > PROVISIONING_CONFIG_ID INTEGER NOT NULL, > ENTITY_TYPE VARCHAR(255) NOT NULL, > ENTITY_LOCAL_USERSTORE VARCHAR(255) NOT NULL, > ENTITY_NAME VARCHAR(255) NOT NULL, > ENTITY_VALUE VARCHAR(255) NOT NULL, > TENANT_ID INTEGER NOT NULL, > * PRIMARY KEY (ID),* > > * UNIQUE (ENTITY_TYPE, TENANT_ID, ENTITY_LOCAL_USERSTORE, > ENTITY_NAME), UNIQUE (PROVISIONING_CONFIG_ID, ENTITY_TYPE, > ENTITY_VALUE),* > FOREIGN KEY (PROVISIONING_CONFIG_ID) REFERENCES > IDP_PROVISIONING_CONFIG(ID) ON DELETE CASCADE) > / > > It has a primary key and two unique constraints. The DB2 will > automatically create indexes for these three constraints. > > The problem is, we have not defined the constraints as named constraints. > Therefore we have to take an extra effort to find out the names of the > indexes and the constraints which are auto generated by DB2. > > These are the two unique constraints that we have in the table. > > > * UNIQUE (ENTITY_TYPE, TENANT_ID, ENTITY_LOCAL_USERSTORE, > ENTITY_NAME), UNIQUE (PROVISIONING_CONFIG_ID, ENTITY_TYPE, > ENTITY_VALUE)* > > You can find the auto generated names for the unique constraints by > running the following query. the constraintyp='U' for unique constraints > and it is 'P' for primary keys. > > *db2 "select NAME from sysibm.systabconst where > tbname='IDP_PROVISIONING_ENTITY' and constraintyp='U'"* > > NAME > > > -------------------------------------------------------------------------------------------------------------------------------- > SQL160219074557840 > > SQL160219074557920 > > > Above is the result I got. You will get different values in your database > as these are random generated. > > You can run the following query to see the indexes created for the > IDP_PROVISIONING_ENTITY' table. > > *db2 "SELECT NAME, COLNAMES FROM SYSIBM.SYSINDEXES WHERE > TBNAME='IDP_PROVISIONING_ENTITY'"* > > NAME COLNAMES > SQL160219074557290 +ID > SQL160219074557790 > +PROVISIONING_CONFIG_ID+ENTITY_TYPE+ENTITY_VALUE > SQL160219074557860 > +ENTITY_TYPE+TENANT_ID+ENTITY_LOCAL_USERSTORE+ENTITY_NAME > > Now we know the constraint names for the two unique constraints and the > index names for the IDP_PROVISIONING_ENTITY table. > > You can find the particular index you need to drop by adding a where > clause for the above query as below. > > *db2 "SELECT NAME FROM SYSIBM.SYSINDEXES WHERE > TBNAME='IDP_PROVISIONING_ENTITY' and > COLNAMES='+ENTITY_TYPE+TENANT_ID+ENTITY_LOCAL_USERSTORE+ENTITY_NAME'"* > > NAME > > > -------------------------------------------------------------------------------------------------------------------------------- > SQL160219074557860 > > In IBM DB2, you cannot drop an index which is generated for a constraint > (primary key or unique) directly. For that, you need to drop the constraint > which will drop the index also. > > But the problem is we do not know what is the name of the unique > constraint that is associated with the index > "+ENTITY_TYPE+TENANT_ID+ENTITY_LOCAL_USERSTORE+ENTITY_NAME". We already > know that the index name we need to drop is *SQL160219074557860*. > > You can find it out which constraint is associated with which index by > running the following query. > > *db2 "select CONSTNAME, BNAME from SYSCAT.CONSTDEP where TABNAME = > 'IDP_PROVISIONING_ENTITY'"* > > CONSTNAME BNAME > SQL160219074557790 SQL160219074557290 > SQL160219074557840 SQL160219074557790 > SQL160219074557920 *SQL160219074557860* > > > > Based on the results, you can get to know the name of the constraint. > So the constraint name associated with the index name " > *SQL160219074557860*" is "*SQL160219074557920*". > > So since we know the constraint name now, we can drop the unique > constraint. > > *db2 "ALTER TABLE IDP_PROVISIONING_ENTITY DROP UNIQUE SQL160219074557920"* > > This will drop the constraint as well as the index created for the > constraint. > > For the migration, you can write a stored procedure which follows the > above steps and finds the constraint name and then drop the constraint. > > Thank you, > TharinduE > > > > On Wed, Feb 17, 2016 at 3:22 PM, Chamila Wijayarathna <[email protected]> > wrote: > >> Hi all, >> >> Still I couldn't came up with a way to achieve this target. >> >> So as a workaround I thought of dropping all the UNIQUE constraints and >> then adding constraints which were not suppose to dropped. It may be more >> costly than our previously mentioned approach, I don't see any other way of >> achieving this. >> >> WDYT? >> >> Thanks >> >> On Wed, Feb 17, 2016 at 12:30 PM, Chamila Wijayarathna <[email protected]> >> wrote: >> >>> Hi all, >>> >>> Currently I am working on developing the migration client and and >>> scripts for DB2 to migrate from IS 5.0.0 to 5.1.0. >>> >>> There are some unique constraints, primary keys and foreign keys that >>> has been used in IS 5.0.0 which have been removed in IS 5.1.0. So I need to >>> remove those constraints in migration script. >>> >>> To drop unique constraints I only have the table name and columns of the >>> unique constraint. >>> >>> To drop the unique constraint, following query can be used. >>> >>> ALTER TABLE IDP_PROVISIONING_ENTITY DROP CONSTRAINT <CONSTRAINT >>> NAME> >>> >>> But to do this I need to retrieve constraint name using table name and >>> column list. Constraint names are available in both "sysibm.systabconst" >>> and also in "SYSCAT.TABCONST" tables. How can I retrieve constraint name >>> using the information I have with me currently. >>> >>> Thank You! >>> >>> -- >>> *Chamila Dilshan Wijayarathna,* >>> Software Engineer >>> Mobile:(+94)788193620 >>> WSO2 Inc., http://wso2.com/ >>> >> >> >> >> -- >> *Chamila Dilshan Wijayarathna,* >> Software Engineer >> Mobile:(+94)788193620 >> WSO2 Inc., http://wso2.com/ >> >> _______________________________________________ >> Dev mailing list >> [email protected] >> http://wso2.org/cgi-bin/mailman/listinfo/dev >> >> > > > -- > > Tharindu Edirisinghe > Software Engineer | WSO2 Inc > Platform Security Team > Blog : tharindue.blogspot.com > mobile : +94 775181586 > -- *Chamila Dilshan Wijayarathna,* Software Engineer Mobile:(+94)788193620 WSO2 Inc., http://wso2.com/
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
