Skip to Main Content
Cloud Platform

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:

Post your ideas

Start by posting 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

Help IBM prioritize your ideas and requests

The IBM team may need your help to refine the ideas so they may ask for more information or feedback. The offering manager team will then decide if they can begin working on your idea. If they can start during the next development cycle, they will put the idea on the priority list. Each team at IBM works on a different schedule, where some ideas can be implemented right away, others may be placed on a different schedule.

Receive a notification on the decision

Some ideas can be implemented at IBM, while others may not fit within the development plans for the product. In either case, the team will let you know as soon as possible. In some cases, we may be able to find alternatives for ideas which cannot be implemented in a reasonable time.

If you encounter any issues accessing the Ideas portals, please send email describing the issue to ideasibm@us.ibm.com for resolution.

For more information about IBM's Ideas program visit ibm.com/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
    Jun 7, 2022

    This was delivered in Liberty 22.0.0.6.

  • Guest
    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.