In DB2 I have a query which goes like this:
Select * from mytable where where mark in ('a','b','c'............);
The 'in' clause is generated dynamically and the size of query size goes to 261 kb. an exception is thrown. It works if I make the size of the 'in' clause lesser.
I have made the Statement heap size to max, still it's not working. Do you have a solution?
Thanks in advance
This appears to be a question about DB2 on Linux, Unix, Windows (not OS/390), but let me attempt to give an answer. Some times SQL statements can become too large or too complex for DB2 to be able to handle them appropriately. This seems to be what is happening with this particularly query of yours.
You state that it "throws an exception" but you do not specify what that exception is. Is it a SQLCODE -101? If so, then your statement is just to long/complex for DB2 and you have to shorten it somehow. Perhaps you can insert the values to be check in the IN clause into another table. For example, insert the appropriate values into a column named vals in a table named marktable. Then you could write the SQL statement as:
select * from mytable where mark in(select vals from marktable);
Of course, this might not be as efficient, but it should avoid the -101 SQLCODE.
Dig Deeper on IBM DB2 management
Related Q&A from Craig S. Mullins
Our expert suggests that the best way to pass the SAP BW exam is to have hands-on job experience. But that's not all he has to say about ... Continue Reading
To export data from a DB2 table to a flat file, you need to run an export specifying the proper file format. The export utility exports data from a ... Continue Reading
Craig Mullins recommends two specific resources for learning how to create and support Binary Large Objects (BLOB) in DB2. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.