1

I've been trying to solve this issue now for a while. I have a table called Students like:

ID     |Classes    |Priority
----------------------------
3       A51         1
3       B51         2
3       K5B         2
3       M5A         2
4       XN5         1
5       XN5         1
5       A51         2
9       BX1         1
9       BX2         2
9       AK3         2

I am using DBVisualizer right now to execute my statements, but I am trying to play around with something called LISTAGG() as a DB2 function:

SELECT
    ID,
    LISTAGG(classes, ',') within GROUP (ORDER BY Priority) AS GROUPED_CLASSES
FROM
    Students
GROUP BY
    ID;

However, every time I try to run this, I get this error:

1) [Code: -4743, SQL State: 56038]  ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL. SQLCODE=-4743, SQLSTATE=56038, DRIVER=4.22.29
2) [Code: -514, SQL State: 26501]  THE CURSOR SQL_CURLH200C1 IS NOT IN A PREPARED STATE. SQLCODE=-514, SQLSTATE=26501, DRIVER=4.22.29

And I have no idea how to fix it or why it is like this. I tried researching into this and someone suggested to do this: SET CURRENT APPLICATION COMPATIBILITY = 'V11R1' but there was no luck with this either, same error even though it ran.

I also tried to look at the version of my DB2 with SELECT GETVARIABLE('SYSIBM.VERSION') FROM SYSIBM.SYSDUMMY1 and this returns DSN12015 (no idea what this means).

I'm desperately looking for an answer or some help, thank you very much. The goal is to have the result look something like:

ID    |Grouped_Classes     |
-----------------------------
3      A51, B51, K5B, M5A
4      XN5
5      XN5, A51
9      BX1, BX2, AK3
10
  • Disclaimer, I don't use DB2. Just found this article googling, but at it does mention some requirements for using LISTAGG(), maybe it's useful? "...1. You must activate Db2 12 for z/OS function level 501 by applying APAR PI70535. Note: In addition to applying the APAR, you must explicitly activate the function level by issuing the ACTIVATE command. For example -db2a ACTIVATE FUNCTION LEVEL (V12R1M501) 2. You might also need to bind or rebind your application with V12R1M501 ...." (cont'd) Commented Apr 7, 2022 at 22:30
  • REBIND PACKAGE(DSNTEP3.DSNTEP3.(*)) APPLCOMPAT(V12R1M501) Commented Apr 7, 2022 at 22:31
  • @SOS do i just run -db2a ACTIVATE FUNCTION LEVEL (V12R1M501) Commented Apr 7, 2022 at 22:32
  • Tbh I don't know. Like I said, I don't even use DB2, just found that information by googling :-) But the article does suggest it might require several steps. I just found this as well, seems to be more detailed steps about applying APAR P170535 at the bottom starting with "To activate Db2 12 function level 501, complete the following steps:...." ibm.com/docs/en/db2-for-zos/… Commented Apr 7, 2022 at 22:40
  • Let us know if you have any luck Commented Apr 8, 2022 at 2:16

1 Answer 1

1

Try this:

/*
WITH STUDENTS (ID, Classes, Priority) AS 
(
          SELECT 3, 'A51', 1 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 3, 'M5A', 2 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 4, 'XN5', 1 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 5, 'XN5', 1 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 3, 'B51', 2 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 3, 'K5B', 2 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 5, 'A51', 2 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 9, 'BX1', 1 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 9, 'BX2', 2 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 9, 'AK3', 2 FROM SYSIBM.SYSDUMMY1
)
*/
SELECT 
  ID
, SUBSTR (XMLSERIALIZE (XMLAGG (XMLTEXT (',' || CLASSES) ORDER BY PRIORITY) AS CLOB (100)), 2)
  AS GROUPED_CLASSES
FROM STUDENTS
GROUP BY ID
ID GROUPED_CLASSES
3 A51,K5B,M5A,B51
4 XN5
5 XN5,A51
9 BX1,BX2,AK3
Sign up to request clarification or add additional context in comments.

18 Comments

it's giving me an "ILLEGAL USE OF THE WORD VARCHAR" error
Seems, that Db2 for z/OS doesn't support xml serialization as varchar. I've edited my answer. Try it.
Oooo, so my platform is z/OS? And when I ran that I got: [Code: -433, SQL State: 22001] VALUE <XML-Value> IS TOO LONG. SQLCODE=-433, SQLSTATE=22001, DRIVER=4.26.14
Can you uncomment the commented out lines and run the statement as is? Do you get the same error?
Yeah i get: 1) [Code: -104, SQL State: 42601] ILLEGAL SYMBOL "<END-OF-STATEMENT>". SOME SYMBOLS THAT MIGHT BE LEGAL ARE:. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.26.14 2) [Code: -514, SQL State: 26501] THE CURSOR SQL_CURLH200C1 IS NOT IN A PREPARED STATE. SQLCODE=-514, SQLSTATE=26501, DRIVER=4.26.14
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.