DB2 functions returning error SQL CODE 4743?

Question

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

Disclaimer, I don't use DB2. Just found [this article](https://www.ibm.com/docs/en/db2-for-zos/12?topic=samples-grouping-values-by-using-listagg-function) 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) — SOS, Apr 07 '22 at 22:30
@SOS do i just run -db2a ACTIVATE FUNCTION LEVEL (V12R1M501) — stackerstack, Apr 07 '22 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:...."* https://www.ibm.com/docs/en/db2-for-zos/12?topic=d1fl-function-level-501-activation-enabled-by-apar-pi70535-may-2017 — SOS, Apr 07 '22 at 22:40
@SOS honestly, i read the doc but had no luck as I don't have access to do all that :( — stackerstack, Apr 08 '22 at 12:38
@Charles whatcha mean by platform? So when i ran ```SELECT GETVARIABLE('SYSIBM.VERSION') FROM SYSIBM.SYSDUMMY1```, it returns ```DSN12015```, so I believe it is ibm db2 version 12? — stackerstack, Apr 08 '22 at 12:39

Mark Barinstein · Accepted Answer · 2022-04-08T18:56:22.973

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

edited Apr 08 '22 at 18:56

answered Apr 08 '22 at 12:51

Mark Barinstein

11,456
2
8
16

it's giving me an "ILLEGAL USE OF THE WORD VARCHAR" error – stackerstack Apr 08 '22 at 18:25
Seems, that Db2 for z/OS doesn't support [xml serialization](https://www.ibm.com/docs/en/db2-for-zos/12?topic=functions-xmlserialize) as varchar. I've edited my answer. Try it. – Mark Barinstein Apr 08 '22 at 18:59
Oooo, so my platform is z/OS? And when I ran that I got: ```[Code: -433, SQL State: 22001] VALUE IS TOO LONG. SQLCODE=-433, SQLSTATE=22001, DRIVER=4.26.14``` – stackerstack Apr 08 '22 at 19:28
Can you uncomment the commented out lines and run the statement as is? Do you get the same error? – Mark Barinstein Apr 08 '22 at 19:31
Yeah i get: ```1) [Code: -104, SQL State: 42601] ILLEGAL SYMBOL "". 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``` – stackerstack Apr 08 '22 at 19:40
I changed the CLOB size to 10000 as a random number and it worked, but the output under GROUPED_CLASSES just shows CLOB "X" Bytes – stackerstack Apr 08 '22 at 19:43
This means, that the statement works, but your client doesn't show CLOBs by default. Find the place in the client, where it's possible to change such a behavior. Or try to CAST the whole expression to, say, VARCHAR (10000) explicitly. – Mark Barinstein Apr 08 '22 at 19:54
I'm using db visualizer version 12, not sure if this is what you mean by client – stackerstack Apr 08 '22 at 19:59
[5.6.12 View and edit Binary/BLOB and CLOB Data](https://www.dbvis.com/doc/12.0/12.0.pdf#page97) – Mark Barinstein Apr 08 '22 at 20:15
I cannot find in here where it actually lets you view the output as something viewable instead of clob – stackerstack Apr 11 '22 at 13:59
In `Tools -> Tools Properties -> General -> Data Formats` below the `Presentation of Binary/BLOB and CLOB Data`. Set `Value` in the `CLOB` field. – Mark Barinstein Apr 11 '22 at 15:38
Oh perfect it worked, the text was so small and it was hard for me to read it without zooming in and spending hours to go through it, i really appreciate it. – stackerstack Apr 11 '22 at 16:01
I've never used the XML functions before but are there any caveats to them or are they just regular functions like DATE/DATETIME? – stackerstack Apr 11 '22 at 16:02
All the functions in my example are built-in ("regular" if you like) ones. You may consider such a combination of them in the example as working alternative to `LISTAGG` which doesn't work in your environment because of unknown reason. So, if it resolves your goal, just use it then :) – Mark Barinstein Apr 11 '22 at 16:32
Ahhh I see, and if possible, is there a way you could help me make it so that assuming each ID can take a max of 3 classes at a time, where the first class is the first_priority, and the subsequent are 2nd and 3rd priority, how could i split the string up and make columns for each of them? – stackerstack Apr 11 '22 at 18:18
Please, mark this answer as accepted, if it solves your initial problem. Then open another question with some data example similar to this one and the result desired. This one s to avoid lengthy discussion of a single solution. – Mark Barinstein Apr 11 '22 at 18:30
Yup will do that, do you mind checking it out as well? I'll post a link here after I made it – stackerstack Apr 11 '22 at 18:35
https://stackoverflow.com/questions/71832796/db2-split-string-into-multiple-columns i made it here – stackerstack Apr 11 '22 at 18:44

DB2 functions returning error SQL CODE 4743?

1 Answers1