Here is a basic implementation to calculate prime numbers, similar to
the Sieve of Eratosthenes. It is not a correct implementation
(crossing out all multiples of all numbers instead of only multiples
of primes) and there are probably ways to make it much faster, but
that's what I came up with.

Here it is:


with

maxnum (maxnum) as (
values 1000
)
,
sqrtnum (sqrtnum) as (
select int(sqrt(maxnum)+1) from maxnum
)
,
nums (c1) as (
values 2
union all
select c1+1 from nums where c1 < (select maxnum from maxnum)
)
,
t (c1, c2, c3) as (
select 2, c1, 2*c1 from nums
union all
select c1+1, c2, (c1+1)*c2 from t where c1 < (select sqrtnum from
sqrtnum) and (c1+1)*c2 < (select maxnum from maxnum)
)
,
primes (c1) as (
select c1 from nums
except
select c3 from t
)

select c1 from primes;
;

For the Microsoft product there are a lot of ideas on calculating
primes over here

http://www.sqlservercentral.com/articles/T-SQL/67666/

I guess most will run on DB2 as well or port without too much effort.

As an exercise this is great but for real work: download a list of
known primes, upload it to your database and use it.

brgds

Philipp Post

Look at my puzzle column:

http://www.sqlservercentral.com/articles/T-SQL/67666/

Take a look in the discussions. There are faster Sieves, but the best
way is to look up a table of Primes on the Internet and just load it
into a table with "cut & paste" to get the answer.