Ratios

This stored procedure, run against the sysmaster database, will return key performance metrics. Run it regularly and monitor the output then historical performance data and alerts can be maintained.

CREATE PROCEDURE sp_ratios()
RETURNING
        DECIMAL(10,2),
        DECIMAL(10,2),
        DECIMAL(10,2),
        DECIMAL(10,2),
        INTERVAL HOUR(5) TO SECOND,
        DATETIME YEAR    TO SECOND;

        DEFINE l_value       DECIMAL(12,2);
        DEFINE i             INT;
        DEFINE l_br          DECIMAL(12,2);
        DEFINE l_btr         DECIMAL(12,2);
        DEFINE l_btradata    DECIMAL(12,2);
        DEFINE l_btraidx     DECIMAL(12,2);
        DEFINE l_buffers     DECIMAL(12,2);
        DEFINE l_buffwts     DECIMAL(12,2);
        DEFINE l_bufwrites   DECIMAL(12,2);
        DEFINE l_dpra        DECIMAL(12,2);
        DEFINE l_pagreads    DECIMAL(12,2);
        DEFINE l_rapgs_used  DECIMAL(12,2);
        DEFINE l_rau         DECIMAL(12,2);
        DEFINE l_stathrs     DECIMAL(12,2);
        DEFINE l_ubtr        DECIMAL(12,2);
        DEFINE l_usedbuffs   DECIMAL(12,2);
        DEFINE l_seconds     INT;
        DEFINE l_stime       INTERVAL HOUR(5)   TO SECOND;
        DEFINE l_ttime       INTERVAL DAY(3)    TO SECOND;
        DEFINE l_start       DATETIME YEAR      TO SECOND;
        DEFINE l_str         CHAR(12);
        DEFINE l_name        CHAR(13);

        FOREACH SELECT name, value
                INTO l_name , l_value
                FROM 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 COUNT(*)
                INTO i
                FROM systables, syscolumns
                WHERE systables.tabid = syscolumns.tabid
                        AND tabname       = 'sysbufhdr'
                        AND colname       = 'pagenum';

        IF i == 0 THEN
                SELECT COUNT(*)
                        INTO l_usedbuffs
                        FROM sysbufhdr
                        WHERE offset >= 0
                                AND chunk > 0;
        ELSE
                SELECT COUNT(*)
                        INTO l_usedbuffs
                        FROM sysbufhdr
                        WHERE pagenum > 0;
        END IF

        SELECT CURRENT YEAR TO SECOND - ( sh_curtime - sh_pfclrtime) UNITS SECOND,
                        (sh_curtime - sh_pfclrtime)
                INTO l_start, l_seconds
                FROM sysshmvals;

        LET l_ttime = CURRENT - l_start;
        LET l_ttime = CURRENT YEAR TO SECOND - l_start;
        LET l_stime = l_ttime;

        LET l_str = (l_seconds / 3600);
        LET l_stathrs = l_str;

        SELECT cf_effective
                INTO l_buffers
                FROM 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
        IF l_usedbuffs > 0 AND l_stathrs > 0 THEN
                LET l_ubtr = (((l_pagreads + l_bufwrites) / l_usedbuffs) / l_stathrs);
        ELSE
                LET l_ubtr = 0;
        END IF

        RETURN l_br, l_rau, l_btr, l_ubtr, l_stime, l_start;

END PROCEDURE

DOCUMENT
        'Procedure sp_ratios.',
        ' Calculate critical ratios BR, BTR, AND RAU ';