I am trying to generate an SQL script that will drop all of the constraints (F,P, and U) and indexes from tables that exist in my schema.

• In order to get a list of all the contraints I query the SYSCAT.TABCONST table.
  
• In order to get a list of all the indexes I query the SYSCAT.INDEXES table.
  
• I then generate a script with SQL statements to drop all of the above.

This is causing me a problem when I encounter constraints that were created using the following command:
ALTER TABLE AUTOMOBILE ADD UNIQUE (CHASISNUMBER);

This statement creates an entry in SYSCAT.TABCONST, but DB2 also creates an entry in SYSCAT.INDEXES. When you drop the constraint based on the information

from SYSCAT.TABCONST, DB2 automatically drops the index from SYSCAT.INDEXES also.

This causes me a problem with my generated SQL script, as when the drop statement that is based on the information from SYSCAT.INDEXES is executed, it

attempts to drop an index that does not exist (as it has already been automatically dropped by DB2), and the statement fails.



Does anyone know how I can select all of the indexes from SYSCAT.INDEXES that will not be automatically dropped when I drop the contraints as taken from

SYSCAT.TABCONST?

I couldn't figure out how to link the contraint from SYSCAT.TABCONST to the index from SYSCAT.INDEXES. The SYSCAT.TABCONST.CONSTNAME and

SYSCAT.INDEXES.INDNAME aren't always equal.


I select my contraints to drop using:

• SELECT CONSTNAME from SYSCAT.TABCONST WHERE type IN ('P','U','F') AND DEFINER='DB2ADMIN' AND TABSCHEMA='DB2ADMIN'

I select my indexes to drop using:

• SELECT INDNAME FROM SYSCAT.INDEXES WHERE DEFINER='DB2ADMIN' AND TABSCHEMA='DB2ADMIN'

I was considering altering my select on SYSCAT.INDEXES to the following

• SELECT INDNAME FROM SYSCAT.INDEXES WHERE DEFINER='DB2ADMIN' AND TABSCHEMA='DB2ADMIN' AND INDSCHEMA='DB2ADMIN'

As the index that is automaticaly generated seems to always get created in the SYSIBM schema.



Any help greatly appreciated.

On further investigation, I think I may have the solution, but would welcome any comments !

The records that I am trying to ignore from SYSCAT.INDEXES are ones that are automatically created by the system when a UNIQUE constraint is created.

From looking here DB2 Database for Linux, UNIX, and Windows I see from the description of SYSCAT.INDEXES that...

* USER_DEFINED will be 0 if the index was not defined by the user
* SYSTEM_REQUIRED will be 1 if one or the other of the following conditions is met:

* This index is required for a primary or unique key constraint, or this index is a dimension block index or composite block index for a multidimensional clustering (MDC) table.
* This is the index on the object identifier (OID) column of a typed table.

So, if I change my select on the SYSCAT.INDEXES table to the following:

SELECT INDNAME FROM SYSCAT.INDEXES WHERE TABSCHEMA=? and not (USER_DEFINED=0 and SYSTEM_REQUIRED=1 and UNIQUERULE='U');

Then I should get all the indexes that are created against tables in my schema, but are not automatically created ones for the purposes of a primary or unique key.

The indexes that were created by the system for the purposes of a primary or unique key will get dropped automatically when I drop the constraints based on my query on SYSCAT.TABCONST.

Sound right?

metricspaces, depending on how you create them, Primary Keys can also generate indexes. If you create the index first and then the Primary key, this is not a problem as the system will find and use the existing index and you will need to drop them separately (with the Primary key being dropped first).

However, if you create the Primary key and there is no supporting index, DB2 will create the index (just like the Unique constraints).

So you might need to modify the query to UNIQUERULE IN('U','P'));

Thanks for the prompt reply Stealth_DBA. I expect the Index to be created before the Primary Key. But I could not rule out a Primary Key being created without the Index first.

So, I'll need to modify my query as you suggested to:

SELECT INDNAME FROM SYSCAT.INDEXES WHERE TABSCHEMA=? and not (USER_DEFINED=0 and SYSTEM_REQUIRED=1 and UNIQUERULE IN ('U', 'P'));

Thanks.