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 only takes a minute to sign up.
Sign up to join this community
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?
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.
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.
