Fix SQL with more than 255 UNION ALL statements

Question

Is there any way I could optimize the below SQL in DB2 :

The below SQL is a combination of 257 UNION ALL statements. This query works when I m combining less than 255 UNION ALL statements, but fails with the below error when we are trying to execute more than 255 UNION ALL statements.

Error :

THE STATEMENT IS TOO LONG OR TOO COMPLEX. SQLCODE=-101, SQLSTATE=54001, DRIVER=3.63.75 SQL Code: -101, SQL State: 54001

DB2 SQL query :

SELECT
   '400005' as COLUMN1,
   'DB' as COLUMN2,
   '1' AS COLUMN3
FROM sysibm.sysdummy1

UNION ALL

SELECT
   '400005' as COLUMN1,
   'DB' as COLUMN2,
    '2' AS COLUMN3
FROM sysibm.sysdummy1

UNION ALL

.
.
.
.

UNION ALL
SELECT
   '400005' as COLUMN1,
   'DB' as COLUMN2,
    '257' AS COLUMN3
FROM sysibm.sysdummy1

I came across the few link https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/codes/src/tpc/n129.html , but they arent much useful.

Any inputs would be helpful.

Thanks !

Please run the same in SQL editor with (more than 255 UNION ALL statements) , you should be able to see the error. — user1993412, May 21 '19 at 20:36
find a table with 257 rows select from it getting row_number() from it and limit to 257. cross join to `SELECT '400005' as COLUMN1, 'DB' as COLUMN2 from sysibm.sysdummy1)` — xQbert, May 21 '19 at 21:11
What exactly are you trying to do? You should not need anywhere near 255 Union All statements. Can we get more context with your tables and what the query is supposed to be doing? — Triston Wasik, May 21 '19 at 21:12

score 2 · Answer 1 · answered May 21 '19 at 21:28

2

Consider a recursive CTE:

WITH data (COLUMN1, COLUMN2, COLUMN3) AS    
    (SELECT '400005' as COLUMN1,
            'DB' as COLUMN2,
            1 AS COLUMN3
     FROM sysibm.sysdummy1

     UNION ALL

     SELECT '400005' as COLUMN1,
            'DB' as COLUMN2,
            COLUMN3 + 1 AS COLUMN3
     FROM data
     WHERE data.COLUMN3 < 255)

SELECT COLUMN1, COLUMN2, CAST(COLUMN3 AS VARCHAR(5)) AS COLUMN3 FROM data;

DB2 demo (dbfiddle.uk)

answered May 21 '19 at 21:28

Parfait

104,375
17
94
125

Yep this works too! and thanks for the db2 fiddle link. Also this doesn't suffer from having to have a table with 257 records either. – xQbert May 21 '19 at 21:31
@xQbert a table with more than 257 records isn't a problem...just use syscolumns if nothing else. Although a true `numbers` table is useful for so many things.. – Charles May 21 '19 at 22:26
1

@charles no argument here. The system doesn't have to generate the numbers performance improves and you can do so much with it. My point was simply that one had to have the table so it's dependent on such a table and depending on the number of records needed that could eventually become a problem. Whereas this solution has no such limitation. Though; there may be an upper limit to recursion allowed... – xQbert May 22 '19 at 12:32

xQbert · Answer 2 · 2019-05-21T21:22:54.570

We're dealing with a relational database here. Join (Cross join) to a table having the needed 257 rows and use it's row_number(). 1:M relationship gives us the needed 257 rows and we just limit to only 257 to get the same results your union is attempting to achieve.

SYSIBM.SYSTABLES likely has more than 257 rows... so I'll use it as an example.

Oddly, This might be one of the few times a limit (Fetch) without an order by makes sense.

SELECT '400005' as COLUMN1, 'DB' as COLUMN2,  Z.RN as COLUMN3
FROM sysibm.sysdummy1
CROSS JOIN (SELECT ROW_NUMBER() OVER () as RN 
            FROM SYSIBM.SYSTABLES  --assuming over 257 rows
            FETCH FIRST 257 ROWS ONLY) Z

Not sure DB2 supports the cross join syntax though (Depends on version likely)... so maybe go with older join syntax...

SELECT '400005' as COLUMN1, 'DB' as COLUMN2,  Z.RN as COLUMN3
FROM sysibm.sysdummy1,
     (SELECT ROW_NUMBER() OVER () as RN 
      FROM SYSIBM.SYSTABLES  --assuming over 257 rows
      FETCH FIRST 257 ROWS ONLY) Z

score 1 · Answer 3 · answered May 21 '19 at 21:24

Platform and version of Db2 are important to mention...the below is supported for sure on Db2 for IBM i v7.2 and higher...and probably on DB2 for LUW...not sure about Db2 for Z/OS.

I suspect if XQbert's answer isn't sufficient, a table value constructor may be the answer..

As a Common Table Expression (CTE) using with

with tbl(column1, column2, column3) as (
  values ('400005', 'DB', '1')
        , ('400005', 'DB', '2')  
        ...
        , ('400005', 'DB', '257')
)
select * from tbl;

Or as a Nested Table Expression (NTE)

select * 
from table(('400005', 'DB', '1')
            , ('400005', 'DB', '2')  
            ...
            , ('400005', 'DB', '257')
) tbl(column1, column2, column3);

I like it. Lots of ways to skin this cat. ;P I just don't like to have to type all those numbers :P — xQbert, May 21 '19 at 21:28

Fix SQL with more than 255 UNION ALL statements

3 Answers3