0

We have an SQL SELECT statement that retrieves over 400 fields from more than 120 joined tables. The query executes successfully on other database systems (Oracle 19c and Microsoft SQL Server 2019). However, when running the same query on Db2 for i, it fails with error SQL0904, which references a job log entry containing message ID CPD4365.

The job log indicates reason code 3: “More than 65535 bytes in an internal buffer.”

If we remove just a few columns from the SELECT clause—regardless of which ones—the query executes successfully again.

What causes this internal buffer limit in Db2 for i, and are there options to avoid the SQL0904 error and support the required number of columns?

Improve this question
3
  • Without seeing the code it is difficult to guess if could be adjusted - 120 joins sounds excessive. However you can change the size of the buffer(s) - ibm.com/docs/en/db2-for-zos/… Commented Oct 7 at 10:35
  • @CHill60 thank you for the link, however the system is an IBM i and I could not find a similar section in the IBM i docs at ibm.com/docs/i/7.6.0. Commented Oct 7 at 12:09
  • 3
    Contact IBM support. The i docs do specifically state that some limitations exist that are based on the size of internal structures (without at the same time indicating whether external configuration of such sizes is possible). But sometimes things are omitted from the docs. So contact IBM. This is not a programming question per se. Commented Oct 7 at 14:05

1 Answer 1

Reset to default
1

The documentation for DB2 for i includes an SQL Limits page where I find this:

enter image description here

So the max row length in a query result set on DB2 for i is around 32K if there are no large objects, or 3G if there are large objects. I believe that the storage length beyond 32K is only for large object data. You can't just get a bigger buffer by adding a small large object. So you will need to split up your query if you really need all 400 columns.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.