PK77610: IN SYSIBM.SYSCOLUMNS NAME VALUE EXTRA DOT AT THE END WHEN CREATE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Customer's problem: if you do a CREATE of a COLUMN, in
  DB2-catalog an entry of this column  is done with a dot at end.
  
  If you do an ALTER of a COLUMN, no dot is made.
  Testcase is below:
  ----------------------------------------------------------------
  1) Create a new table (T2):
  
  DDL:
  C1  DOUBLE    NOT NULL WITH DEFAULT       -
  100000000000000000000.
  C2 DOUBLE    NOT NULL WITH DEFAULT       -
  100000000000000000000
  
  DB2-Catalog shows:
        ,C1                    FLOAT(53)      NOT NULL
  WITH DEFAULT
  -100000000000000000000.
        ,C2                   FLOAT(53)      NOT NULL
  WITH DEFAULT
  -100000000000000000000.
  
  2) Alter the table:
  
  DDL:
  ALTER TABLE T2
       ADD  A1 DOUBLE NOT NULL WITH DEFAULT
         -100000000000000000000.
  ;
  ALTER TABLE T2
       ADD A2 DOUBLE NOT NULL WITH DEFAULT
         -100000000000000000000
  ;
  
  DB2-Catalog shows:
        ,A1                     FLOAT(53)      NOT NULL
  WITH DEFAULT -100000000000000000000
        ,A2                    FLOAT(53)      NOT NULL
  WITH DEFAULT -100000000000000000000
  
  Select from SYSCOLUMNS:
  SELECT SUBSTR(NAME,1,20), SUBSTR(DEFAULTVALUE,1,30)
    FROM SYSIBM.SYSCOLUMNS
   WHERE TBNAME    = 'T2'
  
       -------------------------------------------------------
      !                      !                                !
       -------------------------------------------------------
    1_! A1                   ! -100000000000000000000         !
    2_! A2                   ! -100000000000000000000         !
    3_! C1                   ! -100000000000000000000.        !
    4_! C2                   ! -100000000000000000000.        !
       -------------------------------------------------------
  

Local fix

• N/A
  Additional Keywords:  DB2INCORR/K
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 V8 ALTER TABLE ADD COLUMN users.     *
  ****************************************************************
  * PROBLEM DESCRIPTION: ALTER TABLE ADD COLUMN stores wrong     *
  *                      value in SYSCOLUMNS.DEFAULTVALUE.       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  On ALTER TABLE ADD COLUMN, when the default value defined for
  the column is a decimal constant (SYSCOLUMNS.DEFAULT = 3) with
  leading plus sign (+) or minus sign (-) being specified, DB2
  incorrectly truncates the ending decimal point or the ending
  numeric digit and stores the truncated default value in the
  catalog SYSCOLUMNS.DEFAULTVALUE. This problem has no impact on
  the corresponding default value stored in the internal OBD, and
  correct default values are applied to the newly-added column
  for any existing data rows or newly-inserted data rows.
  
  When the ending decimal point is truncated, the subsequent
  running of REPAIR DBD DIAGNOSE on the containing database will
  encounter abend 04E-00E40600 @ DSNUT810.DSNUEXDB+600C. The
  subsequent running of CREATE TABLE LIKE with the source table
  being the affected table will fail by error SQLCODE -574 (THE
  SPECIFIED DEFAULT VALUE OR IDENTITY ATTRIBUTE VALUE CONFLICTS
  WITH THE DEFINITION OF COLUMN).
  
  When the ending numeric digit is truncated, the subsequent
  running of REPAIR DBD DIAGNOSE will fail by return code = 8
  with error like:
  DSNUEDRT -   OBDS DIFFER AT OFFSET X'00000164' OBDRUFUV
  DSNUEDRT -     EXISTING VALUE  X'3D9B6ED916872B01'
  DSNUEDRT -     EXISTING VALUE  X'C264C28F5C28F5C3'
  DSNUEDRT -     REBUILT  VALUE  X'3D9B70A3D70A3D70'
  DSNUEDRT -     REBUILT  VALUE  X'C264B33333333333'
  DSNUEDDR - OBD X'0003' COMPARISON COMPLETE -- 1 ERRORS
  
  The subsequent running of CREATE TABLE LIKE with the source
  table being the affected table will proceed and store the
  truncated default value in both catalog and OBD.
  

Problem conclusion

• The problem is fixed so on ALTER TABLE ADD COLUMN, correct
  default value is stored in the catalog SYSCOLUMNS.DEFAULTVALUE
  when the user-defined default value is a decimal constant with
  leading plus (+) or minus (-) sign being specified. The fix
  only prevents the future occurrence of the same problem, but
  does not correct any existing wrong catalog value. For the
  existing tables that have wrong default values stored in the
  catalog SYSCOLUMNS.DEFAULTVALUE, user needs to drop and
  recreate the table.
  
  ADDITIONAL KEYWORDS:
  SQLALTER
  ALTERTABLE
  ADDCOLUMN
  ABEND04E
  RC00E40600
  SQLCREATE
  CREATETABLELIKE
  SQLCODE574
  

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:

  UK44360

Modules/Macros

• DSNXIAB3
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK44360

     UP09/03/13 P F903

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.