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
_______________________________________________
Dev mailing list
[email protected]
http://wso2.org/cgi-bin/mailman/listinfo/dev
