Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jun 2009
    Posts
    1

    Lightbulb Unanswered: The Perfect Number

    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. #2
    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. #3
    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
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    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. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote 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. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote 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
    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

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote 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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

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

    Lenny

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote 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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

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

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Lenny77
    I am using QTODBC....
    Is your version 8 DB2 server on LUW or on z/OS?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

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

  13. #13
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote 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
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  14. #14
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote 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. #15
    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
  •