Db2 for z/OS and its ecosystem

 View Only

LISTAGG results can be ordered with an ORDER BY clause in DB2 13 for z/OS at function level 504

By Cynthia Suo posted Wed December 13, 2023 07:13 PM


Starting in Db2 13 at function level 504, and controlled by application compatibility level V13R1M504 or higher, DBAs and application developers who are using a fullselect that contains an invocation of LISTAGG with an ORDER BY clause can now execute them successfully. Prior to this enhancement, attempting to run such queries resulted in a SQLCODE = -390 error message.

To illustrate the limitation that exists before Db2 13 at function level 504, consider a straightforward example using the LISTAGG aggregate functionality. Let's take an employee table named EMP and attempt to display an employee name list for each department based on their birthdate and employee ID. LISTAGG is an aggregate function, and the GROUP BY clause is specified. By default, the GROUP BY clause sorts the data in ascending order. Consequently, the display would consistently show the employees' name list for department 10 first, followed by departments 20 and 30, as shown in the following illustration.

One notable limitation was the inability for DBAs or application developers to customize the display order to descending. The SQLCODE = -390 error prevented them from arranging the results in a specific display preference. This constraint became a significant pain point for both DBAs and application developers. For example, consider the following modified example query.

ListAgg(ALL E_Name, '; ')
AS Name_List

The inclusion of the ORDER BY Dept_ID DESC clause causes applications that run in Db2 for z/OS at application compatibility level V13R1M503 or lower to receive SQLCODE = -390.

However, DBAs and application developers sometimes need to showcase sales data on a monthly, quarterly, or yearly basis. Their preference is to present the most recent month, quarter, or year first. Achieving this result requires the use of the LISTAGG aggregate function along with the ORDER BY DESC clause.

To enhance the usability of the LISTAGG aggregate function and ensure compatibility across various IBM database products, significant improvements have been introduced in LISTAGG. The improvement addresses a prior restriction, denoted by SQLCODE -390, where the use of ORDER BY with LISTAGG was limited.

The previous constraint is now eliminated for applications that run in Db2 13 for z/OS at application compatibility level V13R1M504 or higher, enabling the LISTAGG aggregate function to work seamlessly with an ORDER BY clause. A fullselect that contains an invocation of LISTAGG will consistently allow the inclusion of an ORDER BY clause. And this enhancement allows the full syntax of the ORDER BY clause to be used in a fullselect with LISTAGG, as shown in the following diagram. The ORDER BY sort-key ASC or DESC clause gives our customers their preferred choice and ORDER BY sort-key includes column-name, integer, and sort-key-expression.

As demonstrated in the following example, DBAs and application developers now have the capability to showcase query results in their preferred and meaningful order. For instance, by employing the ORDER BY clause with the Dept_ID DESC clause, users can arrange the output in descending order. Consequently, the employee name list for department 30 is presented first in the results, offering a more tailored and intuitive display.

This enhancement provides DBAs and application developers with increased flexibility by alleviating previous constraints, facilitating effortless customization of the display order based on individual preferences. The enhancements introduced in LISTAGG foster a more adaptable and user-friendly experience. Users now have a refined level of control over their data display preferences, further enhancing their overall interaction with the system.

For more information about using LISTAGG aggregate function, see the following online product documentation topics:

Cynthia Suo is a Senior Software Engineer in Db2 for z/OS development




Thu February 01, 2024 08:22 PM

Dear Bian,

Thank you so much for your kind words! I am thrilled to hear that you find the recent enhancement important and beneficial. 

I wanted to take a moment to express my gratitude for your active participation as one of the voters in Aha Idea. Your insights and contributions play a crucial role in shaping the direction of our initiatives. And we can't wait for you to experience the improvements firsthand when you get to v13 fl504. Your feedback means a lot to us, and we're committed to continuously enhancing our services to meet your expectations.

If you have any further questions or if there's anything else we can assist you with, please feel free to reach out. We value your partnership and look forward to delivering an even better experience.

Warm regards,

Cynthia Suo

Wed January 31, 2024 11:15 AM

This is an important simple enhancement.  I am glad to see it implemented.  I can't wait to validate when I (finally) get to v13 fl504