PK57452: ALC NULL TO NOT NULL NO DEFAULT DATA LOAD ISSUES

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The DB2 Administration Tool ALC command creates load data
  which may be inappropriate or unexpected when used to change
  from a NULLable column format to a NOT NULL with no default
  column format.
  For example a NULL integer column may contain -2147483648
  after the change while a TIMESTAMP column will result in a LOAD
  error DSNU334I ERROR CODE '14' REASON=X'00E40323'.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Admin users that use ALC to change           *
  *                 column attributes from NULL to NOT           *
  *                 NULL without providing a default value.      *
  ****************************************************************
  * PROBLEM DESCRIPTION: The DB2 Administration Tool ALC         *
  *                      function may result in data that may    *
  *                      be inappropriate or unexpected, if a    *
  *                      column is changed from a NULL to NOT    *
  *                      NULL with no default provided.          *
  ****************************************************************
  * RECOMMENDATION: APPLY the PTF.                               *
  ****************************************************************
  When a null value exists for a column that is being changed from
  NULL to NOT NULL, a value must be provided in the input file to
  LOAD.  In a set of conversions allowed by ALC, the value was set
  to an arbitrary numeric value.  For conversion to TIMESTAMP,
  DATE or TIME, the LOAD utility may abend due to an invalid
  format condition.
  

Problem conclusion

• The ALC logic is being changed to force data conversion when a
  column is being changed from NULL to NOT NULL without a default
  for certain conversions.  For changes where the target column
  data type is SMALLINT, INTEGER, FLOAT or DECIMAL, a null value
  will be set to 0.  For changes where a target column is CHAR,
  VARCHAR, LONG VARCHAR, a null value will be set to blanks.  For
  changes where a target column is TIMESTAMP, DATE or TIME, a null
  value will be set to the DB2 minimum value for the respective
  target type.
  Message ADB332A will be displayed on the panel when a candidate
  change of this type occcurs.  Message ADBC039W, ADBC040W,
  or ADBC041W will be written to the SYSPRINT file of the ADB27AC
  step to report when that null data may be converted for the
  candidate changes.
  The following data type conversions are candidate changes if
  the original is defined as NULL and the new is defined as NOT
  NULL and no default is provided:
   SMALLINT->SMALLINT      SMALLINT->INTEGER  SMALLINT->FLOAT
   SMALLINT->DECIMAL       INTEGER->SMALLINT  INTEGER->INTEGER
   INTEGER->FLOAT          INTEGER->DECIMAL   FLOAT->SMALLINT
   FLOAT->INTEGER          FLOAT->FLOAT       FLOAT->DECIMAL
   DECIMAL->SMALLINT       DECIMAL->INTEGER   DECIMAL->FLOAT
   DECIMAL->DECIMAL        CHAR->DECIMAL      CHAR->SMALLINT
   CHAR->INTEGER           VARCHAR->SMALLINT  VARCHAR->INTEGER
   DECIMAL->DATE           CHAR->DATE         VARCHAR->DATE
   LONG VARCHAR->DATE      DATE->DATE         TIMESTAMP->DATE
   DECIMAL->TIME           CHAR->TIME         VARCHAR->TIME
   LONG VARCHAR->TIME      TIME->TIME         TIMESTAMP->TIME
   DECIMAL->TIMESTAMP      CHAR->TIMESTAMP    VARCHAR->TIMESTAMP
   LONG VARCHAR->TIMESTAMP DATE->TIMESTAMP    TIMESTAMP->TIMESTAMP
   DECIMAL->CHAR           DECIMAL->VARCHAR   SMALLINT->CHAR
   SMALLINT->VARCHAR       INTEGER->CHAR      INTEGER->VARCHAR
   CHAR->CHAR              CHAR->VARCHAR      CHAR->LONG VARCHAR
   DATE->CHAR              DATE->VARCHAR      TIME->CHAR
   TIME->VARCHAR           TIMESTAMP->CHAR    TIMESTAMP->VARCHAR
  

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:

Modules/Macros

•    ADBCDTS  ADB2PA6  ADB27AC  ADB27C   ADB27CU
  ADB27DUP ADB33
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• R720 PSY UK35295

     UP08/04/16 P F804

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.