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

Correcting View Problems

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

View problems cause the following message to be generated:

ERROR(S) WERE FOUND IN VIEW authid.view-name AS FOLLOWS:

This message is followed by other messages detailing the problems that were found. The following is a list of other messages that might appear:

VIEW IS MISSING FROM DATADICTIONARY

THE DDD IS MISSING ROWS FOR THIS VIEW

If either of these messages appears, take the following steps:

1. Locate your original CREATE VIEW statement.
2. Drop the view using Interactive SQL Service Facility to execute the SQL statement:

    DROP VIEW authid.viewname
   

3. If the DROP fails, go to the following step 4. If the DROP succeeds:
   1. Re-create the view using your CREATE VIEW source.
   2. If the CREATE VIEW fails, consult the CA Datacom/DB Message Reference Guide for instructions on recovering from your particular SQLCODE, then correct the problem that caused the SQLCODE and retry the CREATE VIEW.

      Note: The problem is solved when the CREATE VIEW succeeds.

4. When the DROP fails:
   1. Execute the three SQL DELETE statements detailed in the Correcting Constraint Problems section that begins on Correcting Constraint Problems, substituting the view name for the constraint-name and ignoring any 100 SQLCODEs received.
   2. Use the Interactive SQL Service Facility to execute the following SQL statement:

       DELETE FROM SYSADM.SYSVIEWDEP WHERE
        DCREATOR='authid' and DNAME='view-name'.
      

   3. Note any negative SQL return code received. See the CA Datacom/DB Message Reference Guide for information about specific return codes.
   4. Execute the DDDDULM utility with FUNC=INDEX. Look for entries beginning with SRC, PRP, COL, REL, and DEP, followed immediately by the AUTHID and the SQL name of the view (see the report headers).
   5. Execute a DDUTILTY Index Report on the VIEW entity-type, looking for names consisting of the AUTHID followed by the view name, separated by a dash.
   6. If no traces of the view are found by the DDDDULM report but the Index Report shows there is a VIEW entity-occurrence in CA Datacom Datadictionary, use DDUPDATE to delete the VIEW entity-occurrence. If you need assistance, or if the reports show that some other combination of circumstances exist, or if you require help interpreting the reports, call CA Support.
   7. Go to step 3 and follow the instructions listed for when the DROP succeeds.