Informix

 View Only
Expand all | Collapse all

alternative way to skip installation of DBMS_RANDOM package ?

  • 1.  alternative way to skip installation of DBMS_RANDOM package ?

    Posted Mon July 13, 2020 10:22 AM
    Hello everyone, 

    i´d like ask if you know a good alterantive to install the DBMS_RANDOM package to get a "random" function in informix 14.10FC3

    Is there any out of the box way to get a random integer ? How do you solve it ?


    Thanks!

    ------------------------------
    Marc Demhartner
    ------------------------------

    #Informix


  • 2.  RE: alternative way to skip installation of DBMS_RANDOM package ?

    Posted Mon July 13, 2020 10:29 AM

    I've been using this for years

     

    <stdlib.h>

    "mi.h"

    void udr_srand(mi_integer seed, MI_FPARAM *fparam)

    {

                    (void) fparam; srand(seed);

    }

    mi_integer  udr_rand(MI_FPARAM *fparam)

    {

                    (void) fparam;

                    return rand();

    }

     

    mi_integer udr_get_randmax(MI_FPARAM *fparam)

    {

                    (void) fparam;

    return RAND_MAX;

    }

    mi_integer udr_rand_n_m(mi_integer n, mi_integer m, MI_FPARAM *fparam)

    {

                    int range;

                    (void) fparam;

                    if ( n == m ) return n;

                    if ( n < m ) range = 1 + m - n;

                    else { range = 1 + n - m; n = m; }

                    return n + (int) ((double)rand() / ((double)RAND_MAX + 1) * range);

    }

     

    Makefile

     

    default : udr_rand.bld

    CC = gcc

    CFLAGS = -W -Wall -ansi -pedantic -O2

    CBLDINCLS = -I${INFORMIXDIR}/incl/public -I${INFORMIXDIR}/incl/esql -I${INFORMIXDIR}/incl

    CBLDFLAGS = -DMI_SERVBUILD -fPIC -shared

    LDBLDFLAGS = -G #linux needs:

    LDBLDFLAGS = -shared -u _etext RM = rm -f .SUFFIXES: .SUFFIXES: .o .c

      needs: LDBLDFLAGS = -shared -u _etext

     

    .SUFFIXES:

    .SUFFIXES: .o .c

    .c.o:

                    ${CC} ${CFLAGS} ${CBLDFLAGS} ${CBLDINCLS} -c $< FILES.c = udr_rand.c

    FILES.o = ${FILES.c:.c=.o}

    udr_rand.bld:

                    ${FILES.o} ${LD} ${LDBLDFLAGS} -o $@ ${FILES.o}

    clean:

                    ${RM} ${FILES.o} udr_rand.bld

     

    Register

     

    create function udr_get_randmax()

                    returns integer

                    with (class="rnd") external name "$INFORMIXDIR/extend/rnd/udr_rand.bld(udr_get_randmax)"

    language c;

     

     

    VPCLASS=rnd

     

     




    Original Message


  • 3.  RE: alternative way to skip installation of DBMS_RANDOM package ?

    Posted Mon July 13, 2020 12:54 PM
    Not out-of-the-box, no. It's either the random blade or write your own bladelet and install that.

    Art


    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.






    Original Message


  • 4.  RE: alternative way to skip installation of DBMS_RANDOM package ?

    Posted Mon July 13, 2020 10:54 PM
    Hello Marc,
    Built-in function for generating random numbers are not provided, so you can write your own routine,
    but wouldn't it be better to install and use the DBMS_RANDOM package?

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------

    Original Message


  • 5.  RE: alternative way to skip installation of DBMS_RANDOM package ?

    Posted Tue July 14, 2020 04:11 AM
    Thanks for your feedback - i just noticed that DBMS_RANDOM requires to have logging enabled?

    ------------------------------
    Marc Demhartner
    ------------------------------

    Original Message


  • 6.  RE: alternative way to skip installation of DBMS_RANDOM package ?

    Posted Wed July 15, 2020 12:48 AM

    Hi Marc,

    Similar to Paul, I've used the following stored procedures for years, I think I originally copied them from IIUG back in the early 2000's maybe, but it turns up in a few places when googled for.

    In our code we set the seed from the current time as an integer.

    Is that any good to you, or maybe it is not truly random?.

     

    CREATE PROCEDURE informix.setseed_sp (n INTEGER)
	DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;
	LET seed = n;
END PROCEDURE;        


CREATE PROCEDURE informix.random_sp (out_range SMALLINT) RETURNING INTEGER;
  -- maximum out_range = 32768
	DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;
	DEFINE d DECIMAL(20,0);
	LET d = (seed * 1103515245) + 12345;
	LET seed = d - 4294967296 * TRUNC(d / 4294967296);
	RETURN MOD((seed / 65536), out_range);
END PROCEDURE;

    Regards,

    Bryce Stenberg



    ------------------------------
    Bryce Stenberg
    ------------------------------

    Original Message


  • 7.  RE: alternative way to skip installation of DBMS_RANDOM package ?

    Posted Wed July 15, 2020 01:05 AM

    I see the 'code' button on the website reply page is completely useless, seems to strip out the line feeds or something.

    This is what I actually pasted (using my pc's email client this time, lets hope ibm don't mangle it again):

     

    CREATE PROCEDURE informix.setseed_sp (n INTEGER)

                    DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;

                    LET seed = n;

    END PROCEDURE;                                                                                                                    

     

    CREATE PROCEDURE informix.random_sp (out_range SMALLINT) RETURNING INTEGER;

      -- maximum out_range = 32768

                    DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;

                    DEFINE d DECIMAL(20,0);

                    LET d = (seed * 1103515245) + 12345;

                    LET seed = d - 4294967296 * TRUNC(d / 4294967296);

                    RETURN MOD((seed / 65536), out_range);

    END PROCEDURE;                                                                                                                                                                              

     

    >> CREATE PROCEDURE informix.setseed_sp (n INTEGER) DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1; LET seed = n; END PROCEDURE; CREATE PROCEDURE informix.random_sp (out_range SMALLINT) RETURNING INTEGER; -- maximum out_range = 32768 DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1; DEFINE d DECIMAL(20,0); LET d = (seed * 1103515245) + 12345; LET seed = d - 4294967296 * TRUNC(d / 4294967296); RETURN MOD((seed / 65536), out_range); END PROCEDURE;

     

    Regards,

    Bryce Stenberg

     




    Original Message


  • 8.  RE: alternative way to skip installation of DBMS_RANDOM package ?

    Posted Wed July 15, 2020 08:58 AM

    I think that is JL's code

     




    Original Message


  • 9.  RE: alternative way to skip installation of DBMS_RANDOM package ?

    Posted Thu July 16, 2020 11:57 AM
    Hi Marc,

    a few weeks ago I've reworked a SPL routine based on the Lehmer random number generator which also allows you to limit the random numbers to upper and lower bounds. If no parameters are used, calling rand() returns a random number between 0 and 1, otherwise you'll get a random value within the specified limits.


    For initialization, either the srand    () function can be called with a start value or is called implicitly to generate a seed which is being calculated using some dynamic values from the sysmaster database. These include session-id, user-id, process-id, thread-id, the time the engine has been running, the number of executed queries and memory blocks in use.


    T    he code has been developed on Informix 14.10 but should probably also run on previous versions as well. Hope this helps.

    Any comments are appreciated.

    create procedure rand(_min integer default 0,

    _max integer default 0,

    _DEBUG boolean default "f")

    returns float;

    define global p_seed integer default 0;

    define p_high integer;

    define p_low integer;

    define p_test integer;

    define sessionid integer;

    define sql_error integer;

    define isam_error integer;

    define error_string varchar(255);

    on exception

    set sql_error, isam_error, error_string

    select dbinfo("sessionid")

    into sessionid

    from systables

    where systables.tabname = "systables";

    set debug file to "/tmp/" || sessionid || ".debug" with append;

    trace "SPL: rand (" || current year to second || ")";

    trace "User: " || user;

    trace "sql_error = " || sql_error;

    trace "isam_error = " || isam_error;

    trace "error_string = " || error_string;

    trace "_min = " || _min;

    trace "_max = " || _max;

    trace "p_seed = " || p_seed;

    end exception with resume;

    if _DEBUG

    then set debug file to "/tmp/rand.debug" with append;

    trace on;

    set explain on;

    end if;

    if (p_seed = 0)

    then call srand(0); -- initialize pseudo random number generator

    end if;

    let p_high = p_seed / (2147483647 / 48271);

    let p_low = mod(p_seed, 2147483647 / 48271);

    let p_test = 48271 * p_low - mod(2147483647, 48271) * p_high;

    if (p_test > 0)

    then let p_seed = p_test;

    else let p_seed = p_test + 2147483647;

    end if;

    if ((_min < _max) and (_max > 0))

    then return ((((p_seed / 2147483647) * ((_max - _min) + 1)) + _min)::integer);

    else return (p_seed / 2147483647);

    end if;

    end procedure


    create procedure srand(_seed integer default 0,

    _DEBUG boolean default "f");

    define global p_seed integer default 0;

    define p_current integer;   -- number of seconds since 1970-01-01 00:00:00

    define p_sessionid integer; -- session id

    define p_uid integer;       -- user id

    define p_pid integer;       -- process id

    define p_tid integer;       -- thread id

    define p_uptime integer;    -- number of seconds since server instance startup

    define p_isamtot integer;   -- total number of calls

    define p_blkused integer;   -- number of blocks of used memory

    define p_usercpu integer;   -- total number of seconds of CPU user time

    define sessionid integer;

    define sql_error integer;

    define isam_error integer;

    define error_string varchar(255);

    on exception

    set sql_error, isam_error, error_string

    select dbinfo("sessionid")

    into sessionid

    from systables

    where systables.tabname = "systables";

    set debug file to "/tmp/" || sessionid || ".debug" with append;

    trace "SPL: srand (" || current year to second || ")";

    trace "User: " || user;

    trace "sql_error = " || sql_error;

    trace "isam_error = " || isam_error;

    trace "error_string = " || error_string;

    trace "_seed = " || _seed;

    trace "p_seed = " || p_seed;

    end exception with resume;

    if _DEBUG

    then set debug file to "/tmp/srand.debug" with append;

    trace on;

    set explain on;

    end if;

    if ((_seed = 0) or (p_seed = 0))

    then let p_current = nvl(dbinfo("utc_current"), 0);

    let p_sessionid = nvl(dbinfo("sessionid"), 0);

    let p_uid = nvl((select sysmaster:syssessions.uid

    from sysmaster:syssessions

    where sysmaster:syssessions.sid = dbinfo("sessionid")), 0);

    let p_pid = nvl((select sysmaster:syssessions.pid

    from sysmaster:syssessions

    where sysmaster:syssessions.sid = dbinfo("sessionid")), 0);

    let p_tid = nvl((select max(sysmaster:sysrstcb.tid)

    from sysmaster:sysrstcb

    where sysmaster:sysrstcb.sid = dbinfo("sessionid")), 0);

    let p_uptime = nvl((select sysmaster:sysshmvals.sh_curtime - sysmaster:sysshmvals.sh_boottime

    from sysmaster:sysshmvals), 0);

    let p_isamtot = nvl((select mod(sysmaster:sysprofile.value, 2147483647)

    from sysmaster:sysprofile

    where sysmaster:sysprofile.name = "isamtot"), 0);

    let p_blkused = nvl((select mod(sum(sysmaster:sysseglst.seg_blkused), 2147483647)

    from sysmaster:sysseglst), 0);

    let p_usercpu = nvl((select mod(round(sum(sysmaster:sysvpprof.usercpu), 0), 2147483647)

    from sysmaster:sysvpprof), 0);

    if (p_current > 0)

    then if (p_sessionid > 0)

    then let p_seed = mod(p_current * p_sessionid, 2147483647);

    end if;

    if (p_uid > 0)

    then let p_seed = mod(mod(p_current * p_uid, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_pid > 0)

    then let p_seed = mod(mod(p_current * p_pid, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_tid > 0)

    then let p_seed = mod(mod(p_current * p_tid, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_uptime > 0)

    then let p_seed = mod(mod(p_current * p_uptime, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_isamtot > 0)

    then let p_seed = mod(mod(p_current * p_isamtot, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_blkused > 0)

    then let p_seed = mod(mod(p_current * p_blkused, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_usercpu > 0)

    then let p_seed = mod(mod(p_current * p_usercpu, 2147483647) * p_seed, 2147483647);

    end if;

    end if;

    if (p_sessionid > 0)

    then if (p_uid > 0)

    then let p_seed = mod(mod(p_sessionid * p_uid, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_pid > 0)

    then let p_seed = mod(mod(p_sessionid * p_pid, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_tid > 0)

    then let p_seed = mod(mod(p_sessionid * p_tid, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_uptime > 0)

    then let p_seed = mod(mod(p_sessionid * p_uptime, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_isamtot > 0)

    then let p_seed = mod(mod(p_sessionid * p_isamtot, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_blkused > 0)

    then let p_seed = mod(mod(p_sessionid * p_blkused, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_usercpu > 0)

    then let p_seed = mod(mod(p_sessionid * p_usercpu, 2147483647) * p_seed, 2147483647);

    end if;

    end if;

    if (p_uid > 0)

    then if (p_pid > 0)

    then let p_seed = mod(mod(p_uid * p_pid, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_tid > 0)

    then let p_seed = mod(mod(p_uid * p_tid, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_uptime > 0)

    then let p_seed = mod(mod(p_uid * p_uptime, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_isamtot > 0)

    then let p_seed = mod(mod(p_uid * p_isamtot, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_blkused > 0)

    then let p_seed = mod(mod(p_uid * p_blkused, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_usercpu > 0)

    then let p_seed = mod(mod(p_uid * p_usercpu, 2147483647) * p_seed, 2147483647);

    end if;

    end if;

    if (p_pid > 0)

    then if (p_tid > 0)

    then let p_seed = mod(mod(p_pid * p_tid, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_uptime > 0)

    then let p_seed = mod(mod(p_pid * p_uptime, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_isamtot > 0)

    then let p_seed = mod(mod(p_pid * p_isamtot, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_blkused > 0)

    then let p_seed = mod(mod(p_pid * p_blkused, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_usercpu > 0)

    then let p_seed = mod(mod(p_pid * p_usercpu, 2147483647) * p_seed, 2147483647);

    end if;

    end if;

    if (p_tid > 0)

    then if (p_uptime > 0)

    then let p_seed = mod(mod(p_tid * p_uptime, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_isamtot > 0)

    then let p_seed = mod(mod(p_tid * p_isamtot, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_blkused > 0)

    then let p_seed = mod(mod(p_tid * p_blkused, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_usercpu > 0)

    then let p_seed = mod(mod(p_tid * p_usercpu, 2147483647) * p_seed, 2147483647);

    end if;

    end if;

    if (p_uptime > 0)

    then if (p_isamtot > 0)

    then let p_seed = mod(mod(p_uptime * p_isamtot, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_blkused > 0)

    then let p_seed = mod(mod(p_uptime * p_blkused, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_usercpu > 0)

    then let p_seed = mod(mod(p_uptime * p_usercpu, 2147483647) * p_seed, 2147483647);

    end if;

    end if;

    if (p_isamtot > 0)

    then if (p_blkused > 0)

    then let p_seed = mod(mod(p_isamtot * p_blkused, 2147483647) * p_seed, 2147483647);

    end if;

    if (p_usercpu > 0)

    then let p_seed = mod(mod(p_isamtot * p_usercpu, 2147483647) * p_seed, 2147483647);

    end if;

    end if;

    if ((p_blkused > 0) and (p_usercpu > 0))

    then if (p_usercpu > 0)

    then let p_seed = mod(mod(p_blkused * p_usercpu, 2147483647) * p_seed, 2147483647);

    end if;

    end if;

    else let p_seed = _seed;

    end if;

    end procedure



    ------------------------------
    Roland Wintgen
    DBA, Genero/4GL Developer
    EVG Martens GmbH & Co. KG
    Moenchengladbach
    ------------------------------

    Original Message


  • 10.  RE: alternative way to skip installation of DBMS_RANDOM package ?

    Posted Thu July 16, 2020 12:04 PM

    Don't use the BOOLEAN datatype – nasty horrible slow datatype invented by Satan

     




    Original Message


  • 11.  RE: alternative way to skip installation of DBMS_RANDOM package ?

    Posted Mon August 10, 2020 07:23 AM
    Regarding the Boolean data type, I don't agree that the data type as such is bad, but I think we all agree that the Informix implementation could be improved

    ------------------------------
    Øyvind Gjerstad
    Developer/Architect
    PostNord AS
    ------------------------------

    Original Message