what is the upper limit for the predicate WHERE IN () db2?

Question

what is the upper limit for predicate WHERE IN in DB2.

For example:

select salary from employee where empid in (1,2,3,4...N)

Answer 1

Sometimes, if you have to ask the question, you're probably going about it the wrong way :-)

If you're worried about the limit, consider placing the values into a temporary table and then just joining the tables to get your result.

As to the specific limits, this will almost certainly vary based on which DB2 you're using: LUW, z/OS, iSeries and so on. It may also vary depending on the version. I've seen questions raised on the net about DB2 refusing more than a thousand entries but, as to which platform/version, I couldn't say.

Your best bet may be to simply test whatever version you're using to see when you hit the limit. IBM as a rule doesn't tend to publish limits like that, assuming instead that you'll do the sensible thing and avoid too many (not least because it's nearly impossible for the optimisation engine to choose a decent execution path for arbitrary values).

Answer 2

For z/OS, following limits are given by IBM, but there is no specification as to the IN clause:

https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_limits.dita

Answer 3

The AS/400 limit is around 1000, based on errors we get when we exceed that. We're splitting up into multiple smaller queries to patch our legacy JEE app.

Answer 4

Use a join statement when reach the limits of an in clause. This has no limit for all intents and purposes.

select salary from employee join mytable on empid = id.