Re: SQL Statement size limit?

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Well as I said, I believe that this is coming through JDBC (but I'm
checking on that :)

It also appears that a SQL0101 is being thrown...

SQL0101  Message Text: SQL statement too long or complex. Cause Text: The
SQL statement is longer than the limit allowed for length or complexity.
The reason code is &1. One of the following reason codes indicates the
error:

   - 1 - The total number of subselects combined using UNION, EXCEPT, and
   INTERSECT is greater than 256.
   - 2 - The total number of columns, constants, and operators is greater
   than the SQL limits.
   - 3 - The sum of the lengths of the non-LOB columns in a select list,
   table, view definition, or user defined table function is greater than
   32766 or the definition contains a LOB and the sum of the lengths specified
   on the ALLOCATE clause for varying-length fields and the non-varying field
   lengths is greater than 32740. The maximum length is reduced if any of the
   columns are varying-length or allow null values.
   - 4 - The total number of subselects referenced in an outer subselect is
   greater than 256.
   - 5 - The total length of the statement text is greater than 2097152, or
   the total length of the prepare attribute string text is greater than 65535.
   - 6 - The relative position value specified on the FETCH statement is
   outside the range of valid values.
   - 7 - A system name could not be generated.

 Recovery Text: Simplify the statement or divide the statement up into more
than one statement and try the request again. For reason code 7, specify a
different name for the table, view, index, or alias. SQLCODE or SQLCODEs: -101
 SQLSTATE or SQLSTATEs: 54001, 54010, 54011


But I'm basing that on the message text, "SQL statement too long or complex."
I was shown from the client side log.  I haven't seen the actual joblog
message with reason code.

I'd assume it would be reason 7.

But now I've got a third limit... 64KB.

The client side log showed the statement being cut off at 32K...but that
could have just been a limitation of the logging.

Charles



On Thu, Apr 18, 2013 at 10:51 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>wrote:

> AFAIK - 2MB in SQL Scripts / in embedded SQL - 32 K?
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
>
> -----Ursprüngliche Nachricht-----
> Von: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Charles Wilt
> Gesendet: Thursday, 18.4 2013 16:41
> An: Midrange Systems Technical Discussion
> Betreff: SQL Statement size limit?
>
> All,
>
> Does anybody know what the SQL statement size limit for the iSeries is at
> v5r4?
>
> I found this:
>
> http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzaik/exitpointt
> able1.htm
>
> which shows a max statement size of 32K
>
> But this
>
> http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstlimt
> abs.htm
>
> says
> Maximum length of an SQL statement (in bytes) 2 097 152
>
> Bit of a difference between 32KB and 2MB :)
>
> Lastly, does the JDBC have some other limit?
>
> Thanks!
> Charles
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> unsubscribe,
> or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
> moment to review the archives at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.