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?

Chris Aldrich
  • 4,916
  • 5
  • 34
  • 55
Daniel Williams
  • 936
  • 4
  • 11
  • 19

2 Answers2

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.

Petter Brodin
  • 897
  • 2
  • 11
  • 18
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.

Chris Aldrich
  • 4,916
  • 5
  • 34
  • 55