Categories
LISTAGG function usage in BI Report causing 'IO Connection Closed' error
Hi Experts,
We recently had an issue in our PROD instance that one of the report (which was working earlier) is causing issue saying 'IO Connection Closed'. We figured that LISTAGG function is causing the issue and the same report is working in other instances.
Since we don't want to change the logic, we need to fix the problem in PROD without touching the code.
So need suggestions/options/solutions on this issue. Kindly comment if anybody awares about it.
Thanks,
Keerthana.
Best Answer
-
Hi All,
We have identified that this is due to character limitation issue in the Listagg. Also we observed the same report is working for sysdate and it is not working for specific date ranges.
We have handled Overflow truncate in LISTAGG to fix our issue. As expected, the report is working.
3
Answers
-
@KeerthanaBaskaran - Please confirm that the PROD instance matches the configuration and patch level of other environments. Also, try comparing the physical SQL generated in working and non working environment and see if there are any differences. Execute them directly on corresponding DB's and validate the performance. These steps should help us identify the bottleneck first.
0 -
Is the production instance newer/have data that is not in lower PODs? What is length of all data your are trying listagg together? Just wondering if data in prod exists that is not in lower pod.
In one case we used listagg to compile invoices related to a payment. When using listagg, the number of characters exceeded 1000 (I believe that is listagg limit).
What we had to do was use xml aggregate instead of listagg. I believe we also faced I/O error.
2 -
Very helpful information.
Thanks for sharing @KeerthanaBaskaran!
0
