IBM Support

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

A fix is available

Subscribe

You can track all active APARs for this component.

 

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 number

    PI36213

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2015-03-03

  • Closed date

    2015-07-02

  • Last modified date

    2016-07-25

  • 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.

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
06 May 2020