Skip to Main Content
Cloud Platform


This is an IBM Automation portal for Cloud Platform products. 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
  1. Post an idea.

  2. Get feedback from the IBM team and other customers to refine your idea.

  3. Follow the idea through the IBM Ideas process.


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.


Status Delivered
Workspace WebSphere Liberty
Created by Guest
Created on Feb 4, 2021

Enhance parameter binding in EclipseLink


In 2.6.4 of EclipeLink, parameter binding was selective in DatabasePlatform (Db2). On 10/23/2018, 1st problem occurred where arithmetic functions (SQRT, ABS, SUBSTR, etc) were not bound when they were used.

APAR PH06475 created and provided "eclipselink.jdbc.force-bind-parameters". In this property, both parameters and literals are bound regardless of the DatabasePlatform (Db2) selection.

After applying APAR PH06475, Some SQL errors occurred due to literals bound by force-bind that did not match Db2 rules.
 
On 2019/07/05, To resolve the situation, created and applied APAR PH14747, which does not bind literals. It is current level.


Parameters are bound even if they result in SQL errors because of binding forcely when using "eclipselink.jdbc.force-bind-parameters".
- So, application modification is required.

Literals are not bound at all because of disable by PH14747.
-no benefit of performance improvement.


Reference: 47176,617,760 48275,617,760 TS003562127 TS004215433 TS003550524 TS003292625 TS004603445


The previous EclipeLink 2.6.4 selective parameter binding is useful, SOMPO wants EclipseLink to perform the selective parameter binding regardless of functions (ABS, SQRT, MOD, etc ...) in the query.

In addition, bind literals that can be host variables (parameters that do not cause SQL errors) as much as possible.

Idea priority Urgent
RFE ID 148362
RFE URL
RFE Product WebSphere Application Server
  • Admin
    Alasdair Nottingham
    Reply
    |
    Jun 7, 2022

    This was delivered in Liberty 22.0.0.6.

  • Guest
    Reply
    |
    Feb 15, 2021

    I want to add more context to this request. This request is to improve EclipseLink's performance for DB2 queries by altering parameter binding behavior.

    By default, EclipseLink disables parameter binding for queries that contain function expressions (like ABS, SQRT, or CASE expressions). EclipseLink makes this decision to disable binding for the WHOLE query when EclipseLink parses any function in the query. I think the best way to describe this request is with some examples.

    Example 1:
    ```
    Query query = em.createQuery("SELECT s FROM SimpleEntity s WHERE s.intVal1 = ?1 AND s.intVal2 = ?2");
    query.setParameter(1, 16);
    query.setParameter(2, 26);
    ```

    With Example 1, EclipseLink creates and executes the following SQL query:
    ```
    SELECT ID, INTVAL1, INTVAL2, STRVAL1 FROM SIMPLEENTITY WHERE ((INTVAL1 = ?) AND (INTVAL2 = ?))
    bind => [16, 26]
    ```

    EclipseLink binds the given parameters as query parameters. This allows the query to be stored with parameters in the prepared statement cache and improves performance.

    Example 2:
    ```
    Query query = em.createQuery("SELECT s FROM SimpleEntity s WHERE s.intVal1 = ?1 AND s.intVal2 = ?2 AND s.intVal1 = ABS(36)");
    query.setParameter(1, 16);
    query.setParameter(2, 26);
    ```

    With Example 2, EclipseLink creates and executes the following SQL query:
    ```
    SELECT ID, INTVAL1, INTVAL2, STRVAL1 FROM SIMPLEENTITY WHERE (((INTVAL1 = 16) AND (INTVAL2 = 26)) AND (INTVAL1 = ABS(36)))
    ```

    When EclipseLink parses the JPQL query and constructs the SQL, EclipseLink disables parameter binding for the whole query. This is a decision that EclipseLink makes, for DB2, regardless of validity. In other words, EclipseLink makes this decision at a very high level, for all functions. It does this because there are known function expressions that are invalid to use parameter markers on DB2. For instance, it is illegal in DB2 for CASE expressions to contain untyped parameter markers in the THEN and ELSE `result-expression` (https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/codes/src/tpc/n418.html). Rather than making a fine, detailed decision, EclipseLink disables all parameter binding for the query if any function appears in the query.

    This RFE requests allowing EclipseLink to make a finer detailed decision and partially allowing parameter binding decisions.

    For instance, instead, the SQL EclipseLink would generate for Example #2 would be:
    ```
    SELECT ID, INTVAL1, INTVAL2, STRVAL1 FROM SIMPLEENTITY WHERE (((INTVAL1 = ?) AND (INTVAL2 = ?)) AND (INTVAL1 = ABS(36)))
    bind => [16, 26]
    ```
    With this RFE, EclipseLink would allow partial parameter binding instead of globally making the decision for the whole query. This will require a change to EclipseLink's JPQL parsing behavior and a large amount of testing. However, the performance benefit would allow more database cache hits and increased performance.