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.
SELECT Dept_ID,
ListAgg(ALL E_Name, '; ')
WITHIN GROUP (ORDR BY Birth, Emp_ID)
AS Name_List
FROM EMP
GROUP BY Dept_ID
ORDER BY Dept_ID DESC;
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
#Db2forz/OS#db2z13#Db2Znews