Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85

    Unanswered: same value returned by udf for each row

    Hi all,
    i have next problem: my UDF function returns same vale on each new row.
    what is wrong? i need different value for each row.
    can you help me pls?

    see code :
    create table t11 (col1 bigint );
    insert into t11 values (1) ;
    insert into t11 values (2) ;
    insert into t11 values (3) ;
    commit;
    create function rand_x (i_min int, i_max int )
    returns float
    language sql
    not deterministic
    begin atomic
    return value(i_min, 0) +(value(i_max, 1)- value(i_min, 0))*rand ();
    end ;

    select rand_x(1, 100) as bad_col , rand() as ok_col from t11;

    BAD_COL OK_COL
    61.586779381695 0.0278328806421094
    61.586779381695 0.329844050416578
    61.586779381695 0.0559099093600269

    thans much advance
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    looks strange.

    Output from the function should be FLOAT, but BAD_COL seems to be DECIMAL .

    I created the function on our testsystem ( DB2 UDB for z/OS V8 NFM )
    omitted the textstring "begin atomic" and the function worked fine.


    CREATE FUNCTION RAND_X (I_MIN INT, I_MAX INT )
    RETURNS FLOAT
    LANGUAGE SQL
    NOT DETERMINISTIC

    RETURN VALUE(I_MIN, 0) +(VALUE(I_MAX, 1)- VALUE(I_MIN, 0))*RAND ();




    SELECT RAND_X ( 1,100), RAND()
    FROM SYSIBM.SYSTABLES
    FETCH FIRST 10 ROWS ONLY ;

    gave as a result:

    COL1 COL2
    ---------- ----------
    5.417E+01 7.872E-02
    2.944E+01 7.680E-01
    6.084E+01 4.268E-02
    4.956E+01 8.509E-01
    4.169E+01 7.381E-01
    6.663E+01 5.565E-01
    9.740E+00 4.582E-01
    7.174E+01 6.858E-01
    6.837E+01 6.786E-02
    6.867E+01 2.851E-01

  3. #3
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    umayer: works, thanks very much
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  4. #4
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    next level. when UDF must contains more than one command, is necessary begin atomic/ end statement. this causes repeating value on UDF result. some ideas ?
    RTFM? bug? feature?
    db version is 8.2.3. LUW
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Initially, this looks like a defect in DB2. You may want to try it with the latest FixPak to see if this is already fixed. If not, open a PMR with IBM support.

    I recommend that you try to change the original function to EXTERNAL ACTION (default is NO EXTERNAL ACTION). With this, DB2 must make sure that the function body is called as often as is mandated by the SQL statement you typed in (and not what the optimizer may make of it).

    Furthermore, your claim that one needs compound statements can usually not be held up. I found so far that it is generally possibly to write procedural SQL statements into relational SQL. (Alas, thinking in sets is not so well practiced amongst the many people who work with relational DBMS.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85

    thanks

    stolze:
    thanks
    sorry for delay.
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

Posting Permissions

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