We are getting conflicting reports on the maximum size of JSON blobs that DB2 will allow queries on (e.g. functions like JSON_VAL and JSON_TABLE.)
There is evidence that it is limited to 16M, but I have found nothing conclusive. For example, here is a link to an IBM tech note regarding DB2 11 for z/OS. The Setup & Configuration section shows DB2’s definitions for SYSTOOLS.BSON2JSON, which seems to declare the BSON value as a 16M CLOB.
On the other hand, one source told me that larger sizes ARE allowed but it will not perform well due to DB2's inability to cache a value larger than 16M. If true, this would at least allow us to run BSON queries in our development environment, or for one-time data extraction.
Can anyone point to a more definitive answer?
IBM confirmed in response to a support request that there is a 16 MB limit, imposed by the JSON2BSON and BSON2JSON functions. They note that MongoDB has the same limit.
They implied that if you implemented your own versions of JSON2BSON and BSON2JSON in C or Java, you could get around the limit. But they have no plans to increase the limit themselves, presumably because the values would not be cacheable by DB2.