Oninit® SQLMorph — sp_performance
Source: examples/sp_performance.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_performance()
DEFINE l_name CHAR(128);
DEFINE l_str LVARCHAR;
DEFINE l_id INTEGER;
DEFINE l_br SMALLFLOAT;
DEFINE l_btr SMALLFLOAT;
DEFINE l_bufreads SMALLFLOAT;
DEFINE l_bufwrits SMALLFLOAT;
DEFINE l_cache SMALLFLOAT;
DEFINE l_isamtot SMALLFLOAT;
DEFINE l_rau SMALLFLOAT;
DEFINE l_chknum INTEGER;
DEFINE l_sqlerr INTEGER;
DEFINE l_isamerr INTEGER;
DEFINE l_dte DATETIME YEAR TO FRACTION(5);
DEFINE l_i01 INTEGER;
DEFINE l_i02 INTEGER;
DEFINE l_i03 INTEGER;
DEFINE l_i05 INTEGER;
DEFINE l_i06 INTEGER;
DEFINE l_r01 SMALLFLOAT;
DEFINE l_r02 SMALLFLOAT;
DEFINE l_r03 SMALLFLOAT;
DEFINE l_r04 SMALLFLOAT;
DEFINE l_r05 SMALLFLOAT;
DEFINE l_fname CHAR(256);
ON EXCEPTION SET l_sqlerr, l_isamerr
DROP TABLE IF EXISTS t_oni_space;
DROP TABLE IF EXISTS t_io;
DROP TABLE IF EXISTS t_oni_io;
DROP TABLE IF EXISTS t_actaul;
DROP TABLE IF EXISTS t_snapshot;
INSERT INTO errorlog
VALUES(CURRENT, l_sqlerr, l_isamerr, "sp_performance:" || DBINFO('sessionid'));
END EXCEPTION
--SET DEBUG FILE TO "/tmp/sp_performance.dbg" WITH APPEND;
--TRACE ON;
--
-- Ensure only runs on primary network
--
IF DBINFO('dbname') != "ml" THEN
RETURN;
END IF
--
-- Are all the dbspaces in the monitoring table?
-- If not call sp_tsmaint to add them
--
FOREACH SELECT name
INTO l_name
FROM sysmaster:sysdbspaces
WHERE name NOT IN (SELECT name FROM oni_space)
LET l_str = "dsspace<esc0>id,name<esc0>,'"|| TRIM(l_name) || "'<esc0>";
CALL sp_tsmaint(0, l_str, 1, "<esc0>", 0, -1) RETURNING l_id;
IF l_id < 0 THEN
RAISE EXCEPTION l_id;
END IF
END FOREACH
--
-- Start of dbspace space monitoring
--
DROP TABLE IF EXISTS t_oni_space;
SELECT id, name
FROM oni_space
INTO TEMP t_oni_space WITH NO LOG;
LET l_dte = CURRENT;
INSERT INTO oni_space_vt(id, ts, r01, i01)
SELECT id, l_dte dte, (SUM(nfree) / SUM(chksize) * 100)::SMALLFLOAT, SUM(chksize)
FROM t_oni_space, sysmaster:syschunks syschunks, sysmaster:sysdbspaces sysdbspaces
WHERE syschunks.dbsnum = sysdbspaces.dbsnum
AND sysdbspaces.name = t_oni_space.name
AND is_sbchunk = 0
GROUP BY 1, 2;
INSERT INTO oni_space_vt(id, ts, r01, i01)
SELECT id, l_dte dte, (SUM(udfree) / SUM(chksize) * 100)::SMALLFLOAT, SUM(chksize)
FROM t_oni_space, sysmaster:syschunks syschunks, sysmaster:sysdbspaces sysdbspaces
WHERE syschunks.dbsnum = sysdbspaces.dbsnum
AND sysdbspaces.name = t_oni_space.name
AND is_sbchunk = 1
GROUP BY 1, 2;
--
-- End of dbspace monitoring
--
-- Are all the dbspaces in the monitoring table?
-- If not call sp_tsmaint to add them
--
FOREACH SELECT name
INTO l_name
FROM sysmaster:sysdbspaces
WHERE name NOT IN (SELECT name FROM oni_io)
LET l_str = "dsio<esc0>id,name<esc0>,'"|| TRIM(l_name) || "'<esc0>";
CALL sp_tsmaint(0, l_str, 0, "<esc0>", 0, NULL) RETURNING l_id;
IF l_id < 0 THEN
RAISE EXCEPTION l_id;
END IF
END FOREACH
--
-- Start of dbspace space monitoring
--
DROP TABLE IF EXISTS t_oni_io;
DROP TABLE IF EXISTS t_io;
DROP TABLE IF EXISTS t_actual;
SELECT id, name
FROM oni_io
INTO TEMP t_oni_io WITH NO LOG;
SELECT d.name, c.pagesread, c.pageswritten,
CASE
WHEN (c.readtime+c.writetime) > 0 THEN
ROUND(((c.reads + c.writes) * 999600/(c.readtime+c.writetime)),1)
ELSE
0
END io_sec,
CASE
WHEN c.reads > 0 THEN
ROUND((c.readtime / 1000000 / c.reads),4)
ELSE
0
END readtime,
CASE
WHEN c.writes > 0 THEN
ROUND((c.writetime / 1000000 / c.writes),4)
ELSE
0
END writetime
FROM sysmaster:syschktab c, sysmaster:sysdbspaces d
WHERE c.dbsnum = d.dbsnum
INTO TEMP t_io WITH NO LOG;
SELECT name, "" fname, 0 chknum, SUM(pagesread) pagesread, SUM(pageswritten) pageswritten,
SUM(io_sec) io_sec, SUM(readtime) readtime, SUM(writetime) writetime
FROM t_io
GROUP BY 1, 2, 3
INTO TEMP t_actual WITH NO LOG;
FOREACH SELECT name, fname, chknum
INTO l_name, l_fname, l_chknum
FROM sysmaster:syschunks syschunks, sysmaster:sysdbspaces sysdbspaces
WHERE syschunks.dbsnum = sysdbspaces.dbsnum
ORDER BY chknum DESC
UPDATE t_actual
SET chknum = l_chknum,
fname = l_fname
WHERE name = l_name;
END FOREACH
SELECT name, "" fname, l_dte dte, SUM(total_read_ops)::INT8 total_read_ops, SUM(total_read_time)::INT total_read_time,
SUM(total_write_ops)::INT8 total_write_ops, SUM(total_write_time)::INT total_write_time, SUM(total_lseek_time)::INT total_lseek_time
FROM sysmaster:sysiohistory, sysmaster:syschunks syschunks, sysmaster:sysdbspaces sysdbspaces
WHERE syschunks.dbsnum = sysdbspaces.dbsnum
AND path = fname
AND ((EXTEND(CURRENT, MINUTE TO MINUTE)::CHAR(2)::INT - MOD(EXTEND(CURRENT, MINUTE TO MINUTE)::CHAR(2)::INT,5))/5::INT) = ((minute - MOD(minute,5))/5)::INT
GROUP BY 1, 2, 3
INTO TEMP t_snapshot WITH NO LOG;
FOREACH SELECT name, fname
INTO l_name, l_fname
FROM sysmaster:syschunks syschunks, sysmaster:sysdbspaces sysdbspaces
WHERE syschunks.dbsnum = sysdbspaces.dbsnum
ORDER BY chknum DESC
UPDATE t_snapshot
SET fname = l_fname
WHERE name = l_name;
END FOREACH
FOREACH SELECT id
INTO l_id
FROM t_oni_io
--
-- Get the delta values
--
SELECT CASE WHEN pagesread - i05 < 0 THEN
pagesread
ELSE
pagesread - i05
END,
CASE WHEN pageswritten - i06 < 0 THEN
pageswritten
ELSE
pageswritten - i06
END,
CASE WHEN io_sec - r05 < 0 THEN
io_sec
ELSE
io_sec - r05
END,
CASE WHEN readtime - r03 < 0 THEN
readtime
ELSE
readtime - r03
END,
CASE WHEN writetime - r04 < 0 THEN
writetime
ELSE
writetime - r04
END,
CASE WHEN total_read_ops - r01< 0 THEN
total_read_ops
ELSE
total_read_ops - r01
END,
CASE WHEN total_read_time - i01 < 0 THEN
total_read_time
ELSE
total_read_time - i01
END,
CASE WHEN total_write_ops - r02< 0 THEN
total_write_ops
ELSE
total_write_ops - r02
END,
CASE WHEN total_write_time - i02< 0 THEN
total_write_time
ELSE
total_write_time - i02
END,
CASE WHEN total_lseek_time - i03< 0 THEN
total_lseek_time
ELSE
total_lseek_time - i03
END
INTO l_i05, l_i06, l_r05, l_r03, l_r04, l_r01, l_i01, l_r02, l_i02, l_i03
FROM t_oni_io, t_snapshot, t_actual, oni_io_latest
WHERE t_oni_io.name = t_actual.name
AND t_oni_io.id = oni_io_latest.id
AND t_oni_io.name = t_snapshot.name
AND t_oni_io.id = l_id;
INSERT INTO oni_io_vt(id, ts, vc01, i04, i05, i06, r05, r03, r04, r01, i01, r02, i02, i03)
SELECT id, l_dte, t_actual.fname, chknum,
l_i05, l_i06, l_r05, l_r03, l_r04, l_r01, l_i01, l_r02, l_i02, l_i03
FROM t_oni_io, t_snapshot, t_actual
WHERE t_oni_io.name = t_actual.name
AND t_oni_io.name = t_snapshot.name
AND t_oni_io.id = l_id;
--
-- Get the actual values
--
SELECT pagesread, pageswritten, io_sec, readtime, writetime,
total_read_ops, total_read_time, total_write_ops,
total_write_time, total_lseek_time
INTO l_i05, l_i06, l_r05, l_r03, l_r04, l_r01, l_i01, l_r02, l_i02, l_i03
FROM t_oni_io, t_snapshot, t_actual
WHERE t_oni_io.name = t_actual.name
AND t_oni_io.name = t_snapshot.name
AND t_oni_io.id = l_id;
UPDATE oni_io_latest
SET ts = l_dte,
i05 = l_i05,
i06 = l_i06,
r05 = l_r05,
r03 = l_r03,
r04 = l_r04,
r01 = l_r01,
i01 = l_i01,
r02 = l_r02,
i02 = l_i02,
i03 = l_i03
WHERE id = l_id;
END FOREACH
DROP TABLE IF EXISTS t_actual;
DROP TABLE IF EXISTS t_io;
DROP TABLE IF EXISTS t_snapshot;
--
-- End of dbio monitoring
--
CALL sp_ratios() RETURNING l_br, l_rau, l_btr;
CALL sp_metricid('br') RETURNING l_id;
INSERT INTO oni_perf_vt(id, ts, r01)
VALUES(l_id, l_dte, l_br);
CALL sp_metricid('rau') RETURNING l_id;
INSERT INTO oni_perf_vt(id, ts, r01)
VALUES(l_id, l_dte, l_rau);
CALL sp_metricid('btr') RETURNING l_id;
INSERT INTO oni_perf_vt(id, ts, r01)
VALUES(l_id, l_dte, l_btr);
SELECT value
INTO l_bufreads
FROM sysmaster:sysprofile
WHERE name = 'bufreads';
IF l_bufreads != 0 THEN
SELECT 100 * (l_bufreads - value) / l_bufreads
INTO l_cache
FROM sysmaster:sysprofile
WHERE name = 'dskreads';
ELSE
LET l_cache = 0;
END IF
LET l_cache = sp_validcache(l_cache);
CALL sp_metricid('readcache') RETURNING l_id;
INSERT INTO oni_perf_vt(id, ts, r01)
VALUES(l_id, l_dte, l_cache);
SELECT value
INTO l_bufwrits
FROM sysmaster:sysprofile
WHERE name = 'bufwrites';
IF l_bufreads != 0 THEN
SELECT 100 * (l_bufwrits - value) / l_bufwrits
INTO l_cache
FROM sysmaster:sysprofile
WHERE name = 'dskwrites';
ELSE
LET l_cache = 0;
END IF
LET l_cache = sp_validcache(l_cache);
CALL sp_metricid('writecache') RETURNING l_id;
INSERT INTO oni_perf_vt(id, ts, r01)
VALUES(l_id, l_dte, l_cache);
CALL sp_metricid('2kreadcache') RETURNING l_id;
INSERT INTO oni_perf_vt(id, ts, r01)
SELECT l_id, l_dte,
CASE WHEN bufreads != 0 THEN
100 * ((bufreads - oni_kreads) / bufreads)
ELSE
0
END
FROM sysmaster:sysbufpool
WHERE bufsize = 2048;
CALL sp_metricid('2kwritecache') RETURNING l_id;
INSERT INTO oni_perf_vt(id, ts, r01)
SELECT l_id, l_dte,
CASE WHEN bufwrites != 0 THEN
100 * ((bufwrites - oni_kwrites) / bufwrites)
ELSE
0
END
FROM sysmaster:sysbufpool
WHERE bufsize = 2048;
--
-- For non default page size dbspaces filter on
-- demominator of 0
--
CALL sp_metricid('8kreadcache') RETURNING l_id;
INSERT INTO oni_perf_vt(id, ts, r01)
SELECT l_id, l_dte,
CASE WHEN bufreads != 0 THEN
100 * ((bufreads - oni_kreads) / bufreads)
ELSE
0
END
FROM sysmaster:sysbufpool
WHERE bufsize = 8192;
CALL sp_metricid('8kwritecache') RETURNING l_id;
INSERT INTO oni_perf_vt(id, ts, r01)
SELECT l_id, l_dte,
CASE WHEN bufwrites != 0 THEN
100 * ((bufwrites - oni_kwrites) / bufwrites)
ELSE
0
END
FROM sysmaster:sysbufpool
WHERE bufsize = 8192;
CALL sp_metricid('16kreadcache') RETURNING l_id;
INSERT INTO oni_perf_vt(id, ts, r01)
SELECT l_id, l_dte,
CASE WHEN bufreads != 0 THEN
100 * ((bufreads - oni_kreads) / bufreads)
ELSE
0
END
FROM sysmaster:sysbufpool
WHERE bufsize = 16384;
CALL sp_metricid('16kwritecache') RETURNING l_id;
INSERT INTO oni_perf_vt(id, ts, r01)
SELECT l_id, l_dte,
CASE WHEN bufwrites != 0 THEN
100 * ((bufwrites - oni_kwrites) / bufwrites)
ELSE
0
END
FROM sysmaster:sysbufpool
WHERE bufsize = 16384;
SELECT value
INTO l_isamtot
FROM sysmaster:sysprofile
WHERE name = 'isamtot';
IF l_isamtot != 0 THEN
SELECT 100 * (value / l_isamtot)
INTO l_cache
FROM sysmaster:sysprofile
WHERE name = 'seqscans';
ELSE
LET l_cache = 0;
END IF
CALL sp_metricid('seqrat') RETURNING l_id;
INSERT INTO oni_perf_vt(id, ts, r01)
VALUES(l_id, l_dte, l_cache);
DROP TABLE IF EXISTS t_oni_space;
DROP TABLE IF EXISTS t_oni_io;
END PROCEDURE;
C UDR — sp_performance.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 void sp_performance(MI_FPARAM *fparam)
{
mi_string * l_name = NULL;
mi_lvarchar * l_str = NULL;
mi_integer l_id;
mi_real l_br;
mi_real l_btr;
mi_real l_bufreads;
mi_real l_bufwrits;
mi_real l_cache;
mi_real l_isamtot;
mi_real l_rau;
mi_integer l_chknum;
mi_integer l_sqlerr;
mi_integer l_isamerr;
mi_datetime * l_dte = NULL;
mi_integer l_i01;
mi_integer l_i02;
mi_integer l_i03;
mi_integer l_i05;
mi_integer l_i06;
mi_real l_r01;
mi_real l_r02;
mi_real l_r03;
mi_real l_r04;
mi_real l_r05;
mi_string * l_fname = NULL;
MI_CONNECTION *conn = NULL;
mi_integer rc;
mi_integer _exc_sqlcode = 0;
mi_integer _exc_isamerr = 0;
const char *_exc_msg = "";
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__);
if ((DBINFO("dbname") != "ml")) {
DBDK_TRACE_EXIT(__FUNCTION__);
if (mi_fp_funcstate(fparam) == NULL && conn) mi_close(conn);
return;
}
/* FOREACH — opens cursor on rewritten SELECT */
{
MI_STATEMENT *_cur1_st;
_cur1_st = mi_prepare(conn, oni_clean_sql("SELECT name FROM sysmaster:sysdbspaces\n WHERE name NOT IN (SELECT name FROM oni_space)"), 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_str = oni_concat((const char *)(oni_concat((const char *)("dsspace<esc0>id,name<esc0>,'"), (const char *)(TRIM(l_name)))), (const char *)("'<esc0>"));
l_id = sp_tsmaint(0, l_str, 1, "<esc0>", 0, (-1));
if ((l_id < 0)) {
_exc_sqlcode = (mi_integer)(l_id);
_exc_msg = "raised by SPL";
goto _exc_handler;
}
}
mi_close_statement(_cur1_st);
}
/* DROP TABLE IF EXISTS t_oni_space */
ONI_EXEC(conn, oni_clean_sql("DROP TABLE IF EXISTS t_oni_space"), "drop_table failed", 0, 0);
/*
* SELECT id, name
* FROM oni_space
* INTO TEMP t_oni_space WITH NO LOG
*/
ONI_EXEC(conn, oni_clean_sql("SELECT id, name\n FROM oni_space\n INTO TEMP t_oni_space WITH NO LOG"), "create_temp failed", 0, 0);
l_dte = CURRENT;
/*
* INSERT INTO oni_space_vt(id, ts, r01, i01)
* SELECT id, l_dte dte, (SUM(nfree) / SUM(chksize) * 100)::SMALLFLOAT, SUM(chksize)
* FROM t_oni_space, sysmaster:syschunks syschunks, sysmaster:sysdbspaces sysdbspaces
* WHERE syschunks.dbsnum = sysdbspaces.dbsnum
* AND sysdbspaces.name = t_oni_space.name
* AND is_sbchunk = 0
* GROUP BY 1, 2
*/
ONI_EXEC(conn, oni_clean_sql("INSERT INTO oni_space_vt(id, ts, r01, i01)\n SELECT id, l_dte dte, (SUM(nfree) / SUM(chksize) * 100)::SMALLFLOAT, SUM(chksize)\n FROM t_oni_space, sysmaster:syschunks syschunks, sysmaster:sysdbspaces sysdbspaces\n WHERE syschunks.dbsnum = sysdbspaces.dbsnum\n AND sysdbspaces.name = t_oni_space.name\n AND is_sbchunk = 0\n GROUP BY 1, 2"), "insert failed", 0, 0);
/*
* INSERT INTO oni_space_vt(id, ts, r01, i01)
* SELECT id, l_dte dte, (SUM(udfree) / SUM(chksize) * 100)::SMALLFLOAT, SUM(chksize)
* FROM t_oni_space, sysmaster:syschunks syschunks, sysmaster:sysdbspaces sysdbspaces
* WHERE syschunks.dbsnum = sysdbspaces.dbsnum
* AND sysdbspaces.name = t_oni_space.name
* AND is_sbchunk = 1
* GROUP BY 1, 2
*/
ONI_EXEC(conn, oni_clean_sql("INSERT INTO oni_space_vt(id, ts, r01, i01)\nSELECT id, l_dte dte, (SUM(udfree) / SUM(chksize) * 100)::SMALLFLOAT, SUM(chksize)\n FROM t_oni_space, sysmaster:syschunks syschunks, sysmaster:sysdbspaces sysdbspaces\n WHERE syschunks.dbsnum = sysdbspaces.dbsnum\n AND sysdbspaces.name = t_oni_space.name\n AND is_sbchunk = 1\n GROUP BY 1, 2"), "insert failed", 0, 0);
/* FOREACH — opens cursor on rewritten SELECT */
{
MI_STATEMENT *_cur2_st;
_cur2_st = mi_prepare(conn, oni_clean_sql("SELECT name FROM sysmaster:sysdbspaces\n WHERE name NOT IN (SELECT name FROM oni_io)"), NULL);
ONI_EXEC_PREP(conn,_cur2_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_str = oni_concat((const char *)(oni_concat((const char *)("dsio<esc0>id,name<esc0>,'"), (const char *)(TRIM(l_name)))), (const char *)("'<esc0>"));
l_id = sp_tsmaint(0, l_str, 0, "<esc0>", 0, NULL);
if ((l_id < 0)) {
_exc_sqlcode = (mi_integer)(l_id);
_exc_msg = "raised by SPL";
goto _exc_handler;
}
}
mi_close_statement(_cur2_st);
}
/* DROP TABLE IF EXISTS t_oni_io */
ONI_EXEC(conn, oni_clean_sql("DROP TABLE IF EXISTS t_oni_io"), "drop_table failed", 0, 0);
/* DROP TABLE IF EXISTS t_io */
ONI_EXEC(conn, oni_clean_sql("DROP TABLE IF EXISTS t_io"), "drop_table failed", 0, 0);
/* DROP TABLE IF EXISTS t_actual */
ONI_EXEC(conn, oni_clean_sql("DROP TABLE IF EXISTS t_actual"), "drop_table failed", 0, 0);
/*
* SELECT id, name
* FROM oni_io
* INTO TEMP t_oni_io WITH NO LOG
*/
ONI_EXEC(conn, oni_clean_sql("SELECT id, name\n FROM oni_io\n INTO TEMP t_oni_io WITH NO LOG"), "create_temp failed", 0, 0);
/*
* SELECT d.name, c.pagesread, c.pageswritten,
* CASE
* WHEN (c.readtime+c.writetime) > 0 THEN
* ROUND(((c.reads + c.writes) * 999600/(c.readtime+c.writetime)),1)
* ELSE
* 0
* END io_sec,
* CASE
* WHEN c.reads > 0 THEN
* ROUND((c.readtime / 1000000 / c.reads),4)
* ELSE
* 0
* END readtime,
* CASE
* WHEN c.writes > 0 THEN
* ROUND((c.writetime / 1000000 / c.writes),4)
* ELSE
* 0
* END writetime
* FROM sysmaster:syschktab c, sysmaster:sysdbspaces d
* WHERE c.dbsnum = d.dbsnum
* INTO TEMP t_io WITH NO LOG
*/
ONI_EXEC(conn, oni_clean_sql("SELECT d.name, c.pagesread, c.pageswritten,\n CASE\n WHEN (c.readtime+c.writetime) > 0 THEN\n ROUND(((c.reads + c.writes) * 999600/(c.readtime+c.writetime)),1)\n ELSE\n 0\n END io_sec,\n CASE\n WHEN c.reads > 0 THEN\n ROUND((c.readtime / 1000000 / c.reads),4)\n ELSE\n 0\n END readtime,\n CASE\n WHEN c.writes > 0 THEN\n ROUND((c.writetime / 1000000 / c.writes),4)\n ELSE\n 0\n END writetime\n FROM sysmaster:syschktab c, sysmaster:sysdbspaces d\n WHERE c.dbsnum = d.dbsnum\n INTO TEMP t_io WITH NO LOG"), "create_temp failed", 0, 0);
/*
* SELECT name, "" fname, 0 chknum, SUM(pagesread) pagesread, SUM(pageswritten) pageswritten,
* SUM(io_sec) io_sec, SUM(readtime) readtime, SUM(writetime) writetime
* FROM t_io
* GROUP BY 1, 2, 3
* INTO TEMP t_actual WITH NO LOG
*/
ONI_EXEC(conn, oni_clean_sql("SELECT name, \"\" fname, 0 chknum, SUM(pagesread) pagesread, SUM(pageswritten) pageswritten,\n SUM(io_sec) io_sec, SUM(readtime) readtime, SUM(writetime) writetime\n FROM t_io\n GROUP BY 1, 2, 3\n INTO TEMP t_actual WITH NO LOG"), "create_temp failed", 0, 0);
/* FOREACH — opens cursor on rewritten SELECT */
{
MI_STATEMENT *_cur3_st;
_cur3_st = mi_prepare(conn, oni_clean_sql("SELECT name, fname, chknum FROM sysmaster:syschunks syschunks, sysmaster:sysdbspaces sysdbspaces\n WHERE syschunks.dbsnum = sysdbspaces.dbsnum\n ORDER BY chknum DESC"), NULL);
ONI_EXEC_PREP(conn,_cur3_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_fname = mi_value(_row, 1, NULL, NULL); /* TODO: cast */
l_chknum = *((mi_integer *) mi_value(_row, 2, NULL, NULL));
/*
* UPDATE t_actual
* SET chknum = l_chknum,
* fname = l_fname
* WHERE name = l_name
*/
ONI_EXEC(conn, oni_clean_sql("UPDATE t_actual\n SET chknum = l_chknum,\n fname = l_fname\n WHERE name = l_name"), "update failed", 0, 0);
}
mi_close_statement(_cur3_st);
}
/*
* SELECT name, "" fname, l_dte dte, SUM(total_read_ops)::INT8 total_read_ops, SUM(total_read_time)::INT total_read_time,
* SUM(total_write_ops)::INT8 total_write_ops, SUM(total_write_time)::INT total_write_time, SUM(total_lseek_time)::INT total_lseek_time
* FROM sysmaster:sysiohistory, sysmaster:syschunks syschunks, sysmaster:sysdbspaces sysdbspaces
* WHERE syschunks.dbsnum = sysdbspaces.dbsnum
* AND path = fname
* AND ((EXTEND(CURRENT, MINUTE TO MINUTE)::CHAR(2)::INT - MOD(EXTEND(CURRENT, MINUTE TO MINUTE)::CHAR(2)::INT,5))/5::INT) = ((minute - MOD(minute,5))/5)::INT
* GROUP BY 1, 2, 3
* INTO TEMP t_snapshot WITH NO LOG
*/
ONI_EXEC(conn, oni_clean_sql("SELECT name, \"\" fname, l_dte dte, SUM(total_read_ops)::INT8 total_read_ops, SUM(total_read_time)::INT total_read_time,\n SUM(total_write_ops)::INT8 total_write_ops, SUM(total_write_time)::INT total_write_time, SUM(total_lseek_time)::INT total_lseek_time\n FROM sysmaster:sysiohistory, sysmaster:syschunks syschunks, sysmaster:sysdbspaces sysdbspaces\n WHERE syschunks.dbsnum = sysdbspaces.dbsnum\n AND path = fname\n AND ((EXTEND(CURRENT, MINUTE TO MINUTE)::CHAR(2)::INT - MOD(EXTEND(CURRENT, MINUTE TO MINUTE)::CHAR(2)::INT,5))/5::INT) = ((minute - MOD(minute,5))/5)::INT\n GROUP BY 1, 2, 3\n INTO TEMP t_snapshot WITH NO LOG"), "create_temp failed", 0, 0);
/* FOREACH — opens cursor on rewritten SELECT */
{
MI_STATEMENT *_cur4_st;
_cur4_st = mi_prepare(conn, oni_clean_sql("SELECT name, fname FROM sysmaster:syschunks syschunks, sysmaster:sysdbspaces sysdbspaces\n WHERE syschunks.dbsnum = sysdbspaces.dbsnum\n ORDER BY chknum DESC"), NULL);
ONI_EXEC_PREP(conn,_cur4_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_fname = mi_value(_row, 1, NULL, NULL); /* TODO: cast */
/*
* UPDATE t_snapshot
* SET fname = l_fname
* WHERE name = l_name
*/
ONI_EXEC(conn, oni_clean_sql("UPDATE t_snapshot\n SET fname = l_fname\n WHERE name = l_name"), "update failed", 0, 0);
}
mi_close_statement(_cur4_st);
}
/* FOREACH — opens cursor on rewritten SELECT */
{
MI_STATEMENT *_cur5_st;
_cur5_st = mi_prepare(conn, oni_clean_sql("SELECT id FROM t_oni_io"), NULL);
ONI_EXEC_PREP(conn,_cur5_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_id = *((mi_integer *) mi_value(_row, 0, NULL, NULL));
/*
* SELECT CASE WHEN pagesread - i05 < 0 THEN
* pagesread
* ELSE
* pagesread - i05
* END,
* CASE WHEN pageswritten - i06 < 0 THEN
* pageswritten
* ELSE
* pageswritten - i06
* END,
* CASE WHEN io_sec - r05 < 0 THEN
* io_sec
* ELSE
* io_sec - r05
* END,
* CASE WHEN readtime - r03 < 0 THEN
* readtime
* ELSE
* readtime - r03
* END,
* CASE WHEN writetime - r04 < 0 THEN
* writetime
* ELSE
* writetime - r04
* END,
* CASE WHEN total_read_ops - r01< 0 THEN
* total_read_ops
* ELSE
* total_read_ops - r01
* END,
* CASE WHEN total_read_time - i01 < 0 THEN
* total_read_time
* ELSE
* total_read_time - i01
* END,
* CASE WHEN total_write_ops - r02< 0 THEN
* total_write_ops
* ELSE
* total_write_ops - r02
* END,
* CASE WHEN total_write_time - i02< 0 THEN
* total_write_time
* ELSE
* total_write_time - i02
* END,
* CASE WHEN total_lseek_time - i03< 0 THEN
* total_lseek_time
* ELSE
* total_lseek_time - i03
* END
* INTO l_i05, l_i06, l_r05, l_r03, l_r04, l_r01, l_i01, l_r02, l_i02, l_i03
* FROM t_oni_io, t_snapshot, t_actual, oni_io_latest
* WHERE t_oni_io.name = t_actual.name
* AND t_oni_io.id = oni_io_latest.id
* AND t_oni_io.name = t_snapshot.name
* AND t_oni_io.id = l_id
*/
ONI_EXEC(conn, oni_clean_sql("SELECT CASE WHEN pagesread - i05 < 0 THEN\n pagesread\n ELSE\n pagesread - i05\n END,\n CASE WHEN pageswritten - i06 < 0 THEN\n pageswritten\n ELSE\n pageswritten - i06\n END,\n CASE WHEN io_sec - r05 < 0 THEN\n io_sec\n ELSE\n io_sec - r05\n END,\n CASE WHEN readtime - r03 < 0 THEN\n readtime\n ELSE\n readtime - r03\n END,\n CASE WHEN writetime - r04 < 0 THEN\n writetime\n ELSE\n writetime - r04\n END,\n CASE WHEN total_read_ops - r01< 0 THEN\n total_read_ops\n ELSE\n total_read_ops - r01\n END,\n CASE WHEN total_read_time - i01 < 0 THEN\n total_read_time\n ELSE\n total_read_time - i01\n END,\n CASE WHEN total_write_ops - r02< 0 THEN\n total_write_ops\n ELSE\n total_write_ops - r02\n END,\n CASE WHEN total_write_time - i02< 0 THEN\n total_write_time\n ELSE\n total_write_time - i02\n END,\n CASE WHEN total_lseek_time - i03< 0 THEN\n total_lseek_time\n ELSE\n total_lseek_time - i03\n END\n INTO l_i05, l_i06, l_r05, l_r03, l_r04, l_r01, l_i01, l_r02, l_i02, l_i03\n FROM t_oni_io, t_snapshot, t_actual, oni_io_latest\n WHERE t_oni_io.name = t_actual.name\n AND t_oni_io.id = oni_io_latest.id\n AND t_oni_io.name = t_snapshot.name\n AND t_oni_io.id = l_id"), "select_into failed", 0, 0);
/*
* INSERT INTO oni_io_vt(id, ts, vc01, i04, i05, i06, r05, r03, r04, r01, i01, r02, i02, i03)
* SELECT id, l_dte, t_actual.fname, chknum,
* l_i05, l_i06, l_r05, l_r03, l_r04, l_r01, l_i01, l_r02, l_i02, l_i03
* FROM t_oni_io, t_snapshot, t_actual
* WHERE t_oni_io.name = t_actual.name
* AND t_oni_io.name = t_snapshot.name
* AND t_oni_io.id = l_id
*/
ONI_EXEC(conn, oni_clean_sql("INSERT INTO oni_io_vt(id, ts, vc01, i04, i05, i06, r05, r03, r04, r01, i01, r02, i02, i03)\n SELECT id, l_dte, t_actual.fname, chknum,\n l_i05, l_i06, l_r05, l_r03, l_r04, l_r01, l_i01, l_r02, l_i02, l_i03\n FROM t_oni_io, t_snapshot, t_actual\n WHERE t_oni_io.name = t_actual.name\n AND t_oni_io.name = t_snapshot.name\n AND t_oni_io.id = l_id"), "insert failed", 0, 0);
/*
* SELECT pagesread, pageswritten, io_sec, readtime, writetime,
* total_read_ops, total_read_time, total_write_ops,
* total_write_time, total_lseek_time
* INTO l_i05, l_i06, l_r05, l_r03, l_r04, l_r01, l_i01, l_r02, l_i02, l_i03
* FROM t_oni_io, t_snapshot, t_actual
* WHERE t_oni_io.name = t_actual.name
* AND t_oni_io.name = t_snapshot.name
* AND t_oni_io.id = l_id
*/
ONI_EXEC(conn, oni_clean_sql("SELECT pagesread, pageswritten, io_sec, readtime, writetime,\n total_read_ops, total_read_time, total_write_ops,\n total_write_time, total_lseek_time\n INTO l_i05, l_i06, l_r05, l_r03, l_r04, l_r01, l_i01, l_r02, l_i02, l_i03\n FROM t_oni_io, t_snapshot, t_actual\n WHERE t_oni_io.name = t_actual.name\n AND t_oni_io.name = t_snapshot.name\n AND t_oni_io.id = l_id"), "select_into failed", 0, 0);
/*
* UPDATE oni_io_latest
* SET ts = l_dte,
* i05 = l_i05,
* i06 = l_i06,
* r05 = l_r05,
* r03 = l_r03,
* r04 = l_r04,
* r01 = l_r01,
* i01 = l_i01,
* r02 = l_r02,
* i02 = l_i02,
* i03 = l_i03
* WHERE id = l_id
*/
ONI_EXEC(conn, oni_clean_sql("UPDATE oni_io_latest\n SET ts = l_dte,\n i05 = l_i05,\n i06 = l_i06,\n r05 = l_r05,\n r03 = l_r03,\n r04 = l_r04,\n r01 = l_r01,\n i01 = l_i01,\n r02 = l_r02,\n i02 = l_i02,\n i03 = l_i03\n WHERE id = l_id"), "update failed", 0, 0);
}
mi_close_statement(_cur5_st);
}
/* DROP TABLE IF EXISTS t_actual */
ONI_EXEC(conn, oni_clean_sql("DROP TABLE IF EXISTS t_actual"), "drop_table failed", 0, 0);
/* DROP TABLE IF EXISTS t_io */
ONI_EXEC(conn, oni_clean_sql("DROP TABLE IF EXISTS t_io"), "drop_table failed", 0, 0);
/* DROP TABLE IF EXISTS t_snapshot */
ONI_EXEC(conn, oni_clean_sql("DROP TABLE IF EXISTS t_snapshot"), "drop_table failed", 0, 0);
l_br = sp_ratios();
/* TODO: capture extra OUT into l_rau */
/* TODO: capture extra OUT into l_btr */
l_id = sp_metricid("br");
/*
* INSERT INTO oni_perf_vt(id, ts, r01)
* VALUES(l_id, l_dte, l_br)
*/
ONI_EXEC(conn, oni_clean_sql("INSERT INTO oni_perf_vt(id, ts, r01)\n VALUES(l_id, l_dte, l_br)"), "insert failed", 0, 0);
l_id = sp_metricid("rau");
/*
* INSERT INTO oni_perf_vt(id, ts, r01)
* VALUES(l_id, l_dte, l_rau)
*/
ONI_EXEC(conn, oni_clean_sql("INSERT INTO oni_perf_vt(id, ts, r01)\n VALUES(l_id, l_dte, l_rau)"), "insert failed", 0, 0);
l_id = sp_metricid("btr");
/*
* INSERT INTO oni_perf_vt(id, ts, r01)
* VALUES(l_id, l_dte, l_btr)
*/
ONI_EXEC(conn, oni_clean_sql("INSERT INTO oni_perf_vt(id, ts, r01)\n VALUES(l_id, l_dte, l_btr)"), "insert failed", 0, 0);
/*
* SELECT value
* INTO l_bufreads
* FROM sysmaster:sysprofile
* WHERE name = 'bufreads'
*/
ONI_EXEC(conn, oni_clean_sql("SELECT value\n INTO l_bufreads\n FROM sysmaster:sysprofile\n WHERE name = 'bufreads'"), "select_into failed", 0, 0);
if ((l_bufreads != 0)) {
/*
* SELECT 100 * (l_bufreads - value) / l_bufreads
* INTO l_cache
* FROM sysmaster:sysprofile
* WHERE name = 'dskreads'
*/
ONI_EXEC(conn, oni_clean_sql("SELECT 100 * (l_bufreads - value) / l_bufreads\n INTO l_cache\n FROM sysmaster:sysprofile\n WHERE name = 'dskreads'"), "select_into failed", 0, 0);
} else {
l_cache = 0;
}
l_cache = sp_validcache(l_cache);
l_id = sp_metricid("readcache");
/*
* INSERT INTO oni_perf_vt(id, ts, r01)
* VALUES(l_id, l_dte, l_cache)
*/
ONI_EXEC(conn, oni_clean_sql("INSERT INTO oni_perf_vt(id, ts, r01)\n VALUES(l_id, l_dte, l_cache)"), "insert failed", 0, 0);
/*
* SELECT value
* INTO l_bufwrits
* FROM sysmaster:sysprofile
* WHERE name = 'bufwrites'
*/
ONI_EXEC(conn, oni_clean_sql("SELECT value\n INTO l_bufwrits\n FROM sysmaster:sysprofile\n WHERE name = 'bufwrites'"), "select_into failed", 0, 0);
if ((l_bufreads != 0)) {
/*
* SELECT 100 * (l_bufwrits - value) / l_bufwrits
* INTO l_cache
* FROM sysmaster:sysprofile
* WHERE name = 'dskwrites'
*/
ONI_EXEC(conn, oni_clean_sql("SELECT 100 * (l_bufwrits - value) / l_bufwrits\n INTO l_cache\n FROM sysmaster:sysprofile\n WHERE name = 'dskwrites'"), "select_into failed", 0, 0);
} else {
l_cache = 0;
}
l_cache = sp_validcache(l_cache);
l_id = sp_metricid("writecache");
/*
* INSERT INTO oni_perf_vt(id, ts, r01)
* VALUES(l_id, l_dte, l_cache)
*/
ONI_EXEC(conn, oni_clean_sql("INSERT INTO oni_perf_vt(id, ts, r01)\n VALUES(l_id, l_dte, l_cache)"), "insert failed", 0, 0);
l_id = sp_metricid("2kreadcache");
/*
* INSERT INTO oni_perf_vt(id, ts, r01)
* SELECT l_id, l_dte,
* CASE WHEN bufreads != 0 THEN
* 100 * ((bufreads - oni_kreads) / bufreads)
* ELSE
* 0
* END
* FROM sysmaster:sysbufpool
* WHERE bufsize = 2048
*/
ONI_EXEC(conn, oni_clean_sql("INSERT INTO oni_perf_vt(id, ts, r01)\n SELECT l_id, l_dte,\n CASE WHEN bufreads != 0 THEN\n 100 * ((bufreads - oni_kreads) / bufreads)\n ELSE\n 0\n END\n FROM sysmaster:sysbufpool\n WHERE bufsize = 2048"), "insert failed", 0, 0);
l_id = sp_metricid("2kwritecache");
/*
* INSERT INTO oni_perf_vt(id, ts, r01)
* SELECT l_id, l_dte,
* CASE WHEN bufwrites != 0 THEN
* 100 * ((bufwrites - oni_kwrites) / bufwrites)
* ELSE
* 0
* END
* FROM sysmaster:sysbufpool
* WHERE bufsize = 2048
*/
ONI_EXEC(conn, oni_clean_sql("INSERT INTO oni_perf_vt(id, ts, r01)\n SELECT l_id, l_dte,\n CASE WHEN bufwrites != 0 THEN\n 100 * ((bufwrites - oni_kwrites) / bufwrites)\n ELSE\n 0\n END\n FROM sysmaster:sysbufpool\n WHERE bufsize = 2048"), "insert failed", 0, 0);
l_id = sp_metricid("8kreadcache");
/*
* INSERT INTO oni_perf_vt(id, ts, r01)
* SELECT l_id, l_dte,
* CASE WHEN bufreads != 0 THEN
* 100 * ((bufreads - oni_kreads) / bufreads)
* ELSE
* 0
* END
* FROM sysmaster:sysbufpool
* WHERE bufsize = 8192
*/
ONI_EXEC(conn, oni_clean_sql("INSERT INTO oni_perf_vt(id, ts, r01)\n SELECT l_id, l_dte,\n CASE WHEN bufreads != 0 THEN\n 100 * ((bufreads - oni_kreads) / bufreads)\n ELSE\n 0\n END\n FROM sysmaster:sysbufpool\n WHERE bufsize = 8192"), "insert failed", 0, 0);
l_id = sp_metricid("8kwritecache");
/*
* INSERT INTO oni_perf_vt(id, ts, r01)
* SELECT l_id, l_dte,
* CASE WHEN bufwrites != 0 THEN
* 100 * ((bufwrites - oni_kwrites) / bufwrites)
* ELSE
* 0
* END
* FROM sysmaster:sysbufpool
* WHERE bufsize = 8192
*/
ONI_EXEC(conn, oni_clean_sql("INSERT INTO oni_perf_vt(id, ts, r01)\n SELECT l_id, l_dte,\n CASE WHEN bufwrites != 0 THEN\n 100 * ((bufwrites - oni_kwrites) / bufwrites)\n ELSE\n 0\n END\n FROM sysmaster:sysbufpool\n WHERE bufsize = 8192"), "insert failed", 0, 0);
l_id = sp_metricid("16kreadcache");
/*
* INSERT INTO oni_perf_vt(id, ts, r01)
* SELECT l_id, l_dte,
* CASE WHEN bufreads != 0 THEN
* 100 * ((bufreads - oni_kreads) / bufreads)
* ELSE
* 0
* END
* FROM sysmaster:sysbufpool
* WHERE bufsize = 16384
*/
ONI_EXEC(conn, oni_clean_sql("INSERT INTO oni_perf_vt(id, ts, r01)\n SELECT l_id, l_dte,\n CASE WHEN bufreads != 0 THEN\n 100 * ((bufreads - oni_kreads) / bufreads)\n ELSE\n 0\n END\n FROM sysmaster:sysbufpool\n WHERE bufsize = 16384"), "insert failed", 0, 0);
l_id = sp_metricid("16kwritecache");
/*
* INSERT INTO oni_perf_vt(id, ts, r01)
* SELECT l_id, l_dte,
* CASE WHEN bufwrites != 0 THEN
* 100 * ((bufwrites - oni_kwrites) / bufwrites)
* ELSE
* 0
* END
* FROM sysmaster:sysbufpool
* WHERE bufsize = 16384
*/
ONI_EXEC(conn, oni_clean_sql("INSERT INTO oni_perf_vt(id, ts, r01)\n SELECT l_id, l_dte,\n CASE WHEN bufwrites != 0 THEN\n 100 * ((bufwrites - oni_kwrites) / bufwrites)\n ELSE\n 0\n END\n FROM sysmaster:sysbufpool\n WHERE bufsize = 16384"), "insert failed", 0, 0);
/*
* SELECT value
* INTO l_isamtot
* FROM sysmaster:sysprofile
* WHERE name = 'isamtot'
*/
ONI_EXEC(conn, oni_clean_sql("SELECT value\n INTO l_isamtot\n FROM sysmaster:sysprofile\n WHERE name = 'isamtot'"), "select_into failed", 0, 0);
if ((l_isamtot != 0)) {
/*
* SELECT 100 * (value / l_isamtot)
* INTO l_cache
* FROM sysmaster:sysprofile
* WHERE name = 'seqscans'
*/
ONI_EXEC(conn, oni_clean_sql("SELECT 100 * (value / l_isamtot)\n INTO l_cache\n FROM sysmaster:sysprofile\n WHERE name = 'seqscans'"), "select_into failed", 0, 0);
} else {
l_cache = 0;
}
l_id = sp_metricid("seqrat");
/*
* INSERT INTO oni_perf_vt(id, ts, r01)
* VALUES(l_id, l_dte, l_cache)
*/
ONI_EXEC(conn, oni_clean_sql("INSERT INTO oni_perf_vt(id, ts, r01)\n VALUES(l_id, l_dte, l_cache)"), "insert failed", 0, 0);
/* DROP TABLE IF EXISTS t_oni_space */
ONI_EXEC(conn, oni_clean_sql("DROP TABLE IF EXISTS t_oni_space"), "drop_table failed", 0, 0);
/* DROP TABLE IF EXISTS t_oni_io */
ONI_EXEC(conn, oni_clean_sql("DROP TABLE IF EXISTS t_oni_io"), "drop_table failed", 0, 0);
DBDK_TRACE_EXIT(__FUNCTION__);
if (mi_fp_funcstate(fparam) == NULL && conn) mi_close(conn);
_exc_handler:;
l_sqlerr = (mi_integer)_exc_sqlcode;
l_isamerr = (mi_integer)_exc_isamerr;
/* DROP TABLE IF EXISTS t_oni_space */
ONI_EXEC(conn, oni_clean_sql("DROP TABLE IF EXISTS t_oni_space"), "drop_table failed", 0, 0);
/* DROP TABLE IF EXISTS t_io */
ONI_EXEC(conn, oni_clean_sql("DROP TABLE IF EXISTS t_io"), "drop_table failed", 0, 0);
/* DROP TABLE IF EXISTS t_oni_io */
ONI_EXEC(conn, oni_clean_sql("DROP TABLE IF EXISTS t_oni_io"), "drop_table failed", 0, 0);
/* DROP TABLE IF EXISTS t_actaul */
ONI_EXEC(conn, oni_clean_sql("DROP TABLE IF EXISTS t_actaul"), "drop_table failed", 0, 0);
/* DROP TABLE IF EXISTS t_snapshot */
ONI_EXEC(conn, oni_clean_sql("DROP TABLE IF EXISTS t_snapshot"), "drop_table failed", 0, 0);
/*
* INSERT INTO errorlog
* VALUES(CURRENT, l_sqlerr, l_isamerr, "sp_performance:" || DBINFO('sessionid'))
*/
ONI_EXEC(conn, oni_clean_sql("INSERT INTO errorlog\n VALUES(CURRENT, l_sqlerr, l_isamerr, \"sp_performance:\" || DBINFO('sessionid'))"), "insert failed", 0, 0);
ONIERR_004(_exc_sqlcode, _exc_msg ? _exc_msg : "raised by SPL");
}
Install SQL — sp_performance_install.sql
-- sqlmorph — Informix install SQL for sp_performance. 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_performance; CREATE PROCEDURE sp_performance() EXTERNAL NAME '$INFORMIXDIR/extend/sqlmorph/sqlmorph.so(sp_performance)' LANGUAGE C NOT VARIANT;