Skip to Main Content
IBM Data and AI Ideas Portal for Customers


This portal is to open public enhancement requests against products and services offered by the IBM Data & AI organization. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).


Shape the future of IBM!

We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:


Search existing ideas

Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updates on them if they matter to you. If you can't find what you are looking for,


Post your ideas

Post ideas and requests to enhance a product or service. Take a look at ideas others have posted and upvote them if they matter to you,

  1. Post an idea

  2. Upvote ideas that matter most to you

  3. Get feedback from the IBM team to refine your idea


Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.

ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

IBM Employees should enter Ideas at https://ideas.ibm.com


Status Delivered
Workspace Db2 for z/OS
Created by Guest
Created on Nov 17, 2020

UTS PBG, SINGLETON SELECT and Lock Avoidance.

Hello Db2 for Z/OS Development.

Apar PM89016 was created several years ago to implement the lock avoidance for singleton SELECTs with

ISO(CS) and CD(YES) when R-scan is used, or the index that is chosen has

matching predicates on all columns of the index.

Recently a new Apar PH19483 (UI67378) has reverted this behaviour to avoid receiving unexpected SQLCODE +100 when there is INSERT/DELETE/UPDATE activity happening concurrently on the same table.

As can be read on the text of such new Apar PH19483:

"PROBLEM CONCLUSION:

Db2 code is changed to acquire locking for singleton SELECTs meeting the criteria described above. As a result of this change, the user may see increased locking activity and slower query performance for such singleton SELECTs; the locking is mandatory to prevent concurrent operations from causing the incorrect output. If the user desires less locking activity, consider using CURRENTDATA(NO). "

In our case (Bankinter) 80% of our read Accesses are Singleton Selects, resolved with skinny index-to-data (equal predicates on all index columns) access instead of Cursors, so the impact of not "profiting" of lock avoidance feature on those singleton Selects with ISO(CS) and CD(YES) is huge. Furthermore We can't use CURRENTDATA(NO) due to our business processes read with Singleton Select most of the times, but they also read by read-only (or ambiguous) cursors and modify afterwards with Delete or Update statements.

Needed by Date Dec 31, 2020
  • Guest
    Reply
    |
    Sep 26, 2023

    We are delighted with the new Zparm LA_SINGLESEL_ISOCS_CDY that specifies whether Db2 enables lock avoidance for singleton SELECT with ISOLATION(CS) CURRENTDATA (YES).

    We'll apply this afternoon in Pre-Production Db2 environment. Thanks for your effort

  • Admin
    Janet Figone
    Reply
    |
    Dec 17, 2020

    Thanks for bringing this concern. PH19483 resolved intermitted incorrect out with CD(YES) when SELECT and DELETE are executed concurrently. While the change is to honor the semantics of ISO(CS) CD(YES), we understand the performance penalty from the lack of lock avoidance. We will conduct further investigation if there is any solution