Problem solve Get help with specific problems with your technologies, process and projects.

Help with the 'in' clause

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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.