IBM Support

PM60533: DB2 10 (CM OR HIGHER): INCORRECT OUTPUT POSSIBLE ON TABLES THAT HAVE GONE THROUGH DDL ALTER ADD COLUMN SINCE ARRIVAL TO 10

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • In DB2 10 CM or higher, incorrout is possible for tables that
    have gone through ALTER TABLE ADD COLUMN in V10 plus a REORG,
    and no rebind has been done on packages using that table.
                       ------------------
    In DB2 10, a new, faster predicate evaluation mechanism takes
    over - the Predicate Proc (P Proc). The P Proc gets built at
    bind time as long as the selection expression does not involve
    host variables with LIKE / NOT LIKE predicate. Such packages
    (P Proc built at bind time) are affected by this problem.
                       ------------------
                                     (d156391)
                       ------------------
    The following is a sample scenario that exposes this problem:
    
    1) Populate the table with some rows
    2) BIND an SQL SELECT application that does not use host
       variables with LIKE/NOT LIKE
    3) ALTER TABLE ADD COLUMN (rows now become downlevel-versioned)
    4) REORG (rows now become currentlevel-versioned (MSANCVF=NO))
    5) Run the SQL SELECT application -> Incorrout
                       ------------------
    Additional keywords: DB2INCORR/K SQLINCORR SQLINCORROUT
                       ------------------
    On April 4 2012, it has come to attention that another problem
    exists that will be fixed by this APAR - storage leak in PHB
    CTLNGSTP, showing a large number of P Procs built (eyecatcher
    'Selp') for the same MSIB block. This is not OK because there
    should be only one P Proc per MSIB. The symptom of this second
    problem is ABEND04E RC00E20003 DSNIGSEL.DSNSVBK+070E.
                       ------------------
    This second problem is independent from the first problem
    but the APAR will fix both issues.
                       ------------------
    The following is a sample scenario that exposes this second
    problem:
    
    1) Populate the table with a very large number of rows
       (e.g. 70,000+)
    2) BIND an SQL SELECT application that does not use host
       variables with LIKE/NOT LIKE
    3) ALTER TABLE ADD COLUMN (rows now become downlevel-versioned
       (MSANCVF=YES))
    4) Run the SQL SELECT application -> Storage leak with Selp's
                       ------------------
    

Local fix

  • For the first problem:
    
    For any table that has gone through ALTER TABLE ADD COLUMN since
    arrival in 10, a query such as this one can be used to identify
    any plans/packages that use the table but have not been rebound
    since the ALTER:
    
    SELECT PKG.COLLID, PKG.NAME, PKG.TYPE
    FROM SYSIBM.SYSPACKAGE PKG,
    SYSIBM.SYSPACKDEP PKD,
    SYSIBM.SYSTABLES TAB,
    SYSIBM.SYSCOLUMNS COL
    WHERE PKG.COLLID = PKD.DCOLLID
    AND PKG.NAME = PKD.DNAME
    AND PKG.CONTOKEN = PKD.DCONTOKEN
    AND TAB.CREATOR = PKD.BQUALIFIER
    AND TAB.NAME = PKD.BNAME
    AND PKD.BTYPE = 'T'
    AND TAB.CREATOR = COL.TBCREATOR
    AND TAB.NAME= COL.TBNAME
    AND TAB.CREATEDTS < COL.CREATEDTS
    AND COL.CREATEDTS > PKG.BINDTIME;
                       ------------------
    Until the APARfix is available, the identified plans/
    packages can be rebound to bypass this incorrout problem.
                       ------------------
    For the second problem, the bypass is to rebind plans/packages
    identified by the query and then do a REORG. The REORG will
    bring all rows to currentlevel-versioned, and such rows will
    not be in the code path where the exposure is for this second
    problem.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 for z/OS users (CM and above) of  *
    *                 ALTER TABLE ADD COLUMN on tables used by     *
    *                 bound application plans                      *
    ****************************************************************
    * PROBLEM DESCRIPTION: The APAR addresses two problems:        *
    *                                                              *
    *                      1. Incorrect output is possible on      *
    *                      tables which have gone through ALTER    *
    *                      TABLE ADD COLUMN since arrival to 10    *
    *                      CM or higher, without a subsequent      *
    *                      rebind on plans/packages using such     *
    *                      tables.                                 *
    *                                                              *
    *                      2. ABEND04E RC00E20003                  *
    *                      DSNIGSEL.DSNSVBK+070E due to storage    *
    *                      leak in the internal DB2 storage pool   *
    *                      that is used for P Procs.               *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    Problem 1:
    
    In DB2 10 CM or higher, incorrout is possible for a table that
    has gone through ALTER TABLE ADD COLUMN in 10 and a REORG, but
    no rebind has been done on plans/packages using that table.
    
    In DB2 10, a new, faster predicate evaluation mechanism takes
    over - the Predicate Proc (P Proc). The P Proc gets built at
    bind time as long as the selection expression does not involve
    host variables with LIKE / NOT LIKE predicate. DDL ALTER
    TABLE ADD COLUMN will not invalidate bound plans/packages
    containing the P Proc. If a REORG is run after the DDL ALTER
    ADD COLUMN and then the plan/package itself, the plan/package
    may get incorrect output because the P Proc in it may be
    affected by the change in positions of columns after REORG and
    all rows are currentlevel-versioned.
    
    The following is a sample scenario that exposes this problem:
    
    (1) Populate the table with some rows
    (2) BIND an SQL SELECT application that does not use host
        variables with LIKE/NOT LIKE
    (3) ALTER TABLE ADD COLUMN (rows now become downlevel-versioned)
    (4) REORG (rows now become currentlevel-versioned)
    (5) Run the SQL SELECT application -> Incorrout
    
    
    Problem 2:
    
    Storage leak may occur in internal DB2 pool CTLNGSTP, with the
    pool showing a large number of P Procs built (eyecatcher
    'Selp') for the same MSIB block, when rows are downlevel-
    versioned. This is not OK because there should be only one
    P Proc per MSIB. The symptom of this second problem is
    ABEND04E RC00E20003 DSNIGSEL.DSNSVBK+070E.
    
    The root cause for the storage leak is that DB2 forgets to
    reset an internal record counter for P Proc building. The
    counter keeps incrementing and overflows, leading to a new
    P Proc being built every 65,000+ rows.
    
    The following is a sample scenario that exposes this second
    problem:
    
    (1) Populate the table with a very large number of rows
        (e.g. 70,000+)
    (2) BIND an SQL SELECT application that does not use host
        variables with LIKE/NOT LIKE
    (3) ALTER TABLE ADD COLUMN (rows now become downlevel-versioned)
    (4) Run the SQL SELECT application -> Storage leak with Selp's
    

Problem conclusion

  • DB2 code is fixed to associate the table version with the P Proc
    that was built at bind time.
    
    At execution time, DB2 will compare the versions of the
    evaluated row and the bind-time P Proc. If they match, the code
    will use the bind-time P Proc, otherwise it will build a new
    P Proc on the fly.
    
    Due to this building of P Proc at execution time, after applying
    this PTF, applications that have not been rebound since ALTER
    TABLE ADD COLUMN might experience performance degradation. To
    improve the performance of these applications, they can be
    rebound.
    
    For any table that has gone through ALTER TABLE ADD COLUMN since
    arrival in 10, a query such as this one can be used to identify
    any plans/packages that use the table but have not been rebound
    since the ALTER:
    
    SELECT PKG.COLLID, PKG.NAME, PKG.TYPE
    FROM SYSIBM.SYSPACKAGE PKG,
    SYSIBM.SYSPACKDEP PKD,
    SYSIBM.SYSTABLES TAB,
    SYSIBM.SYSCOLUMNS COL
    WHERE PKG.COLLID = PKD.DCOLLID
    AND PKG.NAME = PKD.DNAME
    AND PKG.CONTOKEN = PKD.DCONTOKEN
    AND TAB.CREATOR = PKD.BQUALIFIER
    AND TAB.NAME = PKD.BNAME
    AND PKD.BTYPE = 'T'
    AND TAB.CREATOR = COL.TBCREATOR
    AND TAB.NAME= COL.TBNAME
    AND TAB.CREATEDTS < COL.CREATEDTS
    AND COL.CREATEDTS > PKG.BINDTIME;
    
    Additional keywords: DB2INCORR/K SQLINCORR SQLINCORROUT
                         DB2STGLK/K
    

Temporary fix

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

Comments

APAR Information

  • APAR number

    PM60533

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-03-15

  • Closed date

    2012-05-11

  • Last modified date

    2012-06-03

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

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

    UK78700

Modules/Macros

  • DSNIACCH DSNIADR  DSNICRFT DSNIESEN DSNIGSEL
    DSNIHSET DSNIMNEX DSNIONX2 DSNIOST2 DSNIRNXT DSNIRPRE DSNIRSET
    DSNISFRL DSNISFS  DSNISFWL DSNISFX2 DSNISRID DSNIWNRF DSNUUSEL
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK78700

       UP12/05/30 P F205

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":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
03 June 2012