* 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.
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'));