Start of change

Function level 501 (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.

Examples for LISTAGG

  • The following example produces 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                             
    

Start of changeAPAR PI73929 delivered functional code to support the LISTAGG built-in function.End of change

Function level 501 activation details

Function level 501 has the following activation details:

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 Setting 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. 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.
  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 Db2 installation 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 Db2 installation CLIST panels, and wait for the Db2 installation CLIST to tailor the jobs for the activation process. The output data set contains the tailored jobs for the activation process. For more information, see The Db2 installation CLIST panel session.
  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. If the CATMAINT utility jobs from the previous step placed any altered Db2 catalog objects in REORG-pending (AREO*) advisory status, run the REORG utility for those objects.
  8. Check that Db2 is ready for function level activation by issuing the following ACTIVATE command with the TEST option:
    -ACTIVATE FUNCTION LEVEL (V12R1M501) TEST
    Db2 issues message DSNU757I to indicate the results. For more information, see Testing Db2 function level activation.
  9. Run the tailored DSNTIJAF job, or issue the following ACTIVATE command:
    -ACTIVATE FUNCTION LEVEL (V12R1M501)
  10. 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 the Db2 application compatibility level.
    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 changes online.
    3. 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

End of change