DB2 getting QDT Array List maximum exceeded using CTE and sql recursion

Question

I am using CTE to create a recursive query to merge multiple column data into one.

I have about 9 working CTE's (I need to merge columns a few times in one row per request, so I have the CTE helpers). When I add the 10th, I get an error. I am running the query on Visual Studio 2010 and here is the error:

And on the As400 system using the, WRKOBJLCK MyUserProfile *USRPRF command, I see:

I can't find any information on this.

I am using DB2 running on an AS400 system, and using: Operating system: i5/OS Version: V5R4M0

I repeat these same 3 CTE's but with different conditions to compare against:

  t1A (ROWNUM, PARTNO, LOCNAM, LOCCODE, QTY) AS
  ( 
    SELECT rownumber() over(partition by s2.LOCPART),  s2.LOCPART, s2.LOCNAM, s2.LOCCODE, s2.LOCQTY
    FROM (
             SELECT distinct s1.LOCPART, L.LOCNAM, L.LOCCODE, L.LOCQTY
             FROM(
                     SELECT COUNT(LOCPART) AS counts, LOCPART
                     FROM LOCATIONS
                 WHERE LOCCODE = 'A'
                 GROUP BY LOCPART) S1, LOCATIONS L
                     WHERE S1.COUNTS > 1 AND S1.LOCPART = L.LOCPART AND L.LOCCODE = 'A'
                  )s2
  ),
  t2A(PARTNO, LIST, QTY, CODE, CNT) AS
  (
       select PARTNO, LOCNAM, QTY, LOCCODE, 1
       from t1A
       where ROWNUM = 1
       UNION ALL
       select t2A.PARTNO, t2A.LIST || ', ' || t1A.LOCNAM, t1A.QTY, t1A.LOCCODE,  t2A.CNT + 1
       FROM t2A, t1A
       where t2A.PARTNO = t1A.PARTNO
       AND  t2A.CNT + 1 = t1A.ROWNUM
  ),
  t3A(PARTNO, LIST, QTY, CODE, CNT) AS
  (
         select  t2.PARTNO, t2.LIST, q.SQTY, t2.CODE, t2.CNT
         from(
                 select  SUM(QTY) as SQTY, PARTNO
                 FROM t1A
                 GROUP BY PARTNO
             ) q, t2A t2
         where t2.PARTNO = q.PARTNO
  )

Using these, I just call a simple select on one of the CTE's just for testing, and I get the error each time when I have more than 9 CTE's (even if only one is being called).

In the AS400 error (green screen snapshot) what does QDT stand for, and when am I using an Array here?

QDT = Query Definition Template. I believe it's similar to a query plan. — Bacon Bits
– Bacon Bits, Commented Aug 15, 2017 at 18:25
Ah okay thanks. To me this just sounds like a complicated way of saying the sql line is too long, but I would think it would be more specific. Maybe the CTE's are being stored in an Array? — eaglei22
– eaglei22, Commented Aug 15, 2017 at 18:29
I know I have hit nested table limits before, maybe that is what is happening. I don't think the SQL statement is too long, that can be 2 Mb. There is a limit of 256 tables and views nested inside a single view. It could be that an individual table is counted multiple times. It might also include internally generated tables. I can recall not being able to count anywhere near 256 tables when I hit that limit. CTE's may be similar, though I can't find any specific documentation to verify that. — jmarkmurphy
– jmarkmurphy, Commented Aug 15, 2017 at 18:51
@jmarkmurphy any recommendations of what I can try bumping up (if even an option) for max storage to try and resolve this. — eaglei22
– eaglei22, Commented Aug 15, 2017 at 18:55
unlikely that there's anything you can adjust...other than your query. SQL0901 and and CPF4204 are indicative of an OS issue. If you were on a supported release, you could call IBM. — Charles
– Charles, Commented Aug 15, 2017 at 19:08

eaglei22 · Accepted Answer · 2017-08-23 17:34:36Z

This was a mess. Error after error. The only way I could get around this was to create views and piece them together.

When creating the view I was only able to get it to work with one CTE not multiple, then what worked fine as one recursive CTE, wouldn't work when trying to define as a view. I had to break apart the sub query into views, and I couldn't create a view out of SELECT rownumber() over(partition by COL1, Col2) that contained a sub query, I had to break it down into two views. If I called SELECT rownumber() over(partition by COL1, Col2) using a view as its subquery and threw that into the CTE it wouldn't work. I had to put the SELECT rownumber() over(partition by COL1, Col2) with its inner view into another view, and then I was able to use it in the CTE, and then create a main view out of all of that.

Also, Each error I got was a system error not SQL.

So in conclusion, I relied heavily on views to fix my issue if anyone ever runs across this same problem.

Collectives™ on Stack Overflow

DB2 getting QDT Array List maximum exceeded using CTE and sql recursion

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related