PI36213: INCORRECT VALUE INSERTED WITH USER DEFAULT VALUE AFTER CONVERSION FROM INDEX TO TABLE CONTROLLED PARTITIONING

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Customer did to change from Index-controlled to
  Table-controlled  partitioning.
  .
  ALTER INDEX index1 NOT CLUSTER ;
  ALTER INDEX index1 CLUSTER ;
  .
  Package that was doing insert with trigger was not invalidated,
  thus, internal structure for insert was having wrong offset
  to modified OBD, and inserted wrong value, not from user
  default value.
  
  Keyword:
  SQLINSERT SQLALTER
  

Local fix

• after conversion from index to table controlled partitioning
  rebind package that is doing insert with default user value.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and 11 for z/OS users of ALTER    *
  *                 INDEX.                                       *
  ****************************************************************
  * PROBLEM DESCRIPTION: Incorrect user-defined default values   *
  *                      were inserted after converting from     *
  *                      an index-controlled partitioned table   *
  *                      space to a table-controlled partitioned *
  *                      table space by issuing ALTER INDEX      *
  *                      NOT CLUSTER SQL statement.              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The wrong default values were inserted by a trigger, which
  was not invalidated after converting from an index-controlled
  partitioned table space to a table-controlled partitioned
  table space.  After the conversion, any package using INSERT
  statements with user-defined default values might result in
  incorrect output.  Therefore, the package should be invalidated
  or explicitly rebound in order to get the correct results.
  REORG utility and SELECT statements are also affected.
  There may be other unexpected symptoms due to the conversion.
  The same table space conversion may also occur in the
  following paths:
  CREATE INDEX
  DROP INDEX
  ALTER TABLE ADD CLONE
  

Problem conclusion

• DB2 has been modified to invalidate packages and dynamically
  cached statements after converting from an index-controlled
  partitioned table space to a table-controlled partitioned
  table space.  This APAR will not fix any incorrect user-
  defined default values that may have already been inserted
  due to the conversion.  This APAR also will not fix incorrect
  packages or dynamically cached statements that reference
  the affected table and those incorrect packages/statements
  should be rebound.
  
  Additional Keywords:
  SQLALTER
  SQLCREATE
  SQLDROP
  SQLINSERT
  SQLTRIGGER
  SQLSELECT
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

• ×**** PE16/06/16 FIX IN ERROR. SEE APAR PI64320  FOR DESCRIPTION
  

APAR Information

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

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

  UI29092 UI29093

Modules/Macros

•    DSNXIAIX DSNXICIX DSNXICLN DSNXIDIX DSNXISB6
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI29092

     UP15/07/22 P F507 Ž

• RB10 PSY UI29093

     UP15/07/22 P F507 Ž

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.