4

Getting this error in a DB2 production system:

ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0101 - SQL statement too long or complex.

The statement has a large number of OR statements in the WHERE clause, as in

...<sql statements and joins>
WHERE (a=x1 and b=y1)
  OR (a=x2 and b=y2)
  OR (a=x3 and b=y3)
... <4000 times>

I am thinking that I need to scale back the batch size to something less that 4000 OR statements. Any suggestions?

1
  • 1
    Could you specify something more about where these values are coming from, how the query is built, used, etc.? It might be useful to know more about the problem domain. Feb 10, 2013 at 19:00

2 Answers 2

3

This should probably be a comment rather than an answer since I'm not sure if my reasoning is correct, but then I wouldn't be able to have any formatting.

Could you create a temporary table and JOIN instead?

...<sql statements and joins>
JOIN tmp_table ON my_table.a = tmp_table AND my_table.b = tmp_table.b

Edit: if the values you're checking against don't change much, it might even make sense to persist them in a table to increase performance and facilitate querying.

1

Don't know which version of i5/OS you are on. This is from V5R4 documentation. But things you may be coming across:

1 - The total number of subselects in a fullselect (UNION or UNION ALL clause) is greater than 32.
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 nested subselects is greater than 31.
5 - The total length of the statement 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.

IBM's recommendation is to break up the SQL, on which case Petter as a pretty good idea. Otherwise you may have to query more than once and marry the results later.

1
  • So, I guess we are on case 5. 4000 ORs multiplied x 20 (at least) makes that 80k. Feb 11, 2013 at 0:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.