PH42940: DDL LINE COMMAND GENERATES SYNTACTICALLY INCORRECT DDL FOR INDEXWITH DSSIZE AND BUSINESS_TIME

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• When issuing a DDL line command for an index with DSSIZE and
  BUSINESS_TIME specified, the resulting DDL is syntactically
  wrong, as follows:
  
  
  1) Result from DDL line command for index:  ENDDT is included in
  the CREATE INDEX statement. It should not be mentioned in the
  DDL as this is part of the BUSINESS_TIME definition.  If this
  DDL is executed, the following error occurs:
  
  DSNT408I SQLCODE = -20522, ERROR: INVALID SPECIFICATION OF
  WITHOUT OVERLAPS OR PERIOD CLAUSE. REASON CODE = 5
  
  2) If an ALTER INDEX ALTER PARTITION command is executed,
  the partition clause may be missing in the DDL.  For example:
  
  ALTER INDEX >INDEXNAME<
  ALTER PARTITION 2
  FREEPAGE 0 PCTFREE 50 DSSIZE 4 G;
  
  Result from DDL line command for the index:  There is a missing
  "PARTITION 2" clause in the DDL.  If this DDL is executed, the
  following error occurs:
  
  DSNT408I SQLCODE = -637, ERROR: DUPLICATE FREEPAGE KEYWORD OR
  CLAUSE
  
  
  
  Keywords: SQLCODE20522N; SQLCODE637N
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of Db2 Administration Tool for z/OS    *
  *                 who generate DDL for unique indexes          *
  ****************************************************************
  * PROBLEM DESCRIPTION: When generating DDL for unique indexes  *
  *                      with BUSINESS_TIME WITHOUT OVERLAPS,    *
  *                      the resulting DDL is syntactically      *
  *                      incorrect and results in SQLCODE -20522 *
  *                      Reason = 5. The resulting DDL is also   *
  *                      syntactically incorrect if a partition  *
  *                      of the index is altered and results in  *
  *                      and SQLCODE -637.                       *
  ****************************************************************
  1. When the DDL line command is issued on a unique index that
  has columns of the BUSINESS_TIME period, the resulting DDL is
  syntactically incorrect and contains a column that should not
  be included in the DDL. Executing this results in an SQLCODE
  -20522.
  
  DSNT408I SQLCODE = -20522, ERROR: INVALID SPECIFICATION OF
  WITHOUT OVERLAPS OR PERIOD CLAUSE. REASON CODE = 5
  
  2. If the DSSIZE, PCTFREE and FREEPAGE are
  altered for a partition of the index by using ALTER INDEX ALTER
  PARTITION, the resulting DSSIZE value in the DDL is followed by
  additional PCTFREE, FREEPAGE, and DSSIZE values that should not
  be present in the DDL, as it is syntactically incorrect. This
  results in SQLCODE -637.
  
  DSNT408I SQLCODE = -637, ERROR: DUPLICATE FREEPAGE KEYWORD
  OR CLAUSE
  

Problem conclusion

• The problem has been resolved.
  
  Keywords: SQLCODE20522N; SQLCODE637N
  

Temporary fix

Comments

• ×**** PE22/09/01 FIX IN ERROR. SEE APAR PH49221  FOR DESCRIPTION
  

APAR Information

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

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

  UI80169

Modules/Macros

• ADB2REG
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RC10 PSY UI80169

     UP22/04/19 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.