Db2 for z/OS & Db2ZAI

 View Only
Expand all | Collapse all

LISTAGG not usable though FUNCTION LEVEL V12R1M501

  • 1.  LISTAGG not usable though FUNCTION LEVEL V12R1M501

    Posted Wed September 23, 2020 03:02 PM
    Hello,
    we are working with DB2 for z/OS v12 and have done...
    ACTIVATE FUNCTION LEVEL (V12R1M501)

    ********************************* Top of Data **********************************
    DSNU757I -DBCG DSNUGCCA
    *** BEGIN ACTIVATE FUNCTION LEVEL (V12R1M501)
    FUNCTION LEVEL (V12R1M501) SUCCESSFULLY ACTIVATED

    CATALOG LEVEL(V12R1M500)
    CURRENT FUNCTION LEVEL(V12R1M501)
    HIGHEST ACTIVATED FUNCTION LEVEL(V12R1M501)
    HIGHEST POSSIBLE FUNCTION LEVEL(V12R1M501)

    DSN9022I -DBCG DSNZACMD '-ACTIVATE FUNC' NORMAL COMPLETION
    ******************************** Bottom of Data ********************************

    i.e. activated FUNCTION LEVEL V12R1M501 like above, where you see the success message as well.

    Nevertheless when running the following statement via JDBC:
    CREATE VIEW X_VIEW_PARAMETERS AS SELECT MID, MLVL1, MLVL2, MNAME, SRCTYPE, LISTAGG(CAST(DNAME AS VARCHAR(8000)), ',') WITHIN GROUP (ORDER BY DSEQPOS) AS PARAMNAMES, LISTAGG(CAST(SUBSTR(DSUBTYPE,1,1) || LENGTH AS VARCHAR(8000)), ',') WITHIN GROUP (ORDER BY DSEQPOS) AS PARAMTYPES, SUM(LENGTH) AS PARAMLEN, COUNT(*) PARAMNBR FROM X_VIEW_DATAS WHERE ATTRIBUTES LIKE '%FRMPAR%' GROUP BY MID, MLVL1, MLVL2, MNAME, SRCTYPE;

    it' failing with message:
    ATTEMPT‬‎ ‪TO‬‎ ‪USE‬‎ ‪A‬‎ ‪FUNCTION‬‎ ‪WHEN‬‎ ‪THE‬‎ ‪APPLICATION‬‎ ‪COMPATIBILITY‬‎ ‪SETTING‬‎ ‪IS‬‎ ‪SET‬‎ ‪FOR‬‎ ‪A‬‎ ‪PREVIOUS‬‎ ‪LEVEL‬‎.‪‬‎ ‪SQLCODE‬‎=‪‬‎-‪4743‬‎,‪‬‎ ‪SQLSTATE‬‎=‪56038‬‎,‪‬‎ ‪DRIVER‬‎=‪4‬‎.‪22‬‎.‪29

    We appreciate any hint or help, what to do or where to search. We feel to have searched, read and tried hundreds of hints in multiple Forums but nothing was solving the problem.
    Kind regards
    Andreas Gronert

    ------------------------------
    Andreas Gronert
    ------------------------------

    #Db2forz/OS


  • 2.  RE: LISTAGG not usable though FUNCTION LEVEL V12R1M501

    IBM Champion
    Posted Thu September 24, 2020 12:38 AM

    Hi!

     

    Your JDBC access package must also be rebound with the higher APPLCOMPAT and this is often forgotten...

     

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-


    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email:
    R.Boxwell@seg.de
    Web  http://www.seg.de

    Link zur Datenschutzerklärung


    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich

     






  • 3.  RE: LISTAGG not usable though FUNCTION LEVEL V12R1M501

    Posted Thu September 24, 2020 04:42 AM
    ‪Thanks Roy,
    Thanks James,
    SELECT CURRENT APPLICATION COMPATIBILITY FROM SYSIBM.SYSDUMMY1;
    via JDBC delivers
    V12R1M500

    SET‬‎ ‪CURRENT‬‎ ‪APPLICATION‬‎ ‪COMPATIBILITY‬‎ ‪‬‎=‪‬‎ ‪‬‎'‪V12R1M501'
    via JDBC delivers:
    THE‬‎ ‪REPLACEMENT‬‎ ‪VALUE‬‎ ‪FOR‬‎ ‪CURRENT‬‎ ‪APPLICATION‬‎ ‪COMPATIBILITY‬‎ ‪IS‬‎ ‪INVALID‬‎.‪‬‎ ‪SQLCODE‬‎=‪‬‎-‪713‬‎,‪‬‎ ‪SQLSTATE‬‎=‪42815‬‎,‪‬‎ ‪DRIVER‬‎=‪4‬‎.‪22‬‎.‪29

    whereas under green:
    ispf > M > 16 > 7 > (DB2 COMMANDS) >
    ACTIVATE FUNCTION LEVEL (V12R1M501)
    delivers:
    ********************************* Top of Data **********************************
    DSNU757I -DBCG DSNUGCCA
    *** BEGIN ACTIVATE FUNCTION LEVEL (V12R1M501)
    FUNCTION LEVEL (V12R1M501) SUCCESSFULLY ACTIVATED

    CATALOG LEVEL(V12R1M500)
    CURRENT FUNCTION LEVEL(V12R1M501)
    HIGHEST ACTIVATED FUNCTION LEVEL(V12R1M501)
    HIGHEST POSSIBLE FUNCTION LEVEL(V12R1M501)

    DSN9022I -DBCG DSNZACMD '-ACTIVATE FUNC' NORMAL COMPLETION
    ******************************** Bottom of Data ********************************

    What must I do for acitvating LISTAGG? Do you have a short description or a link to any good to how to rebound my JDBC access package with the higher APPLCOMPAT.
    Kind regards
    Andreas Gronert

    ------------------------------
    Andreas Gronert
    ------------------------------



  • 4.  RE: LISTAGG not usable though FUNCTION LEVEL V12R1M501

    IBM Champion
    Posted Thu September 24, 2020 05:03 AM

    You must find out which of the, probably NULLID, packages your JDBC is using and do a REBIND with the higher level APPLCOMPAT

     

    It is probably one of the SYS%%%%% style packages – Naturally caution must be used as then *everyone* who uses these packages gets FL501 which might not be desired. IBM recommend having new Collections where the higher FL levels have been "allowed" and the steering the JDBC through either the connection config variable and/or through the gateway settings.

     

    The packages are SYSSHxyy SYSSNxyy SYSLHxyy SYSLNxyy

    x is isolation level: 0 (NC), 1 (UR), 2 (CS), 3 (RS) or 4 (RR)

    yy is the package iteration from 00 to 99

     

    Here's a link all about the APPLCOMPAT and JDBC parms:

     

    https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/apsg/src/tpc/db2z_applcompatclients.html

     

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-


    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email:
    R.Boxwell@seg.de
    Web  http://www.seg.de

    Link zur Datenschutzerklärung


    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich

     






  • 5.  RE: LISTAGG not usable though FUNCTION LEVEL V12R1M501

    Posted Thu September 24, 2020 08:25 PM
    I just want to add if those NULLID.SYS%%%%% style packages are bound to APPLCOMPAT(V12R1M501), the JDBC driver's version has to be at least 3.72 or 4.22(DB2JCC.jar / DB2JCC4.jar), otherwise you will get connection issue.

    Jack


    ------------------------------
    Jack Yan
    ------------------------------



  • 6.  RE: LISTAGG not usable though FUNCTION LEVEL V12R1M501

    Posted Thu September 24, 2020 01:25 AM
    Db2 might be at FL501, but are you?  What does
      SELECT CURRENT APPLICATION COMPATIBILITY
      FROM SYSIBM.SYSDUMMY1 ;
    show? (Issued from your JDBC program, of course.) 

    Possible solution:
       SET CURRENT APPLICATION COMPATIBILITY = 'V12R1M501';  
    before your SELECT.

    ------------------------------
    JAMES CAMPBELL
    ------------------------------



  • 7.  RE: LISTAGG not usable though FUNCTION LEVEL V12R1M501

    Posted Thu September 24, 2020 09:33 AM
    ​You need to either change you applcompat zparm to be V12R1M501 or bind the application using applcompat V12R1M501. You can not simply activate a function to use it, if your applcompat is set differently.

    ------------------------------
    Will Smith
    ------------------------------



  • 8.  RE: LISTAGG not usable though FUNCTION LEVEL V12R1M501

    Posted Thu September 24, 2020 08:25 PM
    Hello Andreas
    As Roy mentioned, you have to bind the jdbc access package (eg, all db2 client packages). See​ https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/apsg/src/tpc/db2z_applcompatclients.html
    for details. When you create new packages (instead of rebind them), then you have to set the new package-name in the jdbc-connection (property currentPackageS)

    Regards
    Barbara von Euw

    ------------------------------
    Barbara von Euw
    ------------------------------



  • 9.  RE: LISTAGG not usable though FUNCTION LEVEL V12R1M501

    Posted Thu September 24, 2020 08:25 PM
    Hello Andreas, did you make sure that the Db2 packages for your JDBC driver are also bound with APPLCOMPAT(V12R1M501) ? Typically these packages named SYSxxxxx (e.g. SYSLH200) are bound in the NULLID collection. Your installation might have chosen a different collection name - in this case your application needs to set the CurrentPackageSet property to the name of this collection.
    It is not sufficient to activate FL501 on the Db2 side - your packages need to be bound with the higher APPLCOMPAT as well.
    Kind regards
    Christoph Theisen

    ------------------------------
    Christoph Theisen
    ------------------------------



  • 10.  RE: LISTAGG not usable though FUNCTION LEVEL V12R1M501

    IBM Champion
    Posted Thu September 24, 2020 08:25 PM
    Andreas,

    LISTAGG is SQL and SQL is controlled by APPLCOMPAT.

    So you have to activated FL 501 and (RE)BIND the package with APPLCOMPAT(V12R1M501).

    Best Regards,

    Chris

    Chris Crone
    Broadcom Distinguished Engineer - Database Technology, Mainframe Software Division





  • 11.  RE: LISTAGG not usable though FUNCTION LEVEL V12R1M501

    Posted Fri October 02, 2020 08:19 AM
    Hello,
    thanks for all your help.

    Now I've rebound all packages with COLLID NULLID with APPLCOMPAT V12R1M501.

    We are working with RDz
    IBM Software Delivery Platform_8 2018.8.6.140716 IBM Software Delivery Platform_8 com.ibm.sdp.eclipse.ide IBM

    and it's driver
    IBM Datenservertreiber für JDBC und SQLJ (JDBC 4.0)

    unfortunately now we can't connect at all. When following Roys link
    https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/apsg/src/tpc/db2z_applcompatclients.html

    there is written:
    For the IBM Data Server Driver for JDBC and SQLJ, change the DB2BaseDataSource.clientApplcompat Connection or DataSource property value.

    But I don't know how to do that.

    Any hint wellcome.
    Kind regards
    Andreas Gronert

    ------------------------------
    Andreas Gronert
    ------------------------------



  • 12.  RE: LISTAGG not usable though FUNCTION LEVEL V12R1M501

    Posted Fri October 02, 2020 08:45 AM
    Hello Andreas, 
    there are two things to consider:
    First: make sure that Db2 APAR PH08482 is installed. That makes clientApplCompat property on the JDBC side optional.
    Second: what service level does your JDBC driver have? Db2 Clients and Data Server Drivers should be on V11.1 FP1 or higher when they connect to Db2 12 - especially when FL V12R1M501 or higher is activated. V11.1 FP1 corresponds to JDBC architecture level 4.22.29. So if your db2jcc4.jar is older you should update it to a more recent level.
    Kind regards
    Christoph Theisen

    ------------------------------
    Christoph Theisen
    ------------------------------



  • 13.  RE: LISTAGG not usable though FUNCTION LEVEL V12R1M501

    Posted Fri October 02, 2020 10:24 AM
    Thanks Christoph,
    First:
    Currently our server administrator can't apply Db2 APAR PH08482

    Second:
    from following page
    https://www.ibm.com/support/pages/node/6241980

    I've downloaded
    jt400.jar
    Version: 4.27.25

    But still no connection.
    Kind regards
    Andreas Gronert

    ------------------------------
    Andreas Gronert
    ------------------------------



  • 14.  RE: LISTAGG not usable though FUNCTION LEVEL V12R1M501

    Posted Wed December 30, 2020 07:59 AM

    Hello,
    the complete solution was as follows:
    (1)
    rebind JDBC-Packages, e.g. by
    m.17 > 1 > k >
    filter Collection by NULLID
    selection rb for each package of collection NULLID
    APPLCOMPAT . . . . . V12R1M501
    confirm with JDBC-SQL (must be empty as all must be V12R1M501
    select * from sysibm.syspackage where colLid like 'NULL%' and applcompat like '%500';
    (2)
    Download from following page
    https://www.ibm.com/support/pages/node/6241980
    and replace
    C:\IBM\SQLLIB\java\sqlj4.zip
    C:\IBM\SDPShared\plugins\com.ibm.datatools.db2_2.2.200.v20150728_2354\driver\db2jcc.jar
    (db2jcc.jar may be ad different place as seen in driver properties)
    (3)
    run the following SQL before any SQL that needs this function level
    SET CURRENT APPLICATION COMPATIBILITY = 'V12R1M501';
    Kind regards
    Andreas Groner



    ------------------------------
    Andreas Gronert
    ------------------------------