3

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)

user3492304
  • 153
  • 2
  • 4
  • 11
  • 1
    AFAIK, an SQL statement in DB2 (LUW) can be up to about 2 MiB, so you could in principle have somewhere in the region of 250k terms in the IN clause, allowing 8 characters per term including the comma. OTOH, you really shouldn't be taking advantage of that. – Jonathan Leffler Sep 01 '14 at 06:32
  • Also, a definition of `upper limit` would be needed if any answer is even possible. If it means "the maximum number of values", the answer might be different from "the maximum length of the predicate". You might have a thousand CHAR(3) values one time and a hundred CHAR(30) values another time, and get different results. – user2338816 Sep 05 '14 at 08:13

4 Answers4

4

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.

nclark
  • 1,022
  • 1
  • 11
  • 16
2

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).

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • Untagged from Oracle group. – user3492304 Sep 01 '14 at 06:30
  • 3
    I just wanted to know the maximum limit for IN predicate in DB2. I'm not interested in possible solutions like temporary table etc. because we have already thought of all these alternatives :) – user3492304 Sep 01 '14 at 06:32
  • @user3492304: then the _first_ thing you need to spec is which platform and which version. Then go onto IBM's doc site and grab the relevant PDF for it. If it's not in there, IBM don't say. As someone who worked in Software Group, I know that IBM don't specify limitations unless absolutely necessary - otherwise it limits their freedom of action. If they _do_ publish it, I'll eat my lunch :-) – paxdiablo Sep 01 '14 at 06:43
0

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

JRA_TLL
  • 1,186
  • 12
  • 23
-1

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.
danny117
  • 5,581
  • 1
  • 26
  • 35
  • This is the best approach for self-built DA layer, but it doesn't apply to systems you're not been granted writing permissions. In my case I cannot create a new table or insert data, I am a read-only user – Phate01 Apr 16 '19 at 15:46