db2iseries
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?
There are three (well, actually four...) ways to execute export in DB2.
There is the db2export API (.sh in Unix/Linux, .exe in Windows). This can be executed from anything that understands command shell.
There is the export command to the db2 command. This can be run from a DB2 interactive session, or from the command shell.
There is also the export command through the ADMIN_CMD stored procedure. This allows you to invoke the export from a stored procedure which is accessable through anything that can execute SQL against DB2. This is probably the option you are looking for if you want to do export via SQL through another tool.
There is also the db2move utility which can perform an export, but that is done via command shell as well.
In addition to setting Local DTC Properties, you need to open the firewall on both machines to allow program Distributed Transaction Coordinator to go thru.
When testing T-SQL in Query Editor Window, make sure to turn on XACT_ABORT as suggested by Microsoft.
XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions.
In my case, for the Local DTC Properties, I only need these:
Best Answer
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.