Function RANDOM in IBM Informix BD?

Question

I need to use an Random() SQL function in a Informix version 11.50 database.
But in select below returns syntax error :

select random(), codigo from minha_tabela; 
  674: Routine (random) can not be resolved.
Error in line 1
Near character position 8

The function does not exist or has another means of using it?

Answer 1

The Random() function was only natively added to the IBM Informix database in version 11.70.xC6 via a "package" of compatibility with Oracle (using the datablades of Informix).

However, it is possible to create your own Random function in previous versions of the database.
To do this just have permission to create SPL/Udrs or ask your DBA to create the code below.

Version 11.70 xC5 or less

The authorship of this code is Jonathan Leffler (ex-IBM) and its original version can be found on code repository of IIUG (International Informix User Group).

Example run through dbaccess utility:

-- @(#)$Id: random.spl,v 1.2 1997/12/08 19:31:44 johnl Exp $
--
-- Simple emulation of SRAND and RAND in SPL
-- Using random number generator suggested by C standard (ISO 9899:1990)

CREATE PROCEDURE sp_setseed(n INTEGER)
        DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;;
        LET seed = n;;
END PROCEDURE;
Routine created.

;
CREATE PROCEDURE sp_random() RETURNING INTEGER;;
        DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;;
        DEFINE d DECIMAL(20,0);;
        LET d = (seed * 1103515245) + 12345;;
        -- MOD function does not handle 20-digit values...  Dammit!!
        LET seed = d - 4294967296 * TRUNC(d / 4294967296);;
        RETURN MOD(TRUNC(seed / 65536), 32768);;
END PROCEDURE;
Routine created.

;
execute procedure sp_setseed(3414311);
Routine executed.

select first 15 sp_random(), tabid from systables
(expression)       tabid

       20738           1
       14601           2
       22109           3
        2879           4
       24494           5
       27611           6
       30188           7
       30057           8
        6287           9
        1852          10
       18407          11
       13089          12
       24552          13
       30206          14
        8225          15
15 row(s) retrieved.
Database closed.

Version 11.70 xC6 or later

In this version it is already possible to use the function DBMS_RANDOM_RANDOM().
But for this we must first register the datablade "SQL Packages Extension" (excompat).

* **Observing: Although in version 11.70 xC1 already exists the feature to automatically register built-in datablades , it does not record the excompat, being necessary to record it manually. But this should be done only once and is valid within the connected database. ***

-- Exibindo a versao que esto utilizando
select dbinfo('version','full') from sysmaster:sysdual;
(constant)
IBM Informix Dynamic Server Version 11.70.FC7
1 row(s) retrieved.

-- Registrando o datablade excompat (SQL Extension compatible)
execute function sysbldprepare('excompat.1.0', 'create');
(expression)
           0
1 row(s) retrieved.

-- Comparando random SPL com random built-in do Informix ;
select first 15 sp_random(), dbms_random_random() , tabid from systables ;

(expression) (expression)       tabid
       16838  -1109799718           1
        5758   1761650943           2
       10113  -2067931720           3
       17515    462500326           4
       31051   1464472358           5
        5627  -2047557286           6
       23010    356861852           7
        7419  -1157266724           8
       16212   1202317650           9
        4086  -1302274873          10
        2749    186851740          11
       12767   1161898564          12
        9084  -1151907315          13
       12060    755693317          14
       32225  -1420713979          15
15 row(s) retrieved.