IBM Data and AI Ideas Portal for Customers
Hide about this portal


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


Add a new idea Subscribe
Status Not under consideration
Workspace Db2 for z/OS
Created by Guest
Created on Sep 10, 2020

RELATED IDEAS

A Sort-Merge Join should be considered when joining on a SUBSTR expression

See this idea on ideas.ibm.com

When tuning the following query, I saw that changing the "INNER JOIN" to a "FULL JOIN" caused it to perform many times better (which changed the join from a NLJ to a SMJ):

SELECT ARCHIVE_STRT.LOCAL_TS AS LOCAL_TS_STRT,

TIMESTAMPDIFF(2,

CAST(ARCHIVE_END.LOCAL_TS - ARCHIVE_STRT.LOCAL_TS

AS CHAR(22))) AS SECS_TO_ARCHIVE

FROM

(SELECT MSG_TS_GMT - 6

HOURS AS LOCAL_TS,

SUBSTR(MSG_TXT, 71, 20) AS RBA

FROM PDBR111.TDBR_IFI_MSG_TBL

WHERE MSG_TS_GMT > CURRENT TIMESTAMP - 12

DAYS

AND MSG_ID = 'DSNJ002I'

AND

MSG_TXT LIKE '%LOGCOPY1%'

) ARCHIVE_STRT

INNER JOIN

(SELECT MSG_TS_GMT - 6

HOURS AS LOCAL_TS,

SUBSTR(MSG_TXT, 99, 20) AS RBA

FROM PDBR111.TDBR_IFI_MSG_TBL

WHERE MSG_TS_GMT > CURRENT TIMESTAMP - 12

DAYS

AND MSG_ID = 'DSNJ003I'

AND MSG_TXT LIKE '%DB2ARC1%'

) ARCHIVE_END

ON ARCHIVE_STRT.RBA = ARCHIVE_END.RBA

ORDER BY

LOCAL_TS_STRT

WITH UR ;

But, Db2 will not pick a SMJ for this query. I worked with a case for this (TS003109448) and was told that Db2 does not support joining with a SMJ on a SUBSTR expression. If this join could happen in this way, the query above would perform much better.

Needed by Date Sep 1, 2021
  • Admin
    Janet Figone
    Reply
    |     Nov 12, 2020

    Thank you for submitting this enhancement request to the Db2 for z/OS team. We have reviewed it and determined it will not be implemented. Sparse index already provides the capability to join on an expression.

    Enhancements will be made to sparse index rather than sort merge join.

    We value your input, so please continue to submit your enhancement requests for our consideration.

    Thank you,

    The Db2 for z/OS Development team

By clicking the "Post Comment" or "Submit Idea" button, you are agreeing to the IBM Ideas Portal Terms of Use.
Do not place IBM confidential, company confidential, or personal information into any field.