When trying to delete an unused collection definition from the ICMSTCOLLNAME table we are getting a sql -532 referencial integrity error and the delete fails.
User sees a DB2 -532 error or failure at the Sysadmin Client workstation when trying to do the delete.
DB2 as part of a delete of a higher level definition of an attribute, confirms prior to the delete that there are no other tables with the same attribute that still have that value defined. That same attribute does exist and has the value to be deleted, DB2 generates the -532 indicating the value can not be deleted because it is still in use in another table.
z/OS and DB2
Diagnosing the problem
ECM library server log will show a DB2 sql code of -532 as part of a delete request.
Resolving the problem
There are a number of ways to resolve the issue. In all cases, the goal is to find the other tables with the same attribute and value you are trying to delete and remove them from the other table(s).
The best method, given you have the DB2 tools or utilities (IBM or third party), is to determine the attribute and its value you are trying to delete and using those tools, find all other places that attribute is defined and where that value still exists and then clean. You can do that by changing the attribute value in the other tables or remove those rows given they are no longer needed.
The next method is to use the ECM information center where all the system tables are defined. From that determine all the tables that the attribute in question is defined in. In this case it was the SMSCOLLCODE attribute, then write a spufi select to see if the value exists in those tables and then clean them up if it does.
Last, using the second method for ECM defined attributes is fine, but this could happen for user defined attributes. In which case, you will have to determine in which tables the attribute is used as the ECM information center will not have user defined attributes or tables in it. Then do a similar spufi select but against your user defined tables.
Once you have cleaned up the incorrect or left over attribute definitions in the other tables, the delete should then run without a failure.