Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Unanswered: Equation Solver (plain DB2)

    Using the DB2 power it is a very easy to create the simple Equation Solver.

    For example you have to find the root of Equation 2 * SIN(X) = X.

    Transform this equation to f(X) = 2 * SIN(X) - X = 0.

    One root could be find between 1 and 2 because of
    f(1) * f(2) = (2 *Sin(1) - 1) * (2 * Sin(2) - 2) = -1.23889187945779E-001 < zero.

    So Xleft = 1 and Xright = 2....

    When we find the boundaries we will iterate by finding the middle of the interval:

    So the first iteration of the X will be (1 + 2) / 2 = 1.5.

    Then we find where f(X) * f(Xright), or f(X) * f(Xleft) is less then 0.

    If f(X) * f(Xright) > 0 we change Xright on (Xleft + Xright) / 2,
    If f(X) * f(Xleft) > 0 we change Xleft on (Xleft + Xright) / 2.

    We will finish the process when abs (Xleft - Xright) <= eps


    with control_tbl (Equation, Xleft, Xright, eps) as
    (select '2 * sin(X) = X ==> 2 * sin(X) - X = 0', double(1), double(2),
    double(1.e-12)
    from sysibm.sysdummy1
    )
    ,
    eq_solution (Equation, Xleft, Xright, eps, X) as
    (select ct.*, (Xleft + Xright) / 2
    from control_tbl ct
    union all
    select Equation,
    case
    when (2 * sin ((Xleft + Xright) / 2) - (Xleft + Xright) / 2) *
    (2 * sin (Xleft) - Xleft) > 0
    then (Xleft + Xright) / 2
    else Xleft
    end,
    case
    when (2 * sin ((Xleft + Xright) / 2) - (Xleft + Xright) / 2) *
    (2 * sin (Xright) - Xright) > 0
    then (Xleft + Xright) / 2
    else Xright
    end,
    eps,
    (Xleft + Xright) / 2
    from eq_solution
    where abs(Xleft - Xright) > eps
    )
    select Equation, abs(Xleft - Xright) "Real Accurancy", X "Equation Solution"
    from eq_solution
    where abs(Xleft - Xright) <= eps
    Lenny K.

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    We can make better and compacter view with the small transform of the SQL statement:

    with control_tbl (Equation, Xleft, Xright, eps) as
    (select '2 * sin(X) = X ==> 2 * sin(X) - X = 0',
    double(-2), double(-1), double(1.e-12)
    from sysibm.sysdummy1
    )
    ,
    eq_solution (Equation, Xleft, Xright, eps, X) as
    (select ct.*, (Xleft + Xright) / 2
    from control_tbl ct
    union all
    select Equation,
    case
    when (md.E_Function * lf.E_Function ) > 0
    then (Xleft + Xright) / 2
    else Xleft
    end,
    case
    when (md.E_Function * rt.E_Function ) > 0
    then (Xleft + Xright) / 2
    else Xright
    end,
    eps,
    (Xleft + Xright) / 2
    from eq_solution eq,
    table(select 2 * sin (X) - X as E_Function
    from (select Xleft as X from sysibm.sysdummy1 ) ii) lf,
    table(select 2 * sin (X) - X as E_Function
    from (select Xright as X from sysibm.sysdummy1 ) ii) rt,
    table(select 2 * sin (X) - X as E_Function
    from (select (Xleft + Xright) / 2 as X from sysibm.sysdummy1 ) ii) md

    where abs(Xleft - Xright) > eps
    )
    select Equation, abs(Xleft - Xright) "Real Accurancy", X "Equaty Solution"
    from eq_solution
    where abs(Xleft - Xright) <= eps
    Result: X = -1.89549426703434

    Lenny

  3. #3
    Join Date
    Jul 2009
    Posts
    150
    Not bad, I have checked.

    But what we have to do with equation
    SIN(X) = 0 on interval [p/2, p/2 + 2pi] having the same sign on the ends:

    SIN(pi/2) = +1 and SIN(p/2 + 2pi) = +1 ?

    Same time this equation has 2 roots on this interval:
    SIN(pi) = 0 and SIN(2pi) = 0 are solutions.

    Kara S.

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question How to define the step to locate the root

    Quote Originally Posted by DB2Plus
    Not bad, I have checked.

    But what we have to do with equation
    SIN(X) = 0 on interval [p/2, p/2 + 2pi] having the same sign on the ends:

    SIN(pi/2) = +1 and SIN(p/2 + 2pi) = +1 ?

    Same time this equation has 2 roots on this interval:
    SIN(pi) = 0 and SIN(2pi) = 0 are solutions.

    Kara S.
    This change is not too complicated I think.

    Problem is in definition of step length to locate the root.

    Lenny.

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation The really small change

    The small change is required for solution. The previous query never stoped
    on exact solution.

    Code:
    with control_tbl (Equation, Xleft, Xright, eps) as 
    (select '3 * power(x, 3)- 2 * power(x, 2)- 1 = 0', 
    double(0.0), double(2.0), double(1.e-12)
    from sysibm.sysdummy1
    ) 
    ,
    eq_solution (Equation, Xleft, Xright, eps, X) as
    (select ct.*, (Xleft + Xright) / 2
    from control_tbl ct
    union all
    select Equation, 
    case 
    when (md.E_Function * lf.E_Function ) >= 0 
    then (Xleft + Xright) / 2 
    else Xleft
    end, 
    case 
    when (md.E_Function * rt.E_Function ) >= 0 
    then (Xleft + Xright) / 2  
    else Xright
    end, 
    eps, 
    (Xleft + Xright) / 2 
    from eq_solution eq,
    table(select 3*power(x, 3)- 2 * power(x, 2)- 1 as E_Function 
    from (select Xleft as X from sysibm.sysdummy1 ) ii) lf, 
    table(select 3*power(x, 3)- 2 * power(x, 2)- 1 as E_Function 
    from (select Xright as X from sysibm.sysdummy1 ) ii) rt,
    table(select 3*power(x, 3)- 2 * power(x, 2)- 1 as E_Function 
    from (select (Xleft + Xright) / 2 as X from sysibm.sysdummy1 ) ii) md
    
    where abs(Xleft - Xright) > eps   
    ) 
    select Equation, abs(Xleft - Xright) "Real Accurancy", X "Equaty Solution" 
    from eq_solution 
    where abs(Xleft - Xright) <= eps
    The Solution is: X = 1, Accurancy is equal to 0

    Lenny

Posting Permissions

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