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.

Contents

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.

Example

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

Table 1. Activation details for function level 501.
Enabling APAR: PI70535
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.

To activate Db2 12 function level 501, complete the following steps:
  1. 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.
  2. 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.
  3. 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, and it is best to run Db2 at this code level or higher for a period of time before proceeding with activation of 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 activating a function level.
  4. Tailor the CATMAINT and function level activation jobs by running the installation CLIST:
    1. 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.
    2. 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.
    3. 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.
  5. Run the DSNTIJIC job to take an image copy of the Db2 catalog and directory.
  6. 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.
  7. Optionally, check that Db2 is ready for function level activation by issuing the following ACTIVATE command with the TEST option:
    -ACTIVATE FUNCTION LEVEL (V12R1M501) TEST
    Message DSNU757I indicates the results. For more information, see Testing Db2 function level activation.
  8. Run the tailored DSNTIJAF job, or issue the following ACTIVATE command:
    -ACTIVATE FUNCTION LEVEL (V12R1M501)
  9. 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:
    1. Run DSNTIJUZ to modify the subsystem parameter module with the APPLCOMPAT value that was specified on panel DSNTIP00.
    2. Run DSNTIJOZ job to issue SET SYSPARM command to bring the APPLCOMPAT subsystem parameter change online.
    3. Run DSNTIJUA job to modify the Db2 data-only application defaults module with the SQLLEVEL value that was specified on panel DSNITP00.

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