PM60343: COMPARE FAILS CC08 WITH (E)THE EXPLICIT LOB TABLE SPACE TSNAME IS STILL ASSOCIATED WITH AUXILIARY TABLE TABLENAME ...

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Users of DB2 Object Compare Tool report compare fails cc08 with
  error messages and warning messages for LOB objects when LOB
  objects that appear in the target are not present in the source
  for the compare, and option Suppress drop of objects = YES .
  .
  Warning and error messages are similar to:
  .
  1. LOB Tablespace schema.tsname not found on source
    Tablespace schema.tsname will be dropped
    Warning: Tablespace schema.tsname is a LOB tablespace. It can
    only be dropped after auxiliary table has been dropped
  .
  OC wants to drop LOB TS but needs to wait for AUX table to be
  dropped, hence the warning message.
  .
  2.  Table schema.tablename not found on source
     Table kept because KEEPTGT option was specified
  .
  Base table is not dropped hence AUX table is not dropped.
  .
  3.  LOB Index schema.indexname not found on source
    Index schema.indexname will be dropped
  .
  LOB index can and will be dropped regardless of AUX table and
  table space.
  .
  4. (E) The explicit LOB table space schema.tsname is still
     associated with auxiliary table schema.AUXtablename and
     therefore can not be dropped.
  .
  Before generating DROP statement for LOB table space OC checks
  the status of the AUX table. Since AUX table was not dropped,
  its LOB table space can not be dropped (DB2 restriction),
  and Error message (E) is generated.
  

Local fix

• Include LOB objects in Source (to keep them).
  DROP objects on Target (if no longer required).
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Object Comparison Tool      *
  *                 for z/OS who compare a set of explicit       *
  *                 LOB objects whose base table only exists     *
  *                 on the target with the compare option        *
  *                 'Suppress DROP of objects' set to 'YES'      *
  *                 on panel GOC5 'Generate Compare Jobs'.       *
  ****************************************************************
  * PROBLEM DESCRIPTION: In DB2 Object Comparison Tool for z/OS, *
  *                      Compare will fail with RC8 and display  *
  *                      an error message for LOB table space    *
  *                      when the LOB objects and their base     *
  *                      table are on target but not on source,  *
  *                      and option 'Suppress DROP of objects'   *
  *                      is set to 'YES':                        *
  *                                                              *
  *                      The Comparison report displays          *
  *                      the following error messages:           *
  *                                                              *
  *                      LOB Tablespace                          *
  *                      >schema.tsname< not found on            *
  *                      source                                  *
  *                      Tablespace >schema.tsname<              *
  *                      will be dropped                         *
  *                      Warning: Tablespace                     *
  *                      >schema.tsname< is a LOB                *
  *                      tablespace. It can only be              *
  *                      dropped after auxiliary table has       *
  *                      been dropped                            *
  *                                                              *
  *                      Table >schema.tablename< not            *
  *                      found on source                         *
  *                      Table kept because KEEPTGT              *
  *                      option was specified                    *
  *                                                              *
  *                      LOB Index >schema.indexname<            *
  *                      not found on source                     *
  *                      Index >schema.indexname<                *
  *                      will be dropped                         *
  *                                                              *
  *                      (E) The explicit LOB table space        *
  *                      >schema.tsname< is still                *
  *                      associated with auxiliary table         *
  *                      >schema.auxtablename< and               *
  *                      therefore can not be dropped.           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  In DB2 Object Comparison Tool for z/OS, Compare will fail with
  RC8 and display an error message for a LOB table space when the
  LOB objects and their base table are on target but not on
  source, and option 'Suppress DROP of objects' is set to 'YES'.
  
  The following error message is displayed:
  
  (E) The explicit LOB table space >schema.tsname< is still
  associated with auxiliary table >schema.auxtablename< and
  therefore can not be dropped.
  
  This error happens because according to Compare,
  'Suppress DROP of objects' option does not apply to
  explicit LOB objects. Therefore, Compare will try to
  drop the explicit LOB table space even though
  'Suppress DROP of objects' is set to 'YES'. When the
  base table is on target but not on source, the base
  table will be kept. The auxiliary table can be dropped by
  dropping the base table. Since the base table is kept,
  the auxiliary table will be kept as well. When trying to
  drop the LOB table space, Compare detects that it is
  still associated with an auxiliary table, and therefore can
  not be dropped according to DB2's restriction. Compare
  issues the error message above and terminates with a RC8
  to prevent an error during APPLY.
  
  Compare should be able to detect that the base table
  and LOB objects are on target but not on source. All
  the objects should be kept when 'Suppress DROP of
  objects' is set to 'YES'.
  

Problem conclusion

• Problem has been resolved.  Compare will, regardless of the
  setting for 'Suppress DROP of objects' option, attempt to drop
  all the explicit LOB objects from the target if they are not
  specified on the source. However, if the base table the LOB
  objects are associated with is kept because 'Suppress DROP of
  objects' option is set to 'YES', then all the LOB objects will
  be kept instead of being dropped.
  
  The DB2 Object Comparison Tool User's Guide Version 10.1 will
  be updated to reflect this approach.
  
  The following message will be displayed for the index
  on the auxiliary table which is kept:
  
  ADB7107I Index >schema<.>Ixname< is an index on auxiliary
  table. It will be kept because the base table
  >schema<.>tbname< is kept.
  
  The following message will be displayed for the LOB table
  space which is kept:
  
  ADB7109I The explicit LOB table space >schema<.>tsname<
  is supposed to be dropped but is kept because the base table
  >schema<.>tbname< is kept.
  
  In the DB2 Object Comparison Tool User's Guide Version 10.1,
  Chapter 4.  - 'Creating DB2 Object Comparison Tool batch jobs'
  describes the usage and approach for compare option 'Suppress
  DROP of objects'.  The description needs to be updated to
  reflect the improved approach.
  
  Content in the User's Guide before the change:  Suppress DROP of
  objects Specify whether the compare process will suppress
  dropping target objects that are in the target but that are not
  in the source.
  
  Regardless of the value that you set for this option, Object
  Comparison Tool replaces all relationships between a parent and
  a child if a foreign key is specified in the source.  To delete
  a foreign key, both the parent and the child must be present in
  the source (without a foreign key).  If DROP statements are part
  of the source DDL, objects are dropped regardless of the value
  specified for this option.
  
  Regardless of the value that you set for this option, Object
  Comparison Tool drops all explicit LOB objects from the target
  if they are not specified on the source.
  
  
  Content in the User's Guide after the change:
  
  Suppress DROP of objects
  Specify whether the compare process will suppress dropping
  target objects that are in the target but that are not in the
  source.
  
  Regardless of the value that you set for this option, Object
  Comparison Tool replaces all relationships between a parent and
  a child if a foreign key is specified in the source.  To delete
  a foreign key, both the parent and the child must be present in
  the source (without a foreign key).  If DROP statements are part
  of the source DDL, objects are dropped regardless of the value
  specified for this option.
  
  Regardless of the value that you set for this option, Object
  Comparison Tool drops all explicit LOB objects from the target
  if they are not specified on the source.  However, if the base
  table associated with the LOB objects is kept because 'Suppress
  DROP of objects' is set to 'YES', then all of the LOB objects
  are kept.
  

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:

  UK78298

Modules/Macros

•    GOC5H
  

Fix information

• Fixed component name

  DB2 OBJECT COMP

• Fixed component ID

  5697G6400

Applicable component levels

• RA10 PSY UK78298

     UP12/04/28 P F204

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.