Oninit® SQLMorph — sp_ratios
Source: examples/sp_ratios.sql. Original SPL above, sqlmorph-generated Informix C UDR in the middle, and the CREATE FUNCTION script that registers the UDR under the same procedure name at the bottom.
SPL
CREATE PROCEDURE sp_ratios()
RETURNING
SMALLFLOAT,
SMALLFLOAT,
SMALLFLOAT;
DEFINE l_value SMALLFLOAT;
DEFINE i INT;
DEFINE l_br SMALLFLOAT;
DEFINE l_btr SMALLFLOAT;
DEFINE l_btradata SMALLFLOAT;
DEFINE l_btraidx SMALLFLOAT;
DEFINE l_buffers SMALLFLOAT;
DEFINE l_buffwts SMALLFLOAT;
DEFINE l_bufwrites SMALLFLOAT;
DEFINE l_dpra SMALLFLOAT;
DEFINE l_pagreads SMALLFLOAT;
DEFINE l_rapgs_used SMALLFLOAT;
DEFINE l_rau SMALLFLOAT;
DEFINE l_stathrs SMALLFLOAT;
DEFINE l_ubtr SMALLFLOAT;
DEFINE l_seconds INT;
DEFINE l_str CHAR(12);
DEFINE l_name CHAR(13);
--SET DEBUG FILE TO "/tmp/sp_ratios.dbg" WITH APPEND;
--TRACE ON;
FOREACH SELECT name, value
INTO l_name , l_value
FROM sysmaster:sysprofile
WHERE name IN (
'btradata',
'btraidx',
'buffwts',
'bufwrites',
'dpra',
'pagreads',
'rapgs_used'
)
IF l_name == 'btradata' THEN
LET l_btradata = l_value;
END IF
IF l_name == 'btraidx' THEN
LET l_btraidx = l_value;
END IF
IF l_name == 'buffwts' THEN
LET l_buffwts = l_value;
END IF
IF l_name == 'bufwrites' THEN
LET l_bufwrites = l_value;
END IF
IF l_name == 'dpra' THEN
LET l_dpra = l_value;
END IF
IF l_name == 'pagreads' THEN
LET l_pagreads = l_value;
END IF
IF l_name == 'rapgs_used' THEN
LET l_rapgs_used = l_value;
END IF
END FOREACH;
SELECT (sh_curtime - sh_pfclrtime) / 3600
INTO l_seconds
FROM sysmaster:sysshmvals;
LET l_str = (l_seconds / 3600);
LET l_stathrs = l_str;
SELECT cf_effective
INTO l_buffers
FROM sysmaster:sysconfig
WHERE cf_name = 'BUFFERS';
IF (l_pagreads + l_bufwrites) > 0 THEN
LET l_br = ((l_buffwts * 100) / (l_pagreads + l_bufwrites));
ELSE
LET l_br = 0.00;
END IF
IF (l_btradata + l_btraidx + l_dpra) > 0 THEN
LET l_rau = ((l_rapgs_used * 100) / (l_btradata + l_btraidx + l_dpra));
ELSE
LET l_rau = 100.00;
END IF
IF l_buffers > 0 AND l_stathrs > 0 THEN
LET l_btr = (((l_pagreads + l_bufwrites) / l_buffers) / l_stathrs);
ELSE
LET l_btr = 0;
END IF
RETURN l_br, l_rau, l_btr;
END PROCEDURE;
C UDR — sp_ratios.c
/* sqlmorph — Informix C UDR emitter. Built by Oninit LLC. */
#include <mi.h>
#include <miami.h>
#include <string.h>
#include <stdio.h>
#include <stdlib.h>
#include "standard.h"
UDREXPORT mi_real sp_ratios(MI_FPARAM *fparam)
{
mi_real l_value;
mi_integer i;
mi_real l_br;
mi_real l_btr;
mi_real l_btradata;
mi_real l_btraidx;
mi_real l_buffers;
mi_real l_buffwts;
mi_real l_bufwrites;
mi_real l_dpra;
mi_real l_pagreads;
mi_real l_rapgs_used;
mi_real l_rau;
mi_real l_stathrs;
mi_real l_ubtr;
mi_integer l_seconds;
mi_string * l_str = NULL;
mi_string * l_name = NULL;
MI_CONNECTION *conn = NULL;
mi_integer rc;
if ((conn = (MI_CONNECTION *)mi_fp_funcstate(fparam)) == NULL )
{
if ((conn = mi_open(NULL, NULL, NULL )) == (MI_CONNECTION *) NULL )
ONIERR_002("mi_open() failed");
mi_fp_setfuncstate(fparam, (void *)conn);
}
DBDK_TRACE_ENTER(__FUNCTION__);
/* FOREACH — opens cursor on rewritten SELECT */
{
MI_STATEMENT *_cur1_st;
_cur1_st = mi_prepare(conn, oni_clean_sql("SELECT name, value FROM sysmaster:sysprofile\n WHERE name IN (\n 'btradata',\n 'btraidx',\n 'buffwts',\n 'bufwrites',\n 'dpra',\n 'pagreads',\n 'rapgs_used'\n )"), NULL);
ONI_EXEC_PREP(conn,_cur1_st, 0, MI_SEND, 0, NULL, NULL, NULL, NULL, 0, NULL, "cursor exec failed", 0);
while (mi_get_result(conn) == MI_ROWS) {
MI_ROW *_row = mi_next_row(conn,&rc);
if (_row == NULL) break;
l_name = mi_value(_row, 0, NULL, NULL); /* TODO: cast */
l_value = *((mi_real *) mi_value(_row, 1, NULL, NULL));
if ((l_name == "btradata")) {
l_btradata = l_value;
}
if ((l_name == "btraidx")) {
l_btraidx = l_value;
}
if ((l_name == "buffwts")) {
l_buffwts = l_value;
}
if ((l_name == "bufwrites")) {
l_bufwrites = l_value;
}
if ((l_name == "dpra")) {
l_dpra = l_value;
}
if ((l_name == "pagreads")) {
l_pagreads = l_value;
}
if ((l_name == "rapgs_used")) {
l_rapgs_used = l_value;
}
}
mi_close_statement(_cur1_st);
}
/*
* SELECT (sh_curtime - sh_pfclrtime) / 3600
* INTO l_seconds
* FROM sysmaster:sysshmvals
*/
ONI_EXEC(conn, oni_clean_sql("SELECT (sh_curtime - sh_pfclrtime) / 3600\n INTO l_seconds\n FROM sysmaster:sysshmvals"), "select_into failed", 0, 0);
l_str = (l_seconds / 3600);
l_stathrs = l_str;
/*
* SELECT cf_effective
* INTO l_buffers
* FROM sysmaster:sysconfig
* WHERE cf_name = 'BUFFERS'
*/
ONI_EXEC(conn, oni_clean_sql("SELECT cf_effective\n INTO l_buffers\n FROM sysmaster:sysconfig\n WHERE cf_name = 'BUFFERS'"), "select_into failed", 0, 0);
if (((l_pagreads + l_bufwrites) > 0)) {
l_br = ((l_buffwts * 100) / (l_pagreads + l_bufwrites));
} else {
l_br = ((mi_double_precision)0.00);
}
if ((((l_btradata + l_btraidx) + l_dpra) > 0)) {
l_rau = ((l_rapgs_used * 100) / ((l_btradata + l_btraidx) + l_dpra));
} else {
l_rau = ((mi_double_precision)100.00);
}
if (((l_buffers > 0) && (l_stathrs > 0))) {
l_btr = (((l_pagreads + l_bufwrites) / l_buffers) / l_stathrs);
} else {
l_btr = 0;
}
DBDK_TRACE_EXIT(__FUNCTION__);
if (mi_fp_funcstate(fparam) == NULL && conn) mi_close(conn);
return l_br;
}
Install SQL — sp_ratios_install.sql
-- sqlmorph — Informix install SQL for sp_ratios. Built by Oninit LLC. -- -- Drops the existing SPL of the same name (if any) and registers the -- C UDR shared library in its place. All UDRs in a directory link into -- one shared object (sqlmorph.so by default). Fetch the canonical -- Makefile via: -- wget https://www.oninit.com/sqlmorph/Makefile -- then run: -- make # builds sqlmorph.so -- make IFXDB=yourdb install # registers this procedure -- -- Override the library name with LIBNAME on the make command line if you -- want a different .so name; if you do, update the EXTERNAL NAME below -- to match. DROP PROCEDURE IF EXISTS sp_ratios; CREATE PROCEDURE sp_ratios() RETURNING SMALLFLOAT, SMALLFLOAT, SMALLFLOAT EXTERNAL NAME '$INFORMIXDIR/extend/sqlmorph/sqlmorph.so(sp_ratios)' LANGUAGE C NOT VARIANT;