debugMinor
DB2 error - SQL Statement too long or complex
Viewed 0 times
errorstatementsqllongtoodb2complex
Problem
Getting this error in a DB2 production system:
The statement has a large number of OR statements in the WHERE clause, as in
I am thinking that I need to scale back the batch size to something less that 4000 OR statements. Any suggestions?
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
...
WHERE (a=x1 and b=y1)
OR (a=x2 and b=y2)
OR (a=x3 and b=y3)
... I am thinking that I need to scale back the batch size to something less that 4000 OR statements. Any suggestions?
Solution
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?
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.
Could you create a temporary table and JOIN instead?
...
JOIN tmp_table ON my_table.a = tmp_table AND my_table.b = tmp_table.bEdit: 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.
Code Snippets
...<sql statements and joins>
JOIN tmp_table ON my_table.a = tmp_table AND my_table.b = tmp_table.bContext
StackExchange Database Administrators Q#34491, answer score: 3
Revisions (0)
No revisions yet.