PI56347: DB2 OBJECT COMPARISON TOOL FOR Z/OS CREATE DDL IN WRONG ORDER, CREATE TABLE BEFORE CREATE TABLESPACE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• DB2 OBJECT COMPARISON TOOL for z/OS to generate one apply job.
  The compare job generated a dataset containing DDL to create
  objects, but the objects are created in the wrong order - some
  tables are to be created before their tablespace.
  

Local fix

• use RENAMETB mask so that to bypass the problem and to make the
  change through ALTER instead of Drop/Create.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Object Comparison Tool      *
  *                 for z/OS who use the Compare function to     *
  *                 add new table to a table space.              *
  ****************************************************************
  * PROBLEM DESCRIPTION: When using Object Compare to drop an    *
  *                      existing table from a table space       *
  *                      and then add a new table to the table   *
  *                      space, two problems may occur:          *
  *                                                              *
  *                      1. If the table is partitioned, then    *
  *                      the table space will be dropped to      *
  *                      drop the table and recreate it. The     *
  *                      CREATE TABLESPACE and CREATE TABLE      *
  *                      statements in the Apply job could       *
  *                      be in the wrong order which causes      *
  *                      a SQLCODEN601 / =SQLCODE=-601           *
  *                      when running the Apply job.             *
  *                                                              *
  *                      2. If the table space on the target     *
  *                      is segmented and is changed to          *
  *                      partitioned-by-growth (PBG) during      *
  *                      the compare, a SQLCODEN763 /            *
  *                      SQLCODE=-763 may occur when             *
  *                      running the Apply job because the       *
  *                      "PARTITION BY SIZE" keyword is          *
  *                      used in the CREATE TABLE DDL            *
  *                      statement but altering the table        *
  *                      space to PBG is done after the new      *
  *                      table is added.                         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When using Object Compare to drop an existing table from a
  table space and then add a new table to the table space, two
  problems may occur:
  
  1. The CREATE TABLESPACE and CREATE TABLE statements in the
  Apply job could be in wrong order which causes a SQLCODEN601 /
  SQLCODE=-601:
  
  DSNT408I SQLCODE = -601, ERROR:  THE NAME
  (VERSION OR VOLUME SERIAL NUMBER) OF THE
  OBJECT TO BE DEFINED OR THE TARGET OF A
  RENAME STATEMENT IS IDENTICAL TO THE EXISTING
  NAME (VERSION OR VOLUME SERIAL NUMBER)
  >object_name< OF THE OBJECT TYPE >object_type<
  DSNT418I SQLSTATE   = 42710
  SQLSTATE RETURN CODE
  
  when running the Apply job.
  
  2. If the table space on the target is segmented and is
  changed to partitioned-by-growth (PBG) during the compare,
  a SQLCODEN763 / SQLCODE=-763 may occur:
  
  DSNT408I SQLCODE = -763, ERROR:  INVALID TABLE
  SPACE NAME  >ts_name<
  DSNT418I SQLSTATE   = 560A1
  SQLSTATE RETURN CODE
  
  when running the Apply job because "PARTITION BY SIZE"
  keyword is used in the CREATE TABLE DDL statement but
  altering the table space to PBG is done after the new
  table is added.
  

Problem conclusion

• Problem has been resolved.
  

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:

  UI37636 UI37637

Modules/Macros

•    ADB2CMR
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RB10 PSY UI37636

     UP16/05/12 P F605

• RB20 PSY UI37637

     UP16/05/12 P F605

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.