PI92430: DB2 ADMIN TOOL - WHEN CREATING CATALOG-COPY, SOME LOB-TS ARE CREATED AFTER THEIR CREATE AUX TABLE, RETURNS SQLCODE = -204

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• When creating CATALOG-Copy, some LOB-TS are created near the end
  of the SQL file. This indicates that they are created in the
  post processing process (ADB2REP) but the CREATE AUX TABLE
  statements are created in-line, but referencing the LOB TS.
  Job ends with SQLCODE -204 / SQLCODEN204.
  This can be recreated in Admin V12.1 but not in V11.2.
  

Local fix

• Manually edit DDL to move the LOB-TS's before the CREATE
  AUXILIARY TABLE that uses the LOB-TS.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Administration Tool         *
  *                 for z/OS V12.1 who uses the Catalog          *
  *                 Copy function or uses the GEN function       *
  *                 on tables with more than one LOB             *
  *                 column.                                      *
  ****************************************************************
  * PROBLEM DESCRIPTION: Three issues when using Catalog Copy    *
  *                      function or GEN function:               *
  *                                                              *
  *                      1. The generated job to run the DDL     *
  *                      for the catalog copy or DDL for other   *
  *                      tables with more than one LOB column    *
  *                      fails with SQLCODE= -204/SQLCODEN204.   *
  *                                                              *
  *                      2. DB2 Load Utility fails with RC8 and  *
  *                      the following message is seen:          *
  *                                                              *
  *                      DSNU269I - FIELD xxxx IS NOT ALLOWED    *
  *                      where xxxx = fields SYS_START, SYS_END  *
  *                      and TRANS_START.                        *
  *                                                              *
  *                      3. SQLCODE -904 / SQLCODEN904           *
  *                      REASON CODE 00D70008 if the DSSIZE      *
  *                      is greater than 4G.                     *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The DDL generated by the GEN function for the catalog
  copy or DDL for other tables with more than one LOB column
  is incorrect.  For tables containing more than one LOB
  column the CREATE for the second and subsequent LOB table
  space may be missing or is placed near the end of the DDL
  data set causing an SQLCODE = -204.  It should be placed
  before the CREATE of the auxiliary table for the LOB column.
  
  In addition OVERRIDE(SYSTEMPERIOD,TRANSID) will be added to
  the LOAD statement for tables that are defined with a period
  definition and transaction id to resolve the DSNU269I issue.
  
  This APAR also removes the DSSIZE clauses on the
  CREATE statements for Copy Catalog tables, so they no
  longer require EA volumes, if the DSSIZE is greater
  than 4G and avoid SQLCODE -904 REASON CODE 00D70008.
  

Problem conclusion

• Problems 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:

  UI54224

Modules/Macros

•    ADB2CCI  ADB2CCL  ADB2REM  ADB28MC
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RC10 PSY UI54224

     UP18/03/08 P F803

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.