SQL Object Consistency Analyzer and Upgrade Rebind Utilities › SQL Object Consistency Analyzer › Correcting Problems › Correcting Constraint Problems

Correcting Constraint Problems

Read all instructions before attempting any of the steps outlined in this section, and if any step is unclear, call CA Support.

Constraint problems cause the following message to be generated:

ERRORS FOUND IN TABLE authid.tablename CONSTRAINT constraint-name AS FOLLOWS:

Following that message will be other messages detailing the problems that were found. Here is a list of possible messages:

CONSTRAINT IS MISSING FROM DATADICTIONARY

CONSTRAINT IS MISSING FROM SYSADM.SYSCONSTROBJ

CONSTRAINT IS MISSING FROM SYSADM.SYSCONSTRSRC

CONSTRAINT IS MISSING FROM SYSADM.SYSCONSTRDEP

If any of these messages appear, use the CA Datacom Datadictionary Interactive SQL Service Facility to accomplish the following steps:

1. Obtain the SQL statement that created the constraint using one of the following:
   • Obtain the CREATE TABLE or ALTER TABLE SQL statement that was used to create the constraint in question. If this statement is not available, run the following query to obtain the constraint source:

      SELECT TEXT, SEQNO FROM SYSADM.SYSCONSTRSRC WHERE
       CNAME='constraint-name' AND CREATOR='authid' ORDER BY SEQNO.
     

   • If the SELECT above was unsuccessful, run a DDUTILTY Text Report on the CONSTRAINT entity-occurrence, specifying a text CLASS of SQLSOURCE. (See the CA Datacom Datadictionary Batch Reference Guide for details on running this report.)
2. Issue the following SQL statement to drop the constraint:

    ALTER TABLE authid.table-name DROP CONSTRAINT constraint-name.
   

3. If the ALTER TABLE succeeds, go to step 4. If the ALTER TABLE does not succeed:
   1. Execute the following SQL DELETE statements to remove traces of the constraint from the SIT area.

       DELETE FROM SYSADM.SYSCONSTRSRC WHERE CNAME='constraint-name' AND
          CREATOR='authid'
       DELETE FROM SYSADM.SYSCONSTRDEP WHERE CNAME='constraint-name' AND
          CCREATOR='authid'  (CCREATOR is not a misprint)
       DELETE FROM SYSADM.SYSCONSTROBJ WHERE CNAME='constraint-name' AND
          CREATOR='authid'
      

   2. Execute a DDUTILTY Index Report on constraints to determine if traces of the constraint exist.
   3. If no traces are found, go to step 4. If traces are found, use DDUPDATE to delete the CONSTRAINT entity-occurrence. If you require assistance, call CA Support.
4. Re-add the constraint by running an SQL statement in the form:

     ALTER TABLE authid.tablename ADD insert-constraint-text-here
      CONSTRAINT constraint-name.
   

5. If the ALTER TABLE succeeds, your problem has been solved. If the ALTER TABLE fails, consult the CA Datacom/DB Message Reference Guide to determine the appropriate action to take for the SQLCODE you received. After remedying the problem that caused the nonzero SQLCODE, retry the ALTER TABLE.

If you receive this message:

CONSTRAINTS DEFINED ON THE FOLLOWING DATABASES WILL NOT REBIND DUE TO DD STRUCTURE VERIFY ERRORS:

1. Note the DD ERROR, MODULE, and DESCRIPTION listed for each database that had an error.
2. Use the CA Datacom/DB Message Reference Guide to find out what went wrong.
3. If the information printed in the report does not give you enough information to find the problem, perform the CA Datacom Datadictionary VERIFY function on the database in question and correct the problems indicated by the output from the function.
4. After problems are corrected, run DBSOCPR again to confirm that the problems no longer exist.