Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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. –  Petter Brodin Feb 10 '13 at 19:00
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
    
So, I guess we are on case 5. 4000 ORs multiplied x 20 (at least) makes that 80k. –  ypercube Feb 11 '13 at 0:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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