In mathematics, a perfect number is defined as a positive integer which is the sum of its proper positive divisors, that is, the sum of the positive divisors excluding the number itself. Equivalently, a perfect number is a number that is half the sum of all of its positive divisors (including itself), or σ(n) = 2n.

The first perfect number is 6, because 1, 2, and 3 are its proper positive divisors, and 1 + 2 + 3 = 6. Equivalently, the number 6 is equal to half the sum of all its positive divisors: ( 1 + 2 + 3 + 6 ) / 2 = 6.

The next perfect number is 28 = 1 + 2 + 4 + 7 + 14. This is followed by the perfect numbers 496 and 8128.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For those who are interested a perfect number solution in DB2:

with prfct_cand(k, prfct_cand, d1, d2) as ( select 1, int(2 * 3), int(2), int(3) from sysibm.sysdummy1 union all select k + 1, int (power(2., k + 1) * (power(2., k + 2) - 1.)),
int(power(2., k + 1)), int(power(2., k + 2) - 1.) from prfct_cand where k + 1 <= 15
)
,
numbers (number) as
( select 3
from sysibm.sysdummy1
union all
select number + 2 from numbers
where number < 65535
)
,
dividers (divider) as
( select 2
from sysibm.sysdummy1
union all
select divider + 1 from dividers
where divider <= int(sqrt(65535))
)
,
prime(prime) as
(
select 2
from sysibm.sysdummy1
union all
select number
from
numbers left join dividers
on number = divider * int(number / divider)
and divider <= int(sqrt(number))
where divider is null
)
select k, prfct_cand "perfect number"
from
prfct_cand join prime
on d2 = prime
order by k

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

or

with perfect (number, divider, sum_divider, show_d, limit) as ( select 2, 1, 1, varchar('1 ', 10000), 500
from sysibm.sysdummy1
union all
select number,
divider + 1,
case
when number = (divider + 1) * int(number / (divider + 1))
then sum_divider + (divider + 1)
else sum_divider
end,
case
when number = (divider + 1) * int(number / (divider + 1))
then show_d || ' + ' || varchar(divider + 1)
else show_d
end
,
limit
from perfect
where divider + 1 <= number / 2
and number <= limit
and divider + 1 <= limit / 2
and sum_divider <= number

union all
select number + 2, 1, 1, '1 ', limit
from perfect
where
( divider + 1 > number / 2
and number + 2 <= limit )
or
( divider + 1 <= number / 2
and number + 2 <= limit
and divider <= limit / 2
and sum_divider > number )
)
select number "perfect number", show_d
from perfect p1
where
p1.number =
(select max(p2.sum_divider) from perfect p2
where p2.number = p1.number)
and
p1.divider =
(select max(p2.divider) from perfect p2
where p2.number = p1.number)
;

Ok, I am tried to get the perfect numbers using DB2 and triangle number concepts.
And I got 2 extra number which are not a perfect:

130816 and 2096128.

How I can remove these numbers from the result set, do not using existing methods ?


with prfct_cand(k, prfct_cand) as
(
select 1, int(2 * 3)
from sysibm.sysdummy1
union all
select k + 1, int (power(2., k + 1) * (power(2., k + 2) - 1.))
from prfct_cand
where k + 1 <= 15
)
,
Triangle_nbr(n, Triangle_num, tri_show) as
(
select 1, int(1), varchar('1^3 ', 10000)
from sysibm.sysdummy1
union all
select n + 1, Triangle_num + power((2 * (n + 1) - 1), 3),
tri_show || ' + ' || strip(digits(2 * (n + 1) - 1), l, '0') || '^3'
from Triangle_nbr
where n + 1 < 150
)
select 6 " Perfect number" , '1 + 2 + 3' " Triangle representation", 1 " Number of cubes"
from sysibm.sysdummy1
Union All
select prfct_cand " Perfect number", tri_show " Triangle representation", 2 * n - 1 " Number of cubes"
from prfct_cand, Triangle_nbr
where prfct_cand = Triangle_num
and prfct_cand = 9 * int(prfct_cand / 9 ) + 1

Or slightly shorter:

Thank you, Peter for your response. You remind me about BIGINT. That's great !

But I preffer more common solution. Let see following, which is resolve the problem with the extra triangle numbers:

with
numbers (num) as
(select 2 from sysibm.sysdummy1
union all
select num + 1 from numbers
where num + 1 <= int(sqrt(2147483647)) + 1
)
,
prfct_cand(k, prfct_cand, oddiv) as
(
select 1, int(2 * 3), int(3)
from sysibm.sysdummy1
union all
select k + 1, int (power(2., k + 1) * ( power(2., k + 2) - 1.) ), int(power(2., k + 2) - 1.)
from prfct_cand
where k + 1 <= 15
)
,
Triangle_nbr(n, Triangle_num, tri_show) as
(
select 1, int(1), varchar('1^3 ', 10000)
from sysibm.sysdummy1
union all
select n + 1, Triangle_num + power((2 * (n + 1) - 1), 3),
tri_show || ' + ' || strip(digits(2 * (n + 1) - 1), l, '0') || '^3'
from Triangle_nbr
where n + 1 < 150
)
,
perfect_triangle(perfect_nbr, perf_show) as
(
select int(6), varchar('1 + 2 + 3', 10000) from sysibm.sysdummy1
union all
select Triangle_num, tri_show
from Triangle_nbr, prfct_cand
where prfct_cand = Triangle_num
and not exists
(select 1 from numbers
where oddiv > num and
oddiv = int(oddiv / num) * num )

)
select * from perfect_triangle

*** Lenny ***

07/07/09 NY

How I can get any numbers of the prime:

with
limit (lim) as
(select int(10000) from sysibm.sysdummy1
)
,
numbers (num) as
(select 2 from sysibm.sysdummy1
union all
select num + 1 from numbers, limit
where num + 1 <= int(sqrt(lim)) + 1
)
,
prime_nbr(prime_num, prime_ind) as
(select int(2), int(1) from sysibm.sysdummy1
union all
select 3, 1 from sysibm.sysdummy1
union all
select prime_num + 2, 1
from prime_nbr, limit
where
prime_num + 2 <= lim
and
not exists
(select 1 from numbers
where num <= int(sqrt(prime_num )) + 1
and
prime_num + 2 = int((prime_num + 2) / num) * num )
union all
select prime_num + 2, 0
from prime_nbr, limit
where
prime_num + 2 <= lim
and
exists
(select 1 from numbers
where num <= int(sqrt(prime_num )) + 1
and
prime_num + 2 = int((prime_num + 2) / num) * num )

)
select prime_num "prime number" from prime_nbr
where prime_ind = 1

*** Thanks, Lenny ***

May I ask you ?

Why your statement doesn't work ?

I have the message:

SQL0104N An unexpected token "N" was found following "". Expected tokens may include: "DSN_INLINE_OPT_HINT". SQLSTATE=42601

State:42601,Native:-104,Origin:[IBM][CLI Driver][DB2]

I am working with DB2 v8. Maybe you are using v9 ?

Thanks, Lenny

This works for me in DB2 v8 (8.1.0) on MS-Windows, using the command line processor. Strange that you receive SQLCODE -104. I don't see what could be wrong.

I did not do nothing else. Just copy-paste and run.

Lenny

Could you maybe also try it in the CLP, to see if it works there?
Then try changing table and/or column names (viz. "n") to see which "n" is the offender.

I am using QTODBC....

Is your version 8 DB2 server on LUW or on z/OS?

Not sure.... Maybe z/os...

DB2 v8 for z/OS does not support the "VALUES" statement, and has no BIGINT; try the following equivalent SQL instead:

This one will work. That's for sure. You can try any of my queries which I posted here. You will see how we can generate Prime and Perfect number without any constants.

Thanks, Lenny

Using the recursive in DB2 could be a very tricky and useful !

Thanks