Previous Topic: Correcting Constraint ProblemsNext Topic: Correcting View Problems

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

Correcting Plan Problems

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

Plan problems cause the following message to be generated:

ERRORS WERE FOUND IN PLAN authid.plan-name AS FOLLOWS:

This message is followed by further messages detailing the problems that were found. The following is a list of possible messages and the corrective actions they require:

PLAN IS MISSING FROM DD TABLE PLN

STATEMENT(S) ARE MISSING FROM TABLES DDD AND/OR STM

PLAN IS INVALID — REBIND BEFORE RUNNING DBSRFPR

If either of the first two messages above are received, attempt to drop the plan using the DELETE PLAN administrative task in the CA Datacom Datadictionary Interactive SQL Service Facility.

If the DELETE PLAN succeeds, restore the plan by re-preparing (re-precompiling) the program that generated the plan. The problem is then solved.

If the DELETE PLAN or the re-prepare fails:

  1. Execute the DDDDULM utility with FUNC=INDEX. (See CA Datacom/DB Database and System Administration Guide for instructions on using the DDDDULM utility.) Look for entries beginning with PLN, SRC, PRP, and DEP, followed immediately by the AUTHID and the SQL name of the plan (see the report headers).
  2. Execute DDUTILTY Index Reports on the PLAN and STATEMENT entity-types, looking for names consisting of the plan's AUTHID, a dash, then the sqlname. The statements contain the plan name followed by the statement number in numeric form. For example, statement one for plan SYSUSR.TESTPLAN would appear as SYSUSR-TESTPLAN0000000000000001. If you require assistance in deleting a PLAN entity-occurrence, or if the report shows that some other combination of circumstances exist, or if you need assistance interpreting the report, call CA Support.
  3. After the plan has been successfully deleted, restore the plan by re-preparing (re-precompiling) the program that generated it.

Rebind the specified plan if you receive the message:

PLAN IS INVALID — REBIND BEFORE RUNNING DBSRFPR