A fix is available
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