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


Add a new idea Subscribe
Status Delivered
Workspace Db2 for z/OS
Created by Guest
Created on Aug 16, 2019

RELATED IDEAS

allow SELECT with LISTAGG function to always allow ORDER BY

I am using DB2 Version 12. function level 501

 

I have a simple SQL example I show my co-workers to show the power of

new DB2 SQL function of LISTAGG

 

SELECT IXCREATOR, IXNAME

 ,LISTAGG(ALL COLNAME,',') WITHIN GROUP (ORDER BY COLSEQ) AS

ALL_COLS

FROM SYSIBM.SYSKEYS

WHERE 1=1

 AND IXCREATOR = 'SYSIBM'

GROUP BY IXCREATOR, IXNAME

;

 

The above query works in any DB2 subsystem and produces a result set.

> Go ahead and try it... it should run for you too.

 

 

If I add an ORDER BY to the end of the query, it fails with SQLCODE -390

 

SELECT IXCREATOR, IXNAME

 ,LISTAGG(ALL COLNAME,',') WITHIN GROUP (ORDER BY COLSEQ) AS

ALL_COLS

FROM SYSIBM.SYSKEYS

WHERE 1=1

 AND IXCREATOR = 'SYSIBM'

GROUP BY IXCREATOR, IXNAME

order by ixcreator, ixname

;

 

And yes, I read the IBM knowledge center documentation on SQLCODE -390.

The list of possible causes for SQLCODE -390 includes this bullet point

> ARRAY_AGG or LISTAGG is referenced in a fullselect that includes an ORDER BY clause or a DISTINCT clause.

 

My SQL example seems to fall into this bullet point reason.

So I am not allowed to execute the above SELECT with LISTAGG with ORDER BY

 

I opened CASE TS002533212  (PMR 04496,057,649) to ask about this scenario (because I cannot believe it is true).

And essentially I was told that yes, that is proper behavior…. But LISTAGG could work with ORDER BY… but changes to DB2 are require!

 

Hence, I am making this RFE!

 

Basically, I think it would be reasonable for me to want to always be able to use ORDER BY (if I choose) with LISTAGG

  • I am embarrassed to explain this weird sub-case of using LISTAGG… sometimes you can’t use ORDER BY… it seems weird.
  • Admin
    Janet Figone
    Reply
    |     Sep 25, 2023

    Hello Brian, This is currently in development.

  • Admin
    Janet Figone
    Reply
    |     Mar 9, 2023

    Hello Brian, Our development team revisited this idea today and plan to discuss it further with another Db2 component area. I will update here when additional feedback is provided.

    Sincerely,

    The Db2 for z/OS Team

  • Admin
    Janet Figone
    Reply
    |     Dec 14, 2022

    Hello Brian,

    As per our email exchange, the SME assigned to this Aha! idea does have a team member with a simple prototype in mind, but they need more time to work on it.

    As per Aha! guidelines, if an enhancement is not likely to begin development or be delivered, in the upcoming 12 months, we need to be transparent and indicate it is not under consideration. This is not necessarily a decline. We do plan to revisit this request in 2023.

    When we have more information from the SME assigned to this idea, it will be shared here.

    We appreciate your input to the Db2 for z/OS development team. And we hope that you will continue to submit enhancement suggestions for improvements as customer feedback is a key component to shaping the future direction of Db2 for z/OS.

    Sincerely,

    The Db2 for z/OS Team

  • Guest
    Reply
    |     Dec 8, 2022

    ok... the idea is not under consideration. but I am still awaiting to my previous comment. why is not under consideration? too much effort? not important enough? I will argue that this is more important and has higher impact then some other ideas. It is embarassing that LISTAGG does not work as it should

  • Guest
    Reply
    |     Jun 20, 2022


    this ides is "not under consideration". Fine. But I wish you provide more feedback to me about why you rejected this idea. I really feel it is an important idea. And a low cost idea. And an idea that values to many end-users and developers and DBA who use ad-hoc SQL with DB2. It makes our life easier. Many people would benefit.


    Is the effort to fix so hard? I don't know ... you tell me. But it seems easy to me.


    Remember, it is embarrassing to explain this limitation to others. Every other RDMS allows this equivalent functionality. It also works in Db2 LUW! So why not Db2 ZOS?


    -- LISTAGG with ORDER BY is successful in DB2 LUW (example here)

    -- <this is similiar to the Db2 ZOS example in the desc w -390>

    SELECT INDSCHEMA, INDNAME

    ,LISTAGG(ALL COLNAME,',') WITHIN GROUP (ORDER BY COLSEQ) AS

    ALL_COLS

    FROM SYSCAT.INDEXCOLUSE

    WHERE 1=1

    AND INDSCHEMA= 'SYSIBM'

    GROUP BY INDSCHEMA, INDNAME

    ORDER BY INDSCHEMA, INDNAME

    ;






  • Guest
    Reply
    |     Dec 8, 2020

    Does IBM have any feedback on this idea?

    It seems logical ... and in my opinion... it should not be hard to implement.

    It is an annoying limitation of LISTAGG and I am embarrassed to explain it to my end-users!

    Like I said earlier... I opened up a CASE with IBM support last year... they said LISTAGG is working as designed and documented. Ok. fine and true. But the case response also said it could be 'corrected' ... so I made this RFE..

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.