DB2 - L

 View Only
  • 1.  DB2 z/os - RSU2204 - Bind Isolation

    Posted May 13, 2022 08:43 AM
    I have (DB2 z/os) RSU2204 Received and i am reviewing the Hold Actions, I see this one below, and I run the sql and find i have 30 of them. I'm reviewing the package and the plan properties, and I see the Plan has isolation of CS, so correct me if i'm wrong, BUT if you do not specify an isolation setting on the BIND PKG, then it should pick it up from the PLAN. So I really should not need to correct these, and i can simply run DSNTIJCL in the SDSNSAMP library, but if the DB2 code is not picking the isolation setting from the PLAN, then i need to set it in the BIND PKG statements. Futhermore, IBM needs to correct the DB2 code per the manual, the isolation default should come from the PLAN when not specified in the PKG statements. The package names returned in the sql result were all in DSNTIJCL, just different version levels of the modules.

    UI80317 ++ HOLD(UI80317) SYS FMID(HDBCC10) REASON(ACTION) DATE(22116)

    COMMENT

    (****************************************************************

    * Function Affected: *

    * DB2 zOS Base *

    ****************************************************************

    * Timing: *

    * Pre-APPLY *

    ****************************************************************

    This PTF corrects a query problem when a DBAT thread is

    using ISOLATION(RR) instead of ISOLATION(CS). In order to

    determine whether a package may have this issue, please check

    the packages that are part of the following query:

    SELECT LOCATION, COLLID, NAME, CONTOKEN, ISOLATION

    FROM SYSIBM.SYSPACKAGE

    WHERE ISOLATION = ' ';

    Please correct the bind option of the package to enable ISOLATION(CS).).

    ------------------------------
    LynMaberryGeorgia Farm Bureau Insurance Company
    ------------------------------


  • 2.  RE: DB2 z/os - RSU2204 - Bind Isolation

    Posted May 13, 2022 09:15 AM

    Hello,

      We similarly had questions regarding this PTF and it's 'impact'...the PTF speaks to distributed with no isolation yet the SQL provided to list all 'impacted' packages includes STATIC packages as well (which as you note will default to plan level isolation if blank).  We have opened a ticket with IBM asking if the provided SQL should have "      AND REMOTE <> 'N' "    appended to it to ensure only DISTRIBUTED packages are reported...

     

    Roger.                                                          

     

     

    --------------------------------------------------------------------------------------------------------------------

    Roger Tieche  | Db2 IT Specialist | Database Technical Services  | Group ITS-ISD-DCSS-MD-DTSDB2

     

    Db2 FAQ site:  Link

     

    TD Bank Group | ( Work: 416.982.5132  | ) Mobile: 416.845.2497 | * roger.tieche@td.com

     

     

    Internal






  • 3.  RE: DB2 z/os - RSU2204 - Bind Isolation

    Posted May 13, 2022 09:48 AM
    If I add " AND REMOTE <> 'N' " , then i get zero rows returned, and I would have no Action to perform.

    Please let me know what IBM responds with.

    Thank you,
    Lyn Maberry



    ------------------------------
    LynMaberry , Georgia Farm Bureau Insurance Company
    ------------------------------



  • 4.  RE: DB2 z/os - RSU2204 - Bind Isolation

    Posted May 17, 2022 09:22 AM

    Hello Lyn!

     

      After some back and forth IBM has 'clarified' their stance with the following:

     

    " Here is the response from the APAR Owner:

    In general, to be as safe as possible, yes, they should rebind all their packages with a blank isolation with an explicit isolation level.

    Do they really need to rebind all of them? Perhaps not.

    To get this problem, they need to have a plan bound with ISOLATION(RR). If they don't have any of those, they don't need to worry at all. If they do have iso(rr) plans, if those plans do not use packages with blank iso, then again nothing to worry about.

    They only have to worry if they have iso(rr) plans that use iso(blank) packages, and those packages do index skipping in their access path.

    So, it just depends on how much digging they want to do to find out which packages are affected.

    No digging: rebind all packages with blank iso"

     

      But considering the Apar speaks to distributed I still question the whole exercise above for STATIC packages/plans and I have also asked why the highlighted section above regarding Index Skipping is not mentioned in APAR at all!

     

    The discussion continues...

     

     

    --------------------------------------------------------------------------------------------------------------------

    Roger Tieche  | Db2 IT Specialist | Database Technical Services  | Group ITS-ISD-DCSS-MD-DTSDB2

     

    Db2 FAQ site:  Link

     

    TD Bank Group | ( Work: 416.982.5132  | ) Mobile: 416.845.2497 | * roger.tieche@td.com

     

     

    Internal






  • 5.  RE: DB2 z/os - RSU2204 - Bind Isolation

    Posted May 17, 2022 11:37 AM
    Thank you Roger for the follow up. Saved me the time and energy creating the ticket and arguing the same position.


    Thanks,

    ------------------------------
    LynMaberry , Georgia Farm Bureau Insurance Company
    ------------------------------