Function level 501 (activation enabled by APAR PI70535 - May 2017)
Function level 501 (V12R1M501) introduces the LISTAGG built-in function, which produces a list of all values in a group.
The LISTAGG built-in function
The LISTAGG built-in function produces a list of all values in a group. An optional separator argument can delimit items in the result list. For example, specifying a comma as the separator produces a comma-separated list. An optional ordering can also be specified for the items within the group.
Produce an alphabetical list of comma-separated names, grouped by department, from the sample employee table.
SELECT WORKDEPT, LISTAGG(LASTNAME, ', ') WITHIN GROUP(ORDER BY LASTNAME) AS EMPLOYEES FROM EMP GROUP BY WORKDEPT;
The following result is returned.
---------+---------+---------+---------+---------+---------+---------+---------+---------+------- WORKDEPT EMPLOYEES ---------+---------+---------+---------+---------+---------+---------+---------+---------+------- A00 HAAS, HEMMINGER, LUCCHESI, O'CONNELL, ORLANDO B01 THOMPSON C01 KWAN, NATZ, NICHOLLS, QUINTANA D11 ADAMSON, BROWN, JOHN, JONES, LUTZ, PIANKA, SCOUTTEN, STERN, WALKER, YAMAMOTO, YOSHIMURA D21 JEFFERSON, JOHNSON, MARINO, MONTEVERDE, PEREZ, PULASKI, SMITH E01 GEYER E11 HENDERSON, PARKER, SCHNEIDER, SCHWARTZ, SETRIGHT, SMITH, SPRINGER E21 ALONZO, GOUNOT, LEE, MEHTA, SPENSER, WONG
APAR PI73929 delivered functional code to support the LISTAGG built-in function.
Function level 501 activation details
|Minimum required catalog level:||V12R1M500|
|Application compatibility control:||Use of the LISTAGG built-in function level requires application compatibility level V12R1M501 or higher.
IBM data server clients and drivers that use the capabilities in function level 501 or are bound with application compatibility level V12R1M501 or higher, require extra program preparation steps. For instructions, see V12R1Mnnn application compatibility levels for data server clients and drivers.
How to activate function level 501
The following steps summarize the process for activating this function level. To learn more about how to activate and control the adoption of new capabilities available for use in your Db2 12 environment and continuous delivery in general, see Adopting new capabilities in Db2 12 continuous delivery.
- For a new Db2 12 installation, skip Step 3. Otherwise, verify that fallback to Db2 11 and coexistence in data sharing are no longer required in your Db2 12 environment.
- If Db2 was migrated from Db2 11, and function level 500 is not yet activated, follow the steps in Activating Db2 12 new function at migration.
- Check that Db2 is at a sufficient code level by issuing a DISPLAY GROUP command, as described in Determining the Db2 code level, catalog level, and function level. The DSN7100I message indicates the Db2 code level for under DB2 LVL in the member details. If DB2 LVL for any member is lower than 121501, apply the PTF for APAR PI70535 and any other required maintenance, so that all members indicate 121501.Tip: You can apply any PTF at any function level. It is best to run Db2 at this code level or higher for some time before you proceed with activating the function level. Db2 cannot run at any lower code level after you activate a function level, so you cannot remove any of the required PTFs after you activate a function level.
- Tailor the CATMAINT and function level activation jobs by running the installation CLIST:
- In panel DSNTIPA1, specify INSTALL TYPE ===> ACTIVATE. Then, specify the name of the output member from the previous function level activation (or migration) in the INPUT MEMBER field, and specify a new member name in the OUTPUT MEMBER field.
- In panel DSNTIP00, specify TARGET FUNCTION LEVEL ===> V12R1M501. The CLIST uses this value when it tailors the ACTIVATE command in the DSNTIJAF job and the CATMAINT utility control statement in the DSNTIJTC job.
- Proceed through the remaining CLIST panels, and wait for the CLIST to tailor the jobs for the activation process. The output data set contains the tailored jobs for the activation process.
- Run the DSNTIJIC job to take an image copy of the Db2 catalog and directory.
- Run the DSNTIJTC job, or run the CATMAINT utility with LEVEL V12R1M501, to update the to the appropriate catalog level. If multiple catalog updates are required, the CATMAINT job processes each update in sequential order. If a later update in the sequence fails, the previous successful updates do not roll back, and the catalog level remains at the highest level reached. If that occurs, you can correct the reason for the failure and resubmit the same CATMAINT job.
- Check that Db2 is ready for function level activation by issuing the following ACTIVATE command with the TEST option:
Db2 issues message DSNU757I to indicate the results. For more information, see Testing Db2 function level activation.
-ACTIVATE FUNCTION LEVEL (V12R1M501) TEST
- Run the tailored DSNTIJAF job, or issue the following ACTIVATE command:
-ACTIVATE FUNCTION LEVEL (V12R1M501)
- If you are ready for applications to use new capabilities in this function level, rebind them at the corresponding application compatibility level. For more information, see Controlling Db2 application compatibility. Optionally, when you are ready for all applications to use the new capabilities of the target function level, you can run the following jobs:
- Run DSNTIJUZ to modify the subsystem parameter module with the APPLCOMPAT value that was specified on panel DSNTIP00.
- Run DSNTIJOZ job to issue SET SYSPARM command to bring the APPLCOMPAT subsystem parameter changes online.
- Run DSNTIJUA job to modify the Db2 data-only application defaults module with the SQLLEVEL value that was specified on panel DSNTIP00.
Function level 501 incompatible changes
Function level 501 introduces no incompatible changes.
For all incompatible changes in Db2 12, see Incompatible changes in Db2 12