Rounding upto 9 decimal places in DB2 sql query

Question

I have a procedure and i'm setting a value as

DECLARE V_FACTOR DECIMAL(14,9);

SET V_FACTOR  = ROUND((73108997572.52/69453547621393.89),9);

should give me a value like 0.001052632 but its giving as 0.00105

Answer 1

I ran the following test.

Create a source file, round.sql.

--#SET TERMINATOR @
connect to pocdb@

values (ROUND((73108997572.52/69453547621393.89),9))@

create or replace procedure stack.round_proc(out r decimal(14,9))
language sql
begin
    declare r1 decimal(14,9);
    set r = ROUND((73108997572.52/69453547621393.89),9);
end
@

create or replace function stack.round_func()
returns decimal(14,9)
language sql
begin atomic
    declare r1 decimal(14,9);
    set r1 = ROUND((73108997572.52/69453547621393.89),9);
    return r1;
end
@

call stack.round_proc(?)@

values (stack.round_func())@

connect reset@
terminate@

Execute the source file, using:

db2 -tvf round.sql > round.out 2>&1

Results captured in round.out:

connect to pocdb

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = DB2INST1
 Local database alias   = POCDB


values (ROUND((73108997572.52/69453547621393.89),9))

1
---------------------------------
             0.001052632000000000

  1 record(s) selected.


create or replace procedure stack.round_proc(out r decimal(14,9))
language sql
begin
    declare r1 decimal(14,9);
    set r = ROUND((73108997572.52/69453547621393.89),9);
end

DB20000I  The SQL command completed successfully.

create or replace function stack.round_func()
returns decimal(14,9)
language sql
begin atomic
    declare r1 decimal(14,9);
    set r1 = ROUND((73108997572.52/69453547621393.89),9);
    return r1;
end

DB20000I  The SQL command completed successfully.

call stack.round_proc(?)

  Value of output parameters
  --------------------------
  Parameter Name  : R
  Parameter Value : 0.001052632

  Return Status = 0

values (stack.round_func())

1
----------------
     0.001052632

  1 record(s) selected.


connect reset
DB20000I  The SQL command completed successfully.

terminate
DB20000I  The TERMINATE command completed successfully.

If the results you received are different, you should open a PMR.