1. Registered User
Join Date
Jun 2009
Posts
1

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

2. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
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

3. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Or slightly shorter:
Code:
```WITH
n(n) AS (VALUES 2,3,5,7,13,17,19,31,61,89,107,127,521,607,1279,2203,2281),
m(m) AS (SELECT POWER(CAST(2 AS BIGINT),n-1) FROM n)
SELECT m*(2*m-1) FROM m```

4. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
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

5. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Originally Posted by Peter.Vanroose
Or slightly shorter:
Code:
```WITH
n(n) AS (VALUES 2,3,5,7,13,17,19,31,61,89,107,127,521,607,1279,2203,2281),
m(m) AS (SELECT POWER(CAST(2 AS BIGINT),n-1) FROM n)
SELECT m*(2*m-1) FROM m```
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 ***

6. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Originally Posted by Peter.Vanroose
Or slightly shorter:
Code:
```WITH
n(n) AS (VALUES 2,3,5,7,13,17,19,31,61,89,107,127,521,607,1279,2203,2281),
m(m) AS (SELECT POWER(CAST(2 AS BIGINT),n-1) FROM n)
SELECT m*(2*m-1) FROM m```

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

7. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by Lenny77
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]
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.

8. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
I did not do nothing else. Just copy-paste and run.

Lenny

9. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by Lenny77
I did not do nothing else. Just copy-paste and run.
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.

10. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
I am using QTODBC....

11. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by Lenny77
I am using QTODBC....
Is your version 8 DB2 server on LUW or on z/OS?

12. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Not sure.... Maybe z/os...

13. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by Lenny77
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:
Code:
```WITH
n(n) AS (
SELECT 2 FROM sysibm.sysdummy1 UNION ALL
SELECT 3 FROM sysibm.sysdummy1 UNION ALL
SELECT 5 FROM sysibm.sysdummy1 UNION ALL
SELECT 7 FROM sysibm.sysdummy1 UNION ALL
SELECT 13 FROM sysibm.sysdummy1 UNION ALL
SELECT 17 FROM sysibm.sysdummy1 UNION ALL
SELECT 19 FROM sysibm.sysdummy1 UNION ALL
SELECT 31 FROM sysibm.sysdummy1 UNION ALL
SELECT 61 FROM sysibm.sysdummy1 UNION ALL
SELECT 89 FROM sysibm.sysdummy1 UNION ALL
SELECT 107 FROM sysibm.sysdummy1 UNION ALL
SELECT 127 FROM sysibm.sysdummy1 UNION ALL
SELECT 521 FROM sysibm.sysdummy1 UNION ALL
SELECT 609 FROM sysibm.sysdummy1 UNION ALL
SELECT 1279 FROM sysibm.sysdummy1 UNION ALL
SELECT 2203 FROM sysibm.sysdummy1 UNION ALL
SELECT 2281 FROM sysibm.sysdummy1),
m(m) AS (SELECT POWER(2,n-1) FROM n)
SELECT m*(2*m-1) FROM m```

14. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Originally Posted by Peter.Vanroose
DB2 v8 for z/OS does not support the "VALUES" statement, and has no BIGINT; try the following equivalent SQL instead:
Code:
```WITH
n(n) AS (
SELECT 2 FROM sysibm.sysdummy1 UNION ALL
SELECT 3 FROM sysibm.sysdummy1 UNION ALL
SELECT 5 FROM sysibm.sysdummy1 UNION ALL
SELECT 7 FROM sysibm.sysdummy1 UNION ALL
SELECT 13 FROM sysibm.sysdummy1 UNION ALL
SELECT 17 FROM sysibm.sysdummy1 UNION ALL
SELECT 19 FROM sysibm.sysdummy1 UNION ALL
SELECT 31 FROM sysibm.sysdummy1 UNION ALL
SELECT 61 FROM sysibm.sysdummy1 UNION ALL
SELECT 89 FROM sysibm.sysdummy1 UNION ALL
SELECT 107 FROM sysibm.sysdummy1 UNION ALL
SELECT 127 FROM sysibm.sysdummy1 UNION ALL
SELECT 521 FROM sysibm.sysdummy1 UNION ALL
SELECT 609 FROM sysibm.sysdummy1 UNION ALL
SELECT 1279 FROM sysibm.sysdummy1 UNION ALL
SELECT 2203 FROM sysibm.sysdummy1 UNION ALL
SELECT 2281 FROM sysibm.sysdummy1),
m(m) AS (SELECT POWER(2,n-1) FROM n)
SELECT m*(2*m-1) FROM m```
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

15. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Using the recursive in DB2 could be a very tricky and useful !

Thanks

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•