PH41598: OBJECT COMPARE VALIDATE SQLCODE = -104 DUE TO MISSING #SET TERMINATOR STATEMENT; COMPARE & VALIDATE REPORTS INCONSISTENT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• This APAR addresses three issues:
  
  1) Due to a missing SET TERMINATOR statement for a trigger in
  the final changes file, validation fails with SQL error -104
  when RUN VALIDATE is set to V.  The required SET TERMINATOR
  statement needs to be generated before a new terminator is used
  for subsequent DDL statements in the final changes file.
  
  2) When triggers are not on the source, the compare report
  correctly states that triggers will be kept when Suppress Drop
  of Objects is set to YES.  However, the validation report
  indicates that these triggers are implicitly dropped. The
  validation report needs to be consistent and not include
  triggers in the list of implicitly dropped objects.
  
  3) The compare report also needs to be updated to show that
  a View Synonym is being recreated when the object it depends on
  is being recreated.  The report currently mentions that the
  Synonym is kept.
  
  Keywords:  SQLCODE104N
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of Db2 Administration Tool for z/OS    *
  *                 who compare views, triggers, and synonyms    *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1. When recreating a view, SET          *
  *                      TERMINATOR is missing before the SET    *
  *                      CURRENT and CREATE VIEW statements.     *
  *                      2. When a trigger is present on the     *
  *                      target and not on the source and the    *
  *                      "Suppress DROP of Objects" option       *
  *                      is set to YES, triggers that are        *
  *                      reported as KEPT in the object          *
  *                      comparison report are reported as       *
  *                      implicitly dropped in the validation    *
  *                      report.                                 *
  *                      3. When reporting triggers and synonyms *
  *                      that are impacted by the setting        *
  *                      "Suppress DROP of Objects" = YES, the   *
  *                      compare and validation reports are      *
  *                      inconsistent; the validation report     *
  *                      displays error ADB3014E for triggers.   *
  ****************************************************************
  1. When a view is recreated on the target, the SET TERMINATOR
  statement is missing before the SET CURRENT and CREATE VIEW
  statements that are generated for the view. Therefore, the new
  terminator is not explicitly set for the generated CREATE VIEW
  statements. This omission results in an SQL -104 error.
  2. When triggers are only on the target and the "Suppress DROP
  of Objects" option is set to YES, triggers are mentioned as KEPT
  in the object comparison report, but the validation report
  mentions that the objects are implicitly dropped. In the case of
  objects recreated due to the parent objects being recreated, the
  compare report should mention that the objects are recreated
  but are reported as KEPT due to the "Suppress DROP of objects"
  setting. The validation report should mentioned that the objects
  are recreated instead of implicitly dropped.
  
  3. When synonyms are recreated, the compare and validation
  reports are inconsistent. The compare report says that
  the synonyms will be kept, but the validation report says
  that they are recreated. The triggers in the
  validation report also cause a ADB3014E error with respect to
  a column in their parent table.
  
  The following messages are updated for objects that are only
  on the target and are recreated due to
  "Suppress DROP of Objects" set to YES:
  
  Trigger <database>.<table> not found on source
  Trigger is kept because YES was specified for the Suppress
  DROP of objects option
  The trigger is recreated because the base table will be
  dropped and recreated
  
  View Synonym <database>.<view> not found on source
  View Synonym is kept because YES was specified for the
  Suppress DROP of objects option
  View synonym is recreated because the view is recreated
  

Problem conclusion

• The problem has been resolved.
  
  Search Keywords: SQLCODE104N, MSGADB3014E, Triggers, Views
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UI79565

Modules/Macros

• ADB2CME  ADB2CMF  ADB2CMR  ADB2CMS  ADB2WVL
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RC10 PSY UI79565

     UP22/03/05 P F203

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.